Issue with PB4.xx Database library [PB3.9x, ADO, OCI work!]

[gnozal]

I have a problem using the PB4.xx Database library.

I was translating an old PB 3.94 program, which uses some database queries (only SELECT).
The database is an ORACLE 11 database, and I use UseODBCDatabase().

Using PB4.xx (from 4.00 to 4.60), I get an error after the first query :

[Microsoft][ODBC driver for Oracle]Impossible d'utiliser le curseur de table de caractères sur join, avec une clause distincte union, intersection ou moins ou sur une série de résultats en lecture seule.

Translation :

[Microsoft][ODBC driver for Oracle]Can not use the character table cursor on join with a separate union clause, intersection or with a series of read-only results.

I can't post the whole code, but here is the query text :

Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxxxxx') Or (AUTOMATE.NOMEXE= 'yyyyyy')) And (CUPULE.NUMERO = 'xxxxxxxx') ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC

Now, the same code gives the expected results (compared with SQLTalk) using PB3.94.
Furthermore, using Srod's ADOmate_DatabaseQuery() with PB4.51 also works...

So I suppose there might be something wrong with the PB4.xx DatabaseQuery()?
(I am no database nor SQL guru...)

[ABBKlaus]

You can try tracing and compare the results.
http://msdn.microsoft.com/en-us/library ... s.85).aspx

BR Klaus

[gnozal]

Thanks ABBKlaus !

The problem seems to be the SQLSetStmtAttr(SQL_ATTR_CURSOR_TYPE) call with PB4.xx.

Here are the ODBC traces :

Purebasic0 d98-bd4 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C16C8
SQLHANDLE * 0012FF40

Purebasic0 d98-bd4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C16C8
SQLHANDLE * 0x0012FF40 ( 0x003c2430)

Purebasic0 d98-bd4 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C2430

Purebasic0 d98-bd4 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C2430

Purebasic0 d98-bd4 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C16C8
SQLHANDLE * 003A2A5C

Purebasic0 d98-bd4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 003C16C8
SQLHANDLE * 0x003A2A5C ( 0x003c2430)

Purebasic0 d98-bd4 ENTER SQLSetStmtAttr
SQLHSTMT 003C2430
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000002
SQLINTEGER -6

Purebasic0 d98-bd4 EXIT SQLSetStmtAttr with return code 1 (SQL_SUCCESS_WITH_INFO)
SQLHSTMT 003C2430
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000002 (BADMEM)
SQLINTEGER -6

DIAG [01S02] [Microsoft][ODBC driver for Oracle]Type de curseur modifié (0)

Purebasic0 d98-bd4 ENTER SQLExecDirect
HSTMT 003C2430
UCHAR * 0x009F6160 [ 408] "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxx') Or (AUTOMATE.NOMEXE= 'xxx')) And (CUPULE.NUMERO = 'x') ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
SDWORD 408

Purebasic0 d98-bd4 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 003C2430
UCHAR * 0x009F6160 [ 408] "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxx') Or (AUTOMATE.NOMEXE= 'xxx')) And (CUPULE.NUMERO = 'x') ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
SDWORD 408

DIAG [HYC00] [Microsoft][ODBC driver for Oracle]Impossible d'utiliser le curseur de table de caractères sur join, avec une clause distincte union, intersection ou moins ou sur une série de résultats en lecture seule (0)

"PureBasic26461 e04-504 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C16C8
SQLHANDLE * 0012FF50

"PureBasic26461 e04-504 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C16C8
SQLHANDLE * 0x0012FF50 ( 0x008c2430)

"PureBasic26461 e04-504 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C2430

"PureBasic26461 e04-504 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C2430

"PureBasic26461 e04-504 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C16C8
SQLHANDLE * 008A20FC

"PureBasic26461 e04-504 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 008C16C8
SQLHANDLE * 0x008A20FC ( 0x008c2430)

"PureBasic26461 e04-504 ENTER SQLExecDirect
HSTMT 008C2430
UCHAR * 0x00EF0A40 [ 408] "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxx') Or (AUTOMATE.NOMEXE= 'xxx')) And (CUPULE.NUMERO = 'x') ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
SDWORD 408

"PureBasic26461 e04-504 EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
HSTMT 008C2430
UCHAR * 0x00EF0A40 [ 408] "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxx') Or (AUTOMATE.NOMEXE= 'xxx')) And (CUPULE.NUMERO = 'x') ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
SDWORD 408

[ABBKlaus]

Heres an explanation for the errorcodes : http://msdn.microsoft.com/en-us/library ... l.80).aspx

It seems its only a warning : 01S02 Option value changed

The character string value returned for SQLSTATE consists of a two-character class value followed by a three-character subclass value. A class value of 01 indicates a warning and is accompanied by a return code of SQL_SUCCESS_WITH_INFO. Class values other than 01, except for the class IM, indicate an error and are accompanied by a return code of SQL_ERROR. The class IM is specific to warnings and errors that derive from the implementation of ODBC. The subclass value 000 in any class is for implementation defined conditions within the given class. The assignment of class and subclass values is defined by SQL-92.

