[yunqa.de] Re: DISQLite: identify tables with blob columns
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Wed, 26 Nov 2008 14:26:31 +0100
Yeoh Ray Mond wrote:
>I need to get a list of tables that contain blob columns. I'm thinking I need
>to get a list of tables from the sqlite_master table, then perform a dummy
>query to retrieve each tables column definitions e.g. SELECT * FROM table1
>WHERE 1 = 2, and from there, identify the blob columns.
>
>Is this the way to do it? Is there a faster or more efficient way?
You are on the right track!
Use this SQL to list all tables in a database:
SELECT name FROM sqlite_master WHERE type='table';
This SQL lists information on all table columns:
PRAGMA table_info(table-name);
Blow is some Delphi code to demonstrate the two queries working together. The
same is also attached as a Delphi project for easy compiling and testing. For
Delphi 2009 compataibility, it needs DISystemCompat from DISQLite3 2.0.0 or
later to compile.
Ralf
--------------------------------------------------------------------------------
{ DISQLite3 project to show how to retrieve table and column information
from a database.
The code below iterates over all tables and all their columns. This allows
to filter for particular column names, types, or other column information.
The DISQLite3Api.sqlite3_affinity() and sqlite3_affinity16() functions can be
used to determine the affinity of a particular column type declaration.
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2008 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>
------------------------------------------------------------------------------ }
program DISQLite3_Table_Columns;
{$APPTYPE CONSOLE}
{$I DI.inc}
{$I DISQLite3.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}DISystemCompat, Windows, SysUtils,
DISQLite3Api;
const
FILE_NAME = 'Test.db3';
var
DB: sqlite3;
i: Integer;
SQL: Utf8String;
stmtTable: sqlite3_stmt;
stmtTableInfo: sqlite3_stmt;
TableName: Utf8String;
begin
try
{ Create a test database with a few tables. }
DeleteFile(FILE_NAME);
sqlite3_open(FILE_NAME, @DB);
sqlite3_exec_fast(DB, 'create table t1 (a text, b integer, c blob)');
sqlite3_exec_fast(DB, 'create table t2 (a)');
sqlite3_exec_fast(DB, 'create table t3 (a blob)');
{ Iterate over all tables in the database. }
SQL := 'SELECT name FROM sqlite_master WHERE type=''table''';
sqlite3_prepare(DB, PAnsiChar(SQL), Length(SQL), @stmtTable, nil);
while sqlite3_step(stmtTable) = SQLITE_ROW do
begin
TableName := sqlite3_column_str(stmtTable, 0);
WriteLn(TableName);
{ Iterate over all columns in a table. }
SQL := 'PRAGMA table_info (' + QuotedStr(TableName) + ')';
sqlite3_prepare(DB, PAnsiChar(SQL), Length(SQL), @stmtTableInfo, nil);
while sqlite3_step(stmtTableInfo) = SQLITE_ROW do
begin
Write(' ');
for i := 0 to sqlite3_column_count(stmtTableInfo) - 1 do
begin
if i > 0 then Write(' | ');
Write(
sqlite3_column_name(stmtTableInfo, i),
' = ',
sqlite3_column_str(stmtTableInfo, i));
end;
WriteLn;
end;
WriteLn;
sqlite3_finalize(stmtTableInfo);
end;
sqlite3_finalize(stmtTable);
sqlite3_close(DB);
except
on e: Exception do
WriteLn(e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end. { DISQLite3 project to show how to retrieve table and column information
from a database.
The code below iterates over all tables and all their columns. This allows
to filter for particular column names, types, or other column information.
The DISQLite3Api.sqlite3_affinity() and sqlite3_affinity16() functions can be
used to determine the affinity of a particular column type declaration.
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2008 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>
------------------------------------------------------------------------------ }
program DISQLite3_Table_Columns;
{$APPTYPE CONSOLE}
{$I DI.inc}
{$I DISQLite3.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}DISystemCompat, Windows, SysUtils,
DISQLite3Api;
const
FILE_NAME = 'Test.db3';
var
DB: sqlite3;
i: Integer;
SQL: Utf8String;
stmtTable: sqlite3_stmt;
stmtTableInfo: sqlite3_stmt;
TableName: Utf8String;
begin
try
{ Create a test database with a few tables. }
DeleteFile(FILE_NAME);
sqlite3_open(FILE_NAME, @DB);
sqlite3_exec_fast(DB, 'create table t1 (a text, b integer, c blob)');
sqlite3_exec_fast(DB, 'create table t2 (a)');
sqlite3_exec_fast(DB, 'create table t3 (a blob)');
{ Iterate over all tables in the database. }
SQL := 'SELECT name FROM sqlite_master WHERE type=''table''';
sqlite3_prepare(DB, PAnsiChar(SQL), Length(SQL), @stmtTable, nil);
while sqlite3_step(stmtTable) = SQLITE_ROW do
begin
TableName := sqlite3_column_str(stmtTable, 0);
WriteLn(TableName);
{ Iterate over all columns in a table. }
SQL := 'PRAGMA table_info (' + QuotedStr(TableName) + ')';
sqlite3_prepare(DB, PAnsiChar(SQL), Length(SQL), @stmtTableInfo, nil);
while sqlite3_step(stmtTableInfo) = SQLITE_ROW do
begin
Write(' ');
for i := 0 to sqlite3_column_count(stmtTableInfo) - 1 do
begin
if i > 0 then Write(' | ');
Write(
sqlite3_column_name(stmtTableInfo, i),
' = ',
sqlite3_column_str(stmtTableInfo, i));
end;
WriteLn;
end;
WriteLn;
sqlite3_finalize(stmtTableInfo);
end;
sqlite3_finalize(stmtTable);
sqlite3_close(DB);
except
on e: Exception do
WriteLn(e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end.
Other related posts:
