PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

GetDatabaseTables() and GetdatabaseDBNames()

https://www.purebasic.fr/english/viewtopic.php?t=29365

Page 1 of 1

GetDatabaseTables() and GetdatabaseDBNames()

Posted: Tue Oct 30, 2007 8:24 pm
by Amnesty
Good evening...

for my work I ve needed to examine all tables on our Database.
Based on an example from ABBKlaus I ve created these two functions, tested with MySQL and IBM AS400.

Code: Select all

Procedure GetDatabaseTables(Database)
  SQLTables_(PeekL(PeekL(IsDatabase(Database)+4)+4),0,"%",0,0,0,0,0,0)
EndProcedure

Procedure GetDatabaseDBNames(Database)
  SQLTables_(PeekL(PeekL(IsDatabase(Database)+4)+4),"%","%",0,0,0,0,0,0)
EndProcedure

UseODBCDatabase()
If OpenDatabase(0,"bla","lala","lala")
  GetDatabaseTables(0)
  While NextDatabaseRow(0)
    Debug "Database: " + GetDatabaseString(0,0)
    Debug "Table:" + GetDatabaseString(0,2)
    Debug "Type: " + GetDatabaseString(0,3)
  Wend
  
  GetDatabaseDBNames(0)
  While NextDatabaseRow(0)
    Debug "Database: " + GetDatabaseString(0,0)
  Wend
EndIf

Posted: Tue Oct 30, 2007 8:55 pm
by ABBKlaus
1+ for the trick with NextDatabaseRow(), this makes it easy :D

i modified your source to be more safe :

Code: Select all

CompilerSelect #PB_Compiler_Version
CompilerCase 410
  UseODBCDatabase() ; new in PB4.10
  ;Peeking SQL_HANDLE PureBasic Version 4.10 Beta 4 
  Macro SQL_HANDLE_ENV(Database) 
    PeekL(PeekL(IsDatabase(Database))-4) 
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)+4) 
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)) 
  EndMacro 
CompilerCase 4.02
  InitDatabase()
  ;Peeking SQL_HANDLE PureBasic Version 4.02
  Macro SQL_HANDLE_ENV(Database) 
    0
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(IsDatabase(Database)+4)
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(IsDatabase(Database))
  EndMacro 
CompilerEndSelect

Procedure.s GetSQLMessages(Database)
   lResult.w
   SQLState.s = Space(5) 
   NativeErrorPtr.l 
   MessageText.s = ""
   BufferLength.l = 10000 
   TextLengthPtr.l 
   DiagInfoPtr.l = -1 
   Index.l=1
   Result.s=""
   Repeat 
      res.w=SQLGetDiagRec_(3, SQL_HANDLE_STMT(Database), Index, @SQLState, @NativeErrorPtr, @MessageText, 0, @TextLengthPtr)
      If res=1
        MessageText.s = Space(TextLengthPtr)
        res.w=SQLGetDiagRec_(3, SQL_HANDLE_STMT(Database), Index, @SQLState, @NativeErrorPtr, @MessageText, BufferLength, @TextLengthPtr) 
        If res=0
          Result+SQLState+"|"+MessageText
          Debug SQLState+"|"+MessageText
          Index+1
        Else
          Break
        EndIf
      Else
        Break
      EndIf
   ForEver
   
   ProcedureReturn Result
EndProcedure

Procedure.l GetDatabaseTables(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database))
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,0,0,0,0,0,0,0)
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
    ProcedureReturn 1
  EndIf
EndProcedure 

Procedure.l GetDatabaseDBNames(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database))
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),"%",-3,"",-3,"",-3,"",-3) 
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
    ProcedureReturn 1
  EndIf
EndProcedure 

If OpenDatabase(0,"bla","lala","lala") 
  If GetDatabaseTables(0) 
    While NextDatabaseRow(0) 
      Debug "Database: " + GetDatabaseString(0,0) 
      Debug "Table:" + GetDatabaseString(0,2) 
      Debug "Type: " + GetDatabaseString(0,3) 
    Wend 
  Else
    GetSQLMessages(0)
  EndIf
  
  If GetDatabaseDBNames(0) 
    While NextDatabaseRow(0) 
      Debug "Database: " + GetDatabaseString(0,0) 
    Wend 
  Else
    GetSQLMessages(0)
  EndIf