BR Klaus

[gnozal]

Thanks Klaus.

Heres an explanation for the errorcodes : http://msdn.microsoft.com/en-us/library ... l.80).aspxIt seems its only a warning : 01S02 Option value changed

It might only be a warning, but it's the only difference.
And the error message mentions a cursor problem, and as far as I understand it SQLSetStmtAttr() changes the cursor type.

I can't use PB's genuine database functions anymore because of this issue, so I thought I mention it.
Anyway, ADOmate works great.

[ABBKlaus]

You can try this code from JustinJack

Add the call To SQLSetStmtAttr(SQL_ATTR_CURSOR_TYPE)
And if you get the same errormessage you could make a bugreport about this issue.

viewtopic.php?p=331684

BR Klaus

[gnozal]

You can try this code from JustinJack

Add the call To SQLSetStmtAttr(SQL_ATTR_CURSOR_TYPE)
And if you get the same errormessage you could make a bugreport about this issue.

viewtopic.php?p=331684

BR Klaus

Thanks, I tried this code.

Code: Select all

dbNum = initDataBase()
If dbNum > 0
  If myOpenDataBase(dbNum, "xxxx", "xxxxx", "xxxxxxx")
    Debug "Ok"
    qry.s = "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxxx') Or (AUTOMATE.NOMEXE= 'xxxx')) And (CUPULE.NUMERO = 'xxxxxxxx')  ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
    *ptr = myDataBaseQuery(dbNum, qry.s, 5000)
    Debug *ptr ; = 0
    numRecords = RowsFetched( dbNum )
    Debug numRecords ; = 0
    endDataBaseQuery( dbNum )
    myCloseDataBase( dbNum )
  EndIf
EndIf

I could connect to the database, and the query returned without error.
However, RowsFetched( dbNum ) = 0 ...?

[ABBKlaus]

You have to add a ResultColumn()

[gnozal]

You have to add a ResultColumn()

Doh !
Thanks a lot for your help Klaus !

The following code works as expected (like PB3.94 database library and ADOmate) :

Code: Select all

dbNum = initDataBase()

If dbNum > 0
  If myOpenDataBase(dbNum, "xxx", "xxx", "xxx")
    Debug "Ok"
     qry.s = "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxxx') Or (AUTOMATE.NOMEXE= 'xxxx')) And (CUPULE.NUMERO = 'xxxxxxxx')  ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
    
    addResultColumn(dbNum, #PB_Database_String, 256)
    addResultColumn(dbNum, #PB_Database_String, 256)
    addResultColumn(dbNum, #PB_Database_String, 256)
    addResultColumn(dbNum, #PB_Database_String, 256)
    addResultColumn(dbNum, #PB_Database_String, 256)
    
    *ptr = myDataBaseQuery(dbNum, qry.s, 5000)
    Debug *ptr ; Ok !
    
    numRecords = RowsFetched( dbNum )
    Debug numRecords ; Ok !
    
    
    For k = 1 To numRecords
      Debug PeekS(rowCol(dbNum, k,  1), 256) 
      Debug PeekS(rowCol(dbNum, k,  2), 256) 
      Debug PeekS(rowCol(dbNum, k,  3), 256) 
      Debug PeekS(rowCol(dbNum, k,  4), 256) 
      Debug PeekS(rowCol(dbNum, k,  5), 256) 
      Debug "---"
    Next
    
    endDataBaseQuery( dbNum )
    
    myCloseDataBase( dbNum )
  EndIf
EndIf

[gnozal]

I also tried OCILIB : works as expected, no problems like with the PB genuine database library.

Code: Select all

;
;{ OCI LIB [C Driver for Oracle](http://orclib.sourceforge.net)
;
Import "libocilibw.a" ; libociliba.a = ANSI, libocilibw.a = UNICODE
  OCI_Initialize(*ErrorHandler, *LibPath, Mode) As "_OCI_Initialize@12" ; Initialize the OCI library - Returns TRUE on success otherwise FALSE
  OCI_ConnectionCreate(Database$, User$, Password$, Mode) As "_OCI_ConnectionCreate@16" ; Create a physical connection to an Oracle database server - Connection handle on success or NULL on failure
  OCI_StatementCreate(hConnection) As "_OCI_StatementCreate@4" ; Create a statement object - Returns a statement handle on success otherwise NULL
  OCI_StatementFree(hStatement) As "_OCI_StatementFree@4" ; Free a statement and all resources associated to it (resultsets ...) 
  OCI_ExecuteStmt(hStatement, SQL$) As "_OCI_ExecuteStmt@8" ; Execute a SQL statement or PL/SQL block - Returns TRUE on success otherwise FALSE
  OCI_GetResultset(hStatement) As "_OCI_GetResultset@4" ; Retrieve the resultset handle from an executed statement - Returns a resultset handle on success otherwise NULL
  OCI_GetColumnCount(hResultSet) As "_OCI_GetColumnCount@4" ; Return the number of columns in the resultset
  OCI_FetchNext(hResultSet) As "_OCI_FetchNext@4" ; Fetch the next row of the resultset - Returns TRUE on success otherwise FALSE (empty, last row already fetched, an error occurred)
  OCI_GetString_Pointer(hResultSet, ColumnIndex) As "_OCI_GetString@8" ; Return the current string value of the column at the given index in the resultset or NULL if index is out of bounds
  OCI_Cleanup() As "_OCI_Cleanup@0" ; Clean up all resources allocated by the library
  OCI_ErrorGetOCICode(hError) As "_OCI_ErrorGetOCICode@4" ; Retrieve Oracle Error code from error handle
  OCI_ErrorGetString(hError) As "_OCI_ErrorGetString@4" ; Retrieve error message from error handle
EndImport
Procedure.s OCI_GetString(hResultSet, ColumnIndex) ; Return the current string value of the column at the given index in the resultset or '' if index is out of bounds
  ;
  Protected OCI_Result, ReturnValue.s
  ;
  OCI_Result = OCI_GetString_Pointer(hResultSet, ColumnIndex) 
  If OCI_Result
    ReturnValue = PeekS(OCI_Result)
  EndIf
  ;
  ProcedureReturn ReturnValue
  ;
EndProcedure
ProcedureC OCI_ErrorHandler(hError) ; OCI error handler MUST be a ProcedureC !!!
  ;
  Shared OCI_ErrorNumber, OCI_ErrorString.s
  If hError
    OCI_ErrorNumber = OCI_ErrorGetOCICode(hError)
    OCI_ErrorString = PeekS(OCI_ErrorGetString(hError))
    OCI_ErrorString = RemoveString(OCI_ErrorString, Chr(10))
  EndIf
  Debug "+++ OCI ERROR " + Str(OCI_ErrorNumber) + " (" + OCI_ErrorString + ") +++"
  ;
EndProcedure
;  
#OCI_ENV_DEFAULT =                    0
#OCI_ENV_THREADED =                   1
#OCI_ENV_CONTEXT =                    2
#OCI_ENV_EVENTS =                     4
#OCI_SESSION_DEFAULT =                0
#OCI_SESSION_XA =                     1
#OCI_SESSION_SYSDBA =                 2
#OCI_SESSION_SYSOPER =                4
#OCI_SESSION_PRELIM_AUTH =            8
;}
;
;
If OCI_Initialize(@OCI_ErrorHandler(), #Null, #OCI_ENV_DEFAULT)
  ;
  hConnection = OCI_ConnectionCreate("xxx", "xxx", "xxx", #OCI_SESSION_DEFAULT)
  If hConnection
    ;
    ;
    hStatement = OCI_StatementCreate(hConnection) ; Create statement
    ;
    ;
    TexteSQL$ = "Select PLAQUE.NUMERO, to_char(plaque.datec,'DDMMYYYYHH24MISS'), ELEMENT.LIB, CUPULE.X, CUPULE.Y FROM CUPULE,PLAQUE,SECTION,AUTOMATE,ELEMENT WHERE (CUPULE.PLAQUE = PLAQUE.ID) And (PLAQUE.ID = SECTION.PLAQUE)And (SECTION.DIST = AUTOMATE.ID) And (AUTOMATE.ID = ELEMENT.ID) And ((AUTOMATE.NOMEXE= 'xxxx') Or (AUTOMATE.NOMEXE= 'xxxx')) And (CUPULE.NUMERO = 'xxxxxxxx')  ORDER BY PLAQUE.DATEC,PLAQUE.NUMERO ASC"
    ;
    If OCI_ExecuteStmt(hStatement, TexteSQL$)
      ;
      hResultSet = OCI_GetResultset(hStatement)
      ;
      If hResultSet
        ;
        NbColumns = OCI_GetColumnCount(hResultSet)
        ;
        While (OCI_FetchNext(hResultSet))
          ;
          Result$ = ""
          For Index = 1 To NbColumns
            Result$ + OCI_GetString(hResultSet, Index) + ":"
          Next
          Debug Result$
          ;
        Wend
      ;
      EndIf
      ;
    Else
      ;
      MessageRequester("ERROR", "SQL error !" + Chr(10) + Chr(10) + "ERROR " + Str(OCI_ErrorNumber) + " (" + OCI_ErrorString + ")", #MB_ICONERROR)
      ;
    EndIf
    ;
    ;
    OCI_StatementFree(hStatement) ; Free statement
    ;
    ;
  Else
    ;
    MessageRequester("ERROR", "Could not connect to database !" + Chr(10) + Chr(10) + "ERROR " + Str(OCI_ErrorNumber) + " (" + OCI_ErrorString + ")", #MB_ICONERROR)
    ;
  EndIf
  ;
  ;
  OCI_Cleanup()
  ;
  ;
Else
  ;
  MessageRequester("ERROR", "Could not initialize OCILIB !", #MB_ICONERROR)
  ;
EndIf

[Michael42]

Just checking back on this topic...

In looking for a native way to integrate Oracle 11g\12c is OCILIB or any other native option available to connect to Oracle databases?

If OCILIB is an option can some please spell it out for mere mortals how to use it?

Whats step 1 (2,3)?

Thanks!