GetDatabaseTables() and GetdatabaseDBNames()

Share your advanced PureBasic knowledge/code with the community.
Post Reply
Amnesty
User
User
Posts: 54
Joined: Wed Jul 04, 2007 4:34 pm
Location: Germany

GetDatabaseTables() and GetdatabaseDBNames()

Post 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
Top
ABBKlaus
Addict
Addict
Posts: 1143
Joined: Sat Apr 10, 2004 1:20 pm
Location: Germany

Post 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
Last edited by ABBKlaus on Mon Nov 05, 2007 3:59 pm, edited 8 times in total.
Top
User avatar
DoubleDutch
Addict
Addict
Posts: 3220
Joined: Thu Aug 07, 2003 7:01 pm
Location: United Kingdom
Contact:
Contact DoubleDutch

Post by DoubleDutch »

These should be part of the official commands.
https://deluxepixel.com <- My Business website
https://reportcomplete.com <- School end of term reports system
Top
Amnesty
User
User
Posts: 54
Joined: Wed Jul 04, 2007 4:34 pm
Location: Germany

Post 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
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post by Rook Zimbabwe »

Sadly this does not work in 4.02...
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
ABBKlaus
Addict
Addict
Posts: 1143
Joined: Sat Apr 10, 2004 1:20 pm
Location: Germany

Post 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
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post 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
Last edited by Rook Zimbabwe on Mon Nov 05, 2007 3:38 pm, edited 1 time in total.
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
ABBKlaus
Addict
Addict
Posts: 1143
Joined: Sat Apr 10, 2004 1:20 pm
Location: Germany

Post by ABBKlaus »

:oops: fixed
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post 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 
;
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
ABBKlaus
Addict
Addict
Posts: 1143
Joined: Sat Apr 10, 2004 1:20 pm
Location: Germany

Post 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
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post by Rook Zimbabwe »

Note to self... do not take cold medicine and attempt to code!
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
Post Reply

Return to “Tricks 'n' Tips”