PureBasic Forums - English

Posted: **Mon Feb 28, 2005 11:26 am**

Can anybody tell me the standard ODBC API equivalent of the follwing MySQL commands:

SHOW TABLES;

and

SHOW FULL COLUMNS FROM TableName;

Posted: **Mon Feb 28, 2005 11:50 am**

You don't need to translate the query to some obscure API function. You send the query like this:

Code: Select all

DatabaseQuery("SHOW FULL COLUMNS FROM TableName;")

Check the Database library section of the PureBasic help.

Posted: **Mon Feb 28, 2005 1:06 pm**

I'm interested in finding out how to find what tables are present in an access database...

Is there a way using sql?

-Anthony

Posted: **Mon Feb 28, 2005 6:01 pm**

@El_Choni i tried the standard SQL Driver :
"Microsoft Access Driver (*.mdb)"

[Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.

ps: sorry for the german driver message

Posted: **Mon Feb 28, 2005 7:12 pm**

I don't know how to translate "SHOW TABLES" to MSAccess SQL flavour. OTOH, this worked:

I ran the Database sample included with PureBasic without any change. I selected a "*.mdb" included with MS Access named 'Neptuno.mdb', which is translated to Spanish, 'Pedidos' being one of the tables in it. In the input line, I typed:

Code: Select all

SELECT * FROM Pedidos;

And it showed the contents of the table. I hope this helps a bit.

Posted: **Mon Feb 28, 2005 8:02 pm**

Ok, to show the table names this has to be done in MS Access (found it after some googling):

* Activate Tools-Options-View-Show-System objects.
* Select Tools-Security-User and group Permissions. Select MSysObjects from object names. Add 'Read Design' and Read Permissions.

Run this query:

Code: Select all

SELECT MSysObjects.Name FROM MsysObjects WHERE (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

Posted: **Mon Feb 28, 2005 8:53 pm**

El_Choni wrote:You don't need to translate the query to some obscure API function. You send the query like this:
Code: Select all
DatabaseQuery("SHOW FULL COLUMNS FROM TableName;") 
Check the Database library section of the PureBasic help.

Hi El_Choni,

Sorry but you've misunderstood the question, I know how to do it with MySQL.

What I want to know is if there is a generic way that works with all ODBC
datasources.

Thanks

Weave

Posted: **Mon Feb 28, 2005 10:55 pm**

El_Choni: I wonder if there is another way, I can't have the user start messing with access...

-Anthony

Posted: **Mon Feb 28, 2005 11:05 pm**

@weavster: I don't know, I don't know too much about ODBC.

@DoubleDutch: I understand, but I don't know other ways of doing this. Maybe with some COM (ADO?).

Posted: **Tue Mar 01, 2005 12:34 am**

this should work :

Code: Select all

#DB1=1
#SQL_SUCCESS                        =   0
#SQL_SUCCESS_WITH_INFO              =   1

OpenDatabase(#DB1,ODBCDatabaseName$,User$,Password$)

Handle=PeekL(IsDatabase(#DB1)+4)

res=SQLTables_(Handle,0,0,0,0,0,0,0,0)
res&$FFFF
If res = #SQL_SUCCESS Or res = #SQL_SUCCESS_WITH_INFO
    Debug "#SQL_SUCCESS"
    strlen=128+1
    string1$=Space(strlen-1):strlen1.l=0
    string2$=Space(strlen-1):strlen2.l=0
    string3$=Space(strlen-1):strlen3.l=0
    string4$=Space(strlen-1):strlen4.l=0
    string5$=Space(strlen-1):strlen5.l=0
    
    SQLBindCol_(Handle,1,1,@string1$,strlen,@strlen1)
    SQLBindCol_(Handle,2,1,@string2$,strlen,@strlen2)
    SQLBindCol_(Handle,3,1,@string3$,strlen,@strlen3)
    SQLBindCol_(Handle,4,1,@string4$,strlen,@strlen4) ;#SQL_C_CHAR=1

    While SQLFetch_(Handle)&$FFFF=0
      Debug string1$+" "+string2$+" "+string3$+" "+string4$
    Wend
EndIf

Got the info from Bill G. see link below

http://msdn.microsoft.com/library/defau ... erence.asp

Posted: **Tue Mar 01, 2005 2:35 am**

@ABBKlaus: you can't imagine how much your post is going to help me with my current job. Oh, Yes!!!!

Thank you very much.

Posted: **Tue Mar 01, 2005 3:05 pm**

Thanks ABBKlaus

Posted: **Tue Mar 01, 2005 4:50 pm**

Nice solution, Thanks!

Posted: **Thu Mar 03, 2005 4:30 pm**

ABBKlaus,

I've tried to open many different databases and while I can open any database sucessfully (#SQL_SUCCESS) ...

DEBUG returns 1 long empty string (128 bytes * 4)

Perhaps I should be receiving res = #SQL_SUCCESS_WITH_INFOand, if so, how

--blueb

Posted: **Thu Mar 03, 2005 10:36 pm**

@blueb ,

the example wasn´t quite complete

I tested with MySQL Server V4.1 where the only privileg i assigned was GRANT to test your SQL_NO_DATA problem

Code: Select all

#DB1=1 
#ODBC_ADD_DSN                       =   1 ; Add Data source
#ODBC_ADD_SYS_DSN                   =   4 ; Add SYSTEM Data source
#ODBC_CONFIG_DSN                    =   2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN                    =   3 ; Remove Data source
#ODBC_REMOVE_SYS_DSN                =   6 ; Remove SYSTEM Data source
#SQL_SUCCESS                        =   0
#SQL_SUCCESS_WITH_INFO              =   1
#SQL_ERROR                          =  -1
#SQL_NO_DATA                        = 100
#SQL_MAX_MESSAGE_LENGTH             = 512
#ODBC_ERROR_GENERAL_ERR             =   1
#ODBC_ERROR_INVALID_BUFF_LEN        =   2
#ODBC_ERROR_INVALID_HWND            =   3
#ODBC_ERROR_INVALID_STR             =   4
#ODBC_ERROR_INVALID_REQUEST_TYPE    =   5
#ODBC_ERROR_COMPONENT_NOT_FOUND     =   6
#ODBC_ERROR_INVALID_NAME            =   7
#ODBC_ERROR_INVALID_KEYWORD_VALUE   =   8
#ODBC_ERROR_INVALID_DSN             =   9
#ODBC_ERROR_INVALID_INF             =  10
#ODBC_ERROR_REQUEST_FAILED          =  11
#ODBC_ERROR_INVALID_PATH            =  12
#ODBC_ERROR_LOAD_LIB_FAILED         =  13
#ODBC_ERROR_INVALID_PARAM_SEQUENCE  =  14
#ODBC_ERROR_INVALID_LOG_FILE        =  15
#ODBC_ERROR_USER_CANCELED           =  16
#ODBC_ERROR_USAGE_UPDATE_FAILED     =  17
#ODBC_ERROR_CREATE_DSN_FAILED       =  18
#ODBC_ERROR_WRITING_SYSINFO_FAILED  =  19
#ODBC_ERROR_REMOVE_DSN_FAILED       =  20
#ODBC_ERROR_OUT_OF_MEM              =  21
#ODBC_ERROR_OUTPUT_STRING_TRUNCATED =  22

Procedure.s GetSQLError()
  Protected SQLError.w
  Protected ErrorMSGLen.w
  Protected pfErrorCode.l
  Protected iError.l
  Errortext$=""
  
  ODBCLib=OpenLibrary(#PB_Any,"ODBCCP32.DLL")
  If ODBCLib<>0
    For SQLError=1 To 8
      pcbErrorMsg=0
      iError=SQLError
      cbErrorMsgMax=#SQL_MAX_MESSAGE_LENGTH
      pfErrorCode=0
      ErrorMSGBuf=AllocateMemory(cbErrorMsgMax)
      If ErrorMSGBuf<>0
        SQLResult=CallFunction(ODBCLib,"SQLInstallerError",iError,@pfErrorCode,ErrorMSGBuf,(cbErrorMsgMax-1),@ErrorMSGLen)
        Debug Str(SQLError)+" : Res="+Str(SQLResult)
        Select SQLResult
          Case #SQL_SUCCESS
            Debug "ODBC: SQL SUCCESS"
          Case #SQL_SUCCESS_WITH_INFO
            Debug "ODBC: SQL SUCCESS WITH INFO"
          Case #SQL_NO_DATA
            Debug "ODBC: SQL NO DATA"
          Case #SQL_ERROR
            Debug "ODBC: SQL ERROR"
        EndSelect
        If ErrorMSGLen<>0
          ErrorMsg$=PeekS(ErrorMSGBuf,ErrorMSGLen)
          Errortext$+Str(iError)+"="+ErrorMsg$+Chr(13)
          Debug "ODBC : Fehler "+Str(iError)+"="+ErrorMsg$
        EndIf
        FreeMemory(ErrorMSGBuf)
      EndIf
    Next
    CloseLibrary(ODBCLib)
  EndIf
  If Errortext$<>""
    MessageRequester("SQL Error",Errortext$,#PB_MessageRequester_OK)
  EndIf
  ProcedureReturn Errortext$
EndProcedure

Procedure.b MakeConnection(lpszDriver$,strAttributes$)
  ODBCLib=OpenLibrary(#PB_Any,"ODBCCP32.DLL")
  If ODBCLib
    Debug "ODBC: '"+strAttributes$+"'"
    MyMemory=AllocateMemory(Len(strAttributes$))
    CopyMemory(@strAttributes$,MyMemory,Len(strAttributes$))
    For L=1 To Len(strAttributes$)
      If PeekB(MyMemory+l-1)=Asc(";")
        PokeB(MyMemory+l-1,0)
      EndIf
    Next
    Result=CallFunction(ODBCLib,"SQLConfigDataSource",0,#ODBC_ADD_DSN,lpszDriver$,MyMemory)
    
    CloseLibrary(ODBCLib)
    
    If Result=#FALSE
      Debug "SQLConfigDataSource fehlgeschlagen"
      GetSQLError()
    EndIf
    FreeMemory(MyMemory)
    ProcedureReturn Result
  EndIf
EndProcedure

Procedure show_error(result)
  Select result
    Case #SQL_SUCCESS
      Debug "#SQL_SUCCESS"
    Case #SQL_SUCCESS_WITH_INFO
      Debug "#SQL_SUCCESS_WITH_INFO"
    Case #SQL_ERROR
      Debug "#SQL_ERROR"
    Case #SQL_NO_DATA
      Debug "#SQL_NO_DATA"
  EndSelect
EndProcedure

If InitDatabase()
  crlf.s=Chr(13)+Chr(10)
  DSN2$          = "test"                   ;dieser Name taucht im ODBC auf 
  UID2$          = "epuser"                 ;UserId 
  PWD2$          = "12345678"               ;Passwort 
  SERVER2$       = "KLAUSP4"              ;Name of a server running SQL Server on the network 
  ADDRESS2$      = "KLAUSP4"
  NETWORK2$      = "DBMSSOCN"               ;VerbindungsArt 
  DRIVER2$       = "MySQL ODBC 3.51 Driver"
  QUERYLOG_ON2$  = "no"                     ;default no : enables long query-runs 
  QUERYLOGFILE2$ = ""                       ;Full path and name of the file used to log long-running queries. 
  QUERYLOGTIME2$ = "1"                      ;Digit character string specifying the threshold (in milliseconds) 
  STATSLOG_ON2$  = "no"                     ;Enables driver performance logging 
  STATSLOGFILE2$ = ""                       ;Full pth of Log Driver Performance 
  DATABASE2$     = "test"                   ;Name of the Default database for the  connection 
  DESCRIPTION2$  = ""                       ;Description 
  ATTRIB2$="DSN="+DSN2$+";"+crlf 
  ATTRIB2$+"DESCRIPTION="+DESCRIPTION2$+";"+crlf 
  ATTRIB2$+"SERVER="+SERVER2$+";"+crlf 
  ATTRIB2$+"ADDRESS="+ADDRESS2$+";"+crlf 
  ATTRIB2$+"NETWORK="+NETWORK2$+";"+crlf 
  ATTRIB2$+"DATABASE="+DATABASE2$+";"+crlf 
  ATTRIB2$+"StatsLog_On="+STATSLOG_ON2$+";"+crlf 
  ATTRIB2$+"StatsLogFile="+STATSLOGFILE2$+";"+crlf 
  ATTRIB2$+"QueryLog_on="+QUERYLOG_ON2$+";"+crlf 
  ATTRIB2$+"QueryLogFile="+QUERYLOGFILE2$+";"+crlf 
  ATTRIB2$+"QueryLogTime="+QUERYLOGTIME2$+";" 
  If MakeConnection(DRIVER2$,ATTRIB2$) 
    Debug "ODBC: OPDB MakeConnection OK"
    If OpenDatabase(#DB1,DSN2$,UID2$,PWD2$)
      Handle=PeekL(IsDatabase(#DB1)+4) 
      result.w=SQLTables_(Handle,0,0,0,0,0,0,0,0)
      show_error(result)
      If result = #SQL_SUCCESS Or res = #SQL_SUCCESS_WITH_INFO 
        strlen=128+1 
        string1$=Space(strlen-1):strlen1.l=0 
        string2$=Space(strlen-1):strlen2.l=0 
        string3$=Space(strlen-1):strlen3.l=0 
        string4$=Space(strlen-1):strlen4.l=0 
        string5$=Space(strlen-1):strlen5.l=0 
        SQLBindCol_(Handle,1,1,@string1$,strlen,@strlen1) 
        SQLBindCol_(Handle,2,1,@string2$,strlen,@strlen2) 
        SQLBindCol_(Handle,3,1,@string3$,strlen,@strlen3) 
        SQLBindCol_(Handle,4,1,@string4$,strlen,@strlen4) ;#SQL_C_CHAR=1 
        Repeat
          result.w=SQLFetch_(Handle)
          show_error(result)
          If result = #SQL_SUCCESS Or result = #SQL_SUCCESS_WITH_INFO
            Debug string1$+" "+string2$+" "+string3$+" "+string4$ 
          EndIf
        Until result = #SQL_ERROR Or result = #SQL_NO_DATA
      EndIf 
    Else
      Debug "OpenDatabase fehlgeschlagen"
    EndIf
  Else
    Debug "Makeconnection fehlgeschlagen"
  EndIf
Else
  Debug "Initdatabase fehlgeschlagen"
EndIf