I needed a simple piece of code to list the tables in an ODBC database and so I hacked up the following.
It doesn't use any SQL statements as there is no standard way of listing tables utilised by the various ODBC drivers. Instead it uses the Windows ODBC api directly.
Note that I have steered away from using any of PB's internal structures which means that this code will not be broken by future PB updates etc. It works in both Ansi and Unicode modes.
Tested on an MS Access database and an MS Excel spreadsheet. In the case of the MS Excel ODBC driver, you have to opt to list all objects in order to have the tables listed.
Code: Select all
;/////////////////////////////////////////////////////////////////////////////////
;A small utility for listing the tables in an ODBC database.
;It does not use SQL (for which different drivers require different SQL statements in
;order to list the tables etc.)
;It also does not use internal Purebasic structures.
;Developed with Purebasic 4.2 beta 4.
;Windows only.
;Unicode : Yes.
;
;By Stephen Rodriguez.
;April 2008.
;/////////////////////////////////////////////////////////////////////////////////
#SQL_SUCCESS = 0
#SQL_SUCCESS_WITH_INFO = 1
#SQL_NO_DATA = 100
#SQL_NULL_HANDLE = 0
#SQL_HANDLE_ENV = 1
#SQL_HANDLE_DBC = 2
#SQL_HANDLE_STMT = 3
#SQL_ATTR_ODBC_VERSION = 200
#SQL_OV_ODBC3 = 3
#SQL_NTS = -3
#SQL_DRIVER_COMPLETE = 1
#SQL_C_CHAR = 1
#SQL_C_WCHAR = -8
;The following compiler directive accounts for Ansi and Unicode modes.
CompilerIf #PB_Compiler_Unicode
#SQL_CHAR = #SQL_C_WCHAR
CompilerElse
#SQL_CHAR = #SQL_C_CHAR
CompilerEndIf
;Set the 'blnListAllObjects' parameter to #True if you wish to list all objects.
;You have to do this in the case of the MS Excel ODBC driver for example.
;Returns zero if no error else returns an SQL error code.
Procedure.l ODBCListTables(dsn$, blnListAllObjects = #False)
Protected result.w, env, dbc, stmt, table, len
Protected tableName$ = Space(512)
;Create an environment handle and set the environment attribute to use ODBC 3.
SQLAllocHandle_(#SQL_HANDLE_ENV, #SQL_NULL_HANDLE, @env)
SQLSetEnvAttr_(env, #SQL_ATTR_ODBC_VERSION, #SQL_OV_ODBC3, 0)
;Allocate a connection handle.
SQLAllocHandle_(#SQL_HANDLE_DBC, env, @dbc);
;Connect to the database.
result = SQLDriverConnect_(dbc, #Null, dsn$, #SQL_NTS, #Null, 0, #Null, #SQL_DRIVER_COMPLETE)&$ffff
If result = #SQL_SUCCESS Or result = #SQL_SUCCESS_WITH_INFO
;Allocate a statement handle.
SQLAllocHandle_(#SQL_HANDLE_STMT, dbc, @stmt)
;Retrieve a list of tables.
If blnListAllObjects
SQLTables_(stmt, #Null, 0, #Null, 0, #Null, 0, 0, 0)
Else
SQLTables_(stmt, #Null, 0, #Null, 0, #Null, 0, @"TABLE", #SQL_NTS)
EndIf
;Loop through the tables.
SQLBindCol_(stmt,3,#SQL_CHAR,@tableName$, 512, @len)
result = SQLFetch_(stmt)&$ffff
While result = #SQL_SUCCESS
table+1
Debug "Table " + Str(table) + " is named : " + tableName$
result = SQLFetch_(stmt)&$ffff
Wend
If result = #SQL_NO_DATA
result = #SQL_SUCCESS
EndIf
SQLFreeHandle_(#SQL_HANDLE_STMT, stmt);
EndIf
;Free the handles.
SQLFreeHandle_(#SQL_HANDLE_DBC, dbc);
SQLFreeHandle_(#SQL_HANDLE_ENV, env)
ProcedureReturn result
EndProcedure
;TEST.
;In my case I set up a DSN connection to a MS Access database named customers.
;You will need to create your own DSN connection before running this program.
ODBCListTables("DSN=customers;")
EDIT : the following simple program lists the tables in an SQLite database. It is fully crossplatform but does require PB 4.2.
Code: Select all
;/////////////////////////////////////////////////////////////////////////////////
;A small utility for listing the tables in an SQLite database.
;Developed with Purebasic 4.2 beta 4.
;Requires PB 4.2 to run.
;Platforms : All.
;Unicode : Yes.
;
;By Stephen Rodriguez.
;April 2008.
;/////////////////////////////////////////////////////////////////////////////////
UseSQLiteDatabase()
;Set the 'blnListAllObjects' parameter to #True if you wish to list all objects.
;You have to do this in the case of the MS Excel ODBC driver for example.
;Returns zero if an error.
Procedure.l SQLiteListTables(database$, blnListAllObjects = #False)
Protected result, db, sql$, table
db = OpenDatabase(#PB_Any, database$, "", "", #PB_Database_SQLite)
If db
If blnListAllObjects
sql$ = "select * from sqlite_master"
Else
sql$ = "select * from sqlite_master WHERE type='table'"
EndIf
If DatabaseQuery(db, sql$)
While NextDatabaseRow(db)
table+1
Debug "Table " + Str(table) + " is named : " + GetDatabaseString(db, 2)
Wend
result = 1
EndIf
CloseDatabase(db)
EndIf
ProcedureReturn result
EndProcedure
;TEST.
SQLiteListTables("MyDatabase.db")
