PureBasic Forums - English

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;")

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")

Code: Select all

ODBCListTables("DSN=[myWorkingSqlServerOdbc];")

Code: Select all

#SQL_SUCCESS_WITH_INFO = 1

Procedure ...
...
 If result = #SQL_SUCCESS OR result = #SQL_SUCCESS_WITH_INFO
...
EndProcedure

ODBCListTables ("DSN=DSN_SQLServer;UID=userid;PWD=password;")