ODBC: Get the ErrorCode from ODBC

lapo · Post by **lapo** » Mon Nov 13, 2006 9:35 pm

Hallo!

I called the function InitDatabase and connected me to the Database (Oracle) with OpenDatabase. After this i get datarows with the function DatabaseQuery. If this or other functions fails, how can i get the Errorcode (not the errortext with DatabaseError). I mean to now that the ODBC-API and/or the ODBC-Driver from the Database returns ErrorCodes....

Bye

Ralf

Trond · Post by **Trond** » Mon Nov 13, 2006 10:12 pm

I think ErrorCode = DatabaseQuery(...) will give you the error code, but I haven't tested it.

bobobo · Post by **bobobo** » Tue Nov 14, 2006 5:23 pm

0 if error
and value if ok

http://www.purebasic.fr/english/viewtop ... 584#170584

ABBKlaus · Post by **ABBKlaus** » Tue Nov 14, 2006 6:59 pm

maybe you mean this ?

Code: Select all

#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
  Protected ODBCLib.l
  Errortext$=""
  
  ODBCLib=OpenLibrary(#PB_Any,"ODBCCP32.DLL")
  If ODBCLib
    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)
        ;SQLResult=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

Debug GetSQLError()

lapo · Post by **lapo** » Wed Nov 15, 2006 2:17 pm

Hello ABBKlaus,

i will try and examine it...
After testing the code i post the result.

Bye

Ralf

lapo · Post by **lapo** » Thu Nov 16, 2006 1:55 pm

Hi,

i tested the sourcecode, but it doesnt work....

I called the GetSQLError Procedure while i'm open the Database (see my code):

Code: Select all

  ; Datenbank mittels ODBC für Lesezugriffe öffnen
  RetVal = OpenDatabase(dbAbisRead, DsnName, DbUserDtn, DbUserDtnPw)
  If RetVal = 0
  
    dbFehler = GetSQLError()
    MsgTxt = "Fehlermeldung:" + DsnName + dbFehler + Chr(13) + Chr(13) + " Das Programm wird beendet...."
    MessageRequester("Datenbankfehler GetSQLError", MsgTxt, #PB_MessageRequester_Ok)
    
    
    dbFehler = DatabaseError()
    MsgTxt = "Es ist ein Fehler bei dem lesenden Verbindungsaufbau zur Datenbank aufgetreten (keine ODBC-Verknüpfung mit dem Namen '" + DsnName + "' vorhanden, der Name des Anwenders oder das Kennwort sind falsch, etc.)!! Die Fehlermeldung vom ODBC-Treiber lautet:. " + Chr(13)+ Chr(13) + dbFehler + Chr(13) + Chr(13) + " Das Programm wird beendet...."
    MessageRequester("Datenbankfehler", MsgTxt, #PB_MessageRequester_Ok)
    CloseOpenFileDbs()
    ProcedureReturn 0
  EndIf

GetSQLError gives an empty string to my variable dbFehler, but the PB-Function DatabaseError returned the follow:

[Oracle][ODBC][ORA]ORA-12170: TNS: Connect Timout aufgetreten

Is SQLInstallerError not only for the installation?
Is the ODBC-Function SQLError not the better way as SQLInstallerError and when yes: how get i'm the parameters for this function?

Bye

Ralf

ABBKlaus · Post by **ABBKlaus** » Thu Nov 16, 2006 8:32 pm

yes SQLInstallerError is part of the Installer DLL API Reference Functions http://msdn.microsoft.com/library/en-us ... erence.asp

I use the above function to detect errors in keyword value pairs.
Useful with SQLConfigDataSource.

SQLError is a deprecated function see here http://msdn.microsoft.com/library/en-us ... lerror.asp

Maybe you need SQLGetDiagField Function :http://msdn.microsoft.com/library/en-us ... gfield.asp
or SQLGetDiagRec http://msdn.microsoft.com/library/en-us ... iagrec.asp

Regards Klaus

lapo · Post by **lapo** » Fri Nov 24, 2006 2:50 pm

Hi ABBKlaus!

I was ill and come now to post to you...

You write from the API-Function SQLGetDiagRec and SQLGetDiagField. How can i get the handles from PB? With handles i mean the handles for the parameter HandleType (= SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT, SQL_HANDLE_DESC)

Code: Select all

SQLRETURN SQLGetDiagRec(
     SQLSMALLINT     HandleType,
     SQLHANDLE     Handle,         <----> How get i this handles?
     SQLSMALLINT     RecNumber,
     SQLCHAR *     Sqlstate,
     SQLINTEGER *     NativeErrorPtr,
     SQLCHAR *     MessageText,
     SQLSMALLINT     BufferLength,
     SQLSMALLINT *     TextLengthPtr);

Bye Ralf

ABBKlaus · Post by **ABBKlaus** » Fri Nov 24, 2006 5:46 pm

Posted this a long time ago :

Code: Select all

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

i don´t know if it works with PB4.01

But you could test and report back later ...

Regards Klaus

lapo · Post by **lapo** » Sat Nov 25, 2006 3:59 pm

Hi!

Thank you for your answer. You posted

>> Posted this a long time ago :
>> Code:
>> Handle=PeekL(IsDatabase(#DB1)+4)
>>
>> i don´t know if it works with PB4.01 Question

What for a handle is returned? Is this the handle for the TypeHandle SQL_HANDLE_ENV or SQL_HANDLE_DBC?
Is this a documented feature to get the handle?
Why do you use PeekL and not PeekW? If i now handles are 16 bit-values.

How can i get the other handles for SQL_HANDLE_STMT, SQL_HANDLE_DESC?

I tested the follow code:

Code: Select all

; Datenbank mittels ODBC für Schreibzugriffe öffnen
  RetVal = OpenDatabase(dbAbisWrite, DsnName,  DbUserDtn, DbUserDtnPw)
  If RetVal = 0
    
    hdbAbisWrite = PeekL(IsDatabase(dbAbisWrite)+4)              ; <--- exception-error
    DbgPrtkAusgabe("hdbAbisWrite:       " + Str(hdbAbisWrite))
    
    dbFehler = DatabaseError()
    MsgTxt = "Es ist ein Fehler bei dem schreibenden Verbindungsaufbau zur Datenbank aufgetreten (keine ODBC-Verknüpfung mit dem Namen '" + DsnName + "' vorhanden, der Name des Anwenders oder das Kennwort sind falsch, etc.)!! Die Fehlermeldung vom ODBC-Treiber lautet:. " + Chr(13)+ Chr(13) + dbFehler + Chr(13) + Chr(13) + " Das Programm wird beendet...."
    MessageRequester("Datenbankfehler", MsgTxt, #PB_MessageRequester_Ok)
    CloseOpenFileDbs()
    ProcedureReturn 0
  EndIf

If the database not opened an exception-error occurs (see below) and the software hangs.... I can't get the error-code.

Bye Ralf

ABBKlaus · Post by **ABBKlaus** » Sat Nov 25, 2006 10:23 pm

the handle shoudl be SQL_HANDLE_STMT
And this works only when the database is successfully opened :

Code: Select all

PBdb=IsDatabase(#DB)
If PBdb<>0
  dbh=PeekL(PBdb+4)
  Debug dbh
EndIf

Regards Klaus

lapo · Post by **lapo** » Sat Nov 25, 2006 10:37 pm

the handle shoudl be SQL_HANDLE_STMT

O.K. - next time, i will try it with an SQL-Statement...

And this works only when the database is successfully opened :

Yes it's right, but an error can also apear when the database will opened.

Bye Ralf