EndIf
[Edit] Added SQLCancel_()
[Edit] Added PB4.02 Support

Posted: Wed Oct 31, 2007 8:08 am
by DoubleDutch
These should be part of the official commands.

Posted: Wed Oct 31, 2007 10:18 am
by Amnesty
I ve tested it on Oracle 10g now, and it works, but no DBNames.

@ABBKlaus:

Great Work! What do you think about renaming the followed macro to 'DatabaseID()', to keep the PB Syntax ?

Code: Select all

Macro SQL_HANDLE_STMT(Database) 
  PeekL(PeekL(IsDatabase(Database)+4)+4) 
EndMacro

Posted: Sun Nov 04, 2007 5:41 pm
by Rook Zimbabwe
Sadly this does not work in 4.02...

Posted: Mon Nov 05, 2007 1:05 pm
by ABBKlaus
Rook Zimbabwe wrote:Sadly this does not work in 4.02...
Fixed :wink: see my example above

@Amnesty
why should i rename that function ? it is the Statement handle !

Regards Klaus

Posted: Mon Nov 05, 2007 3:32 pm
by Rook Zimbabwe
Will try the code in a moment but I noticed this:

Code: Select all

CompilerCase 4.02 
  ;Peeking SQL_HANDLE PureBasic Version 4.02 
  Macro SQL_HANDLE_ENV(Database) 
    0 
  EndMacro
Interesting...

{edit} I get a message popup

LINE 33 Constant not found #EPDB
and behind it a macro error box says
MACRO ERROR
SQLCancel_(PeekL(IsDatabase(#EPDB)+4) )

:) I changed the CompilerCase 4.02 to CompilerCase 402 and now

Posted: Mon Nov 05, 2007 3:38 pm
by ABBKlaus
:oops: fixed

Posted: Mon Nov 05, 2007 3:43 pm
by Rook Zimbabwe
UseODBCDatabase() is not a function macro or linked list...
I am ; this out and initdatabase() instead... let you know in a second!

I made a simple change... the program runs but shows nothing AND no debug results (yes debug is on) :D

Code: Select all

CompilerSelect #PB_Compiler_Version 
CompilerCase 410 
  ;Peeking SQL_HANDLE PureBasic Version 4.10 Beta 4 
  Macro SQL_HANDLE_ENV(Database) 
    PeekL(PeekL(IsDatabase(Database))-4) 
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)+4) 
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)) 
  EndMacro 
CompilerCase 4.02 
  ;Peeking SQL_HANDLE PureBasic Version 4.02 
  Macro SQL_HANDLE_ENV(Database) 
    0 
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(IsDatabase(Database)+4) 
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(IsDatabase(Database)) 
  EndMacro 
CompilerEndSelect 

Procedure.l GetDatabaseTables(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database)) 
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,"%",0,0,0,0,0,0) 
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
  Debug res
    ProcedureReturn 1 
  EndIf 
EndProcedure 

Procedure.l GetDatabaseDBNames(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database)) 
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),"%",-3,"",-3,"",-3,"",-3) 
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO 
  Debug res
    ProcedureReturn 1 
  EndIf 
EndProcedure 

;UseODBCDatabase() ; PB 4.10+
InitDatabase() ; PB 4

If OpenDatabase(0,"menu.mdb","","") 
  If GetDatabaseTables(0) 
    While NextDatabaseRow(0) 
      Debug "Database: " + GetDatabaseString(0,0) 
      Debug "Table:" + GetDatabaseString(0,2) 
      Debug "Type: " + GetDatabaseString(0,3) 
    Wend 
  EndIf 
  
  If GetDatabaseDBNames(0) 
    While NextDatabaseRow(0) 
      Debug "Database: " + GetDatabaseString(0,0) 
    Wend 
  EndIf 
EndIf 
;

Posted: Mon Nov 05, 2007 4:04 pm
by ABBKlaus
If OpenDatabase(0,"menu.mdb","","")
you must first setup a dsn for your database ! This won´t work :wink:
For a complete example you can download this : http://www.purebasicpower.de/downloads/DSN-Test.zip

Posted: Mon Nov 05, 2007 4:30 pm
by Rook Zimbabwe
Note to self... do not take cold medicine and attempt to code!
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited