[Gambas-user] ODBC Access crash

[Brian G]

Making direct calls to the odbc drivers, I have been trying different drivers for access and they were doing things i did not understand. In the end will us gambas component.
Easysoft looks to work, but has issues on the disconnect, mdbTools driver only good for extraction, 'where' does not really work well and returns numbers as text strings, devsoft version just crashes all over.

Count seems to be wrong, -1 for easy soft and 0 for the mdbTools driver, But I think that is being worked on.
Maybe someone can recommend a better access backend driver.

It was the latest odbc thread that got me thinking about doing an update to the software in gambas. I looked at it a few years ago but it got sidelined to other things.

Looks like in the end inter-opt with my existing databases clients may not be possible in an easy way. Probably just write something to convert everything. big job!!!

If your interested here is the more complete test program I am using:

' Gambas module file

'typedef void *                  SQLHANDLE;
'typedef SQLHANDLE               SQLHENV;

Public Const SQL_ATTR_ODBC_VERSION As Integer = 200
Public SQL_OV_ODBC2 As Long = 2 ' supposed to be unsigned
Public SQL_OV_ODBC3 As Long = 3 ' supposed to be unsigned

Public Const SQL_C_BINARY As Integer = -2
Public Const SQL_C_BIT As Integer = -7
Public Const SQL_C_TINYINT As Integer = -6
Public Const SQL_C_CHAR As Integer = 1                               '/ * CHAR, VARCHAR, DECIMAL, NUMERIC * /
Public Const SQL_C_LONG As Integer = 4                               '/ * INTEGER * /
Public Const SQL_C_SHORT As Integer = 5                              '/ * SMALLINT * /
Public Const SQL_C_FLOAT As Integer = 7                              '/ * REAL * /
Public Const SQL_C_DOUBLE As Integer = 8                             '/ * FLOAT, DOUBLE * /
Public Const SQL_C_NUMERIC As Integer = 2
Public Const SQL_C_DEFAULT As Integer = 99
' #define SQL_UNKNOWN_TYPE 0
' #define SQL_CHAR 1
' #define SQL_NUMERIC 2
' #define SQL_DECIMAL 3
' #define SQL_INTEGER 4
' #define SQL_SMALLINT 5
' #define SQL_FLOAT 6
' #define SQL_REAL 7
' #define SQL_DOUBLE 8
' #define SQL_DATETIME 9
' #define SQL_VARCHAR 12

Public Const SQL_SUCCESS As Integer = 0
Public Const SQL_SUCCESS_WITH_INFO As Integer = 1
Public Const SQL_ERROR As Integer = -1
Public Const SQL_INVALID_HANDLE As Integer = -2
Public Const SQL_STILL_EXECUTING As Integer = 2
Public Error_table As Collection = [Str(SQL_SUCCESS): "Success", Str(SQL_SUCCESS_WITH_INFO): "Success with info", Str(SQL_ERROR): "Error", Str(SQL_STILL_EXECUTING): "Still Executing", 
                                    Str(SQL_INVALID_HANDLE): "Invalid Handle"]

Public Const SQL_LOGIN_TIMEOUT As Integer = 103

Public Const SQL_NTS As Short = -3
Public Const SQL_NO_DATA As Integer = 100

Public Const SQL_HANDLE_ENV As Integer = 1
Public Const SQL_HANDLE_DBC As Integer = 2
Public Const SQL_HANDLE_STMT As Integer = 3
Public Const SQL_HANDLE_DESC As Integer = 4

Public Const SQL_NULL_HANDLE As Long = 0
Public SQLHANDLE As Pointer = 0
Public V_OD_Env As Pointer = 0
Public V_OD_erg As Integer = 0 
Public V_OD_err As Integer = 0
Public V_OD_hdbc As Pointer = 0
Public V_OD_hstmt As Pointer = 0
Public V_OD_htables As Pointer = 0
Public V_OD_STAT As Pointer = Alloc(10)
Public V_OD_msg As Pointer = Alloc(100)
Public V_OD_mlen As Integer = 0
Public V_OD_colanz As Integer = 0
Public V_OD_rowanz As Integer = 0

Public ClientID As Pointer = Alloc(16)
Public FirstName As Pointer = Alloc(200)
Public LastName As Pointer = Alloc(200)

Extern SQLAllocHandle(HandleType As Integer, SQL_INPUT_HANDLE As Long, SQL_OUPUT_HANDLE As Pointer) As Long In "libodbc"
Extern SQLSetEnvAttr(EnvironmentHandle As Pointer, Attribute As Integer, ValuePtr As Pointer, StringLength As Integer) As Long In "libodbc"
Extern SQLSetConnectAttr(ConnectionHandle As Pointer, Attribute As Integer, ValuePtr As Pointer, StringLength As Integer) As Long In "libodbc"
Extern SQLConnect(ConnectionHandle As Pointer, ServerName As String, NameLength1 As Short, UserName As String, NameLength2 As Short, Authentication As String, NameLength3 As Short) As Long In "libodbc"
Extern SQLDisconnect(ConnectionHandle As Pointer) As Long In "libodbc"
Extern SQLFreeHandle(HandleType As Integer, InputHandle As Pointer) As Long In "libodbc"
Extern SQLExecDirect(Handle As Pointer, Statement As String, TextLength As Short) As Long In "libodbc"
Extern SQLBindCol(StatementHandle As Pointer, column As Short, TargetType As Short, TargetValPtr As Pointer, bufferLen As Integer, Strlen_or_indPtr As Pointer) As Long In "libodbc"
Extern SQLGetDiagRec(HandleType As Integer, HandlePtr As Pointer, RecordNumber As Short, SQLSTATE As Pointer, NativeErrorPtr As Pointer, MessageText As Pointer, BufferLen As Short, TextLengthPtr As Pointer) As Long In "libodbc"
Extern SQLNumResultCols(StatementHandle As Pointer, NumCols As Pointer) As Long In "libodbc"
Extern SQLRowCount(StatementHandle As Pointer, NumRow As Pointer) As Long In "libodbc"
Extern SQLFetch(StatementHandle As Pointer) As Long In "libodbc"
Extern SQLTables(StaementHandle As Pointer, CatalogName As String, NameLength1 As Short, SchemaName As String, NameLength2 As Short, TableName As String, NameLength3 As Short, TableType As String, NameLength4 As Short) As Long In "libodbc"


Public Sub Main()
 Dim Statement As String = "Select RefererenceNumber,LastName,FirstName from ClientList"
 'ODBC_OPEN("CLIENT_INFORMATION_MANAGER") 'Use mdbTools driver
  ODBC_OPEN("ACCESS_SAMPLE")              'Use Easy Soft driver
 
   V_OD_erg = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, VarPtr(V_OD_hstmt))
   Print Error_Table[V_OD_erg]
   V_OD_erg = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, VarPtr(V_OD_htables))
   Print Error_Table[V_OD_erg]
   
 V_OD_erg = SQLBindCol(V_OD_hstmt, 1, SQL_C_LONG, ClientID, 16, VarPtr(V_OD_err))
 V_OD_erg = SQLBindCol(V_OD_hstmt, 2, SQL_C_CHAR, LastName, 200, VarPtr(V_OD_err))
 V_OD_erg = SQLBindCol(V_OD_hstmt, 3, SQL_C_CHAR, FirstName, 200, VarPtr(V_OD_err))
   Print Error_Table[V_OD_erg]
   
 V_OD_erg = SQLExecDirect(V_OD_hstmt, Statement, Statement.len)
  If V_OD_erg <> SQL_SUCCESS And If V_OD_erg <> SQL_SUCCESS_WITH_INFO Then
            Dim recno As Integer = 1
            Print "Select Failed = "; Error_table[V_OD_erg];;
            SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc, 1, V_OD_stat, VarPtr(V_OD_err), V_OD_msg, 100, VarPtr(V_OD_mlen))
            Print String@(V_OD_msg)
            odbc_Close()
            Quit 1
   Else 
            Print Error_Table[V_OD_erg]
   Endif 
   
   V_OD_erg = SQLNumResultCols(V_OD_hstmt, VarPtr(V_OD_colanz))
   Print Error_Table[V_OD_erg];; "Cols="; V_OD_colanz
   V_OD_erg = SQLRowCount(V_OD_hstmt, VarPtr(V_OD_rowanz))
    Print Error_Table[V_OD_erg];; "Rows="; V_OD_rowanz
    
    While SQLFetch(V_OD_hstmt) = SQL_SUCCESS
      'Print string@(ClientId);; String@(LastName); ","; String@(FirstName)   ' mdbTools driver
      Print Long@(ClientId);; String@(LastName); ","; String@(FirstName)      ' easy soft driver
    Wend
    
    Print Error_Table[V_OD_erg]
   V_OD_erg = SQLTables(V_OD_htables, 0, 0, 0, 0, 0, 0, 0, 0)
     V_OD_erg = SQLNumResultCols(V_OD_htables, VarPtr(V_OD_colanz))
   Print Error_Table[V_OD_erg];; "Cols="; V_OD_colanz
   V_OD_erg = SQLRowCount(V_OD_htables, VarPtr(V_OD_rowanz))
    Print Error_Table[V_OD_erg];; "Rows="; V_OD_rowanz
    
 odbc_Close()
    
 Print "All Done"

End

Public Sub ODBC_OPEN(DataBase As String)
    
   V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, VarPtr(V_OD_Env))
   Print Error_Table[V_OD_erg]
   V_OD_erg = SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)
   Print Error_Table[V_OD_erg]
   V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, VarPtr(V_OD_hdbc))
   Print Error_Table[V_OD_erg]
   V_OD_erg = SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, 5, 0)
   Print Error_Table[V_OD_erg]
   V_OD_erg = SQLConnect(V_OD_hdbc, DataBase, Database.len, "", 0, "", 0)
   If V_OD_erg <> SQL_SUCCESS And If V_OD_erg <> SQL_SUCCESS_WITH_INFO Then
            Dim recno As Integer = 1
            While SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc, recno, V_OD_stat, VarPtr(V_OD_err), V_OD_msg, 100, VarPtr(V_OD_mlen)) <> SQL_NO_DATA
                Print Error_Table[V_OD_erg];; String@(V_OD_msg)
                Inc recno
            Wend 
   Else 
            Print Error_Table[V_OD_erg]
   Endif 
    
End

Public Sub odbc_Close()
    V_OD_erg = SQLFreeHandle(SQL_HANDLE_STMT, V_OD_HTABLES)
    Print Error_Table[V_OD_erg]
    V_OD_erg = SQLFreeHandle(SQL_HANDLE_STMT, V_OD_HSTMT)
    Print Error_Table[V_OD_erg]
    V_OD_erg = SQLDisconnect(V_OD_HDBC)
    Print Error_Table[V_OD_erg]
    V_OD_erg = SQLFreeHandle(SQL_HANDLE_DBC, V_OD_HDBC)
    Print Error_Table[V_OD_erg]
    V_OD_erg = SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env)
    Print Error_Table[V_OD_erg]
    
End


"Failure is the key to success; 
 each mistake teaches us something"  .. Morihei Ueshiba
Brian G

----- On Apr 7, 2021, at 2:24 AM, KKing kicking177 at gmail.com wrote:

> Hi Brian,
> Just curious, what is the basis of your syntax?
> I'm not saying one way better than another but I would have expected
> something more along lines of
> <<
>   Dim $con As New Connection
>   Dim intCount As Integer
>   Dim rs As Result
>   Dim $ExecCmd As String
> 
>   Try $con.Close()
>   $con.Type = "odbc"
>   $con.Host = "CLIENT_INFORMATION_MANAGER"
>   $con.Open()
> 
>   $ExecCmd = "Select FirstName from ClientList "
> 
>   rs = $con.Exec($ExecCmd)
> 
>   intCount = rs.Count
> 
>   While (rs.Available)
>     Print "V_OD_erg:" & rs!FirstName
>     rs.MoveNext
>   Wend
> 
> <<
> 
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----