• Welcome to PlanetSquires Forums.
 

Typo in COdbcDbc.inc ? Establishing a connection to a driver and a data source

Started by Eigil Dingsor, November 19, 2018, 10:38:11 AM

Previous topic - Next topic

Eigil Dingsor

Hi,
I'm trying to use Jose's ODBC framework files. Firstly I'd say : Many thanks for your great willingness to share your knowledge!
When trying to connect to a server (using either wrong dns name, or UID or password) I would expect to  get a connection handle = NULL(i.e error of some sort). Whatever wrong syntax I used I got a connection handle other than NULL(i.e no errors). After some digging and testing which gave me the expected results, I think there might be a typo in COdbcDbc.inc file. According to MS when trying to allocate the environment handle one should use.

SQLAllocHandle( 
      SQLSMALLINT   HandleType, 
      SQLHANDLE     InputHandle, 
      SQLHANDLE *   OutputHandlePtr)

inc file says SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, @AFX_ODBC_hEnv), but AFX_ODBC_hEnv is already defined  as a pointer in sqltypes.bi


I think the code should be corrected to:

' ========================================================================================
' Establishes connections to a driver and a data source.
' ========================================================================================
CONSTRUCTOR COdbc (BYREF wszServerName AS WSTRING, BYREF wszUserName AS WSTRING, BYREF wszAuthentication AS WSTRING, BYVAL nODbcVersion AS SQLINTEGER = SQL_OV_ODBC3, BYVAL ConnectionPoolingAttr AS SQLUINTEGER = 0)
   IF AFX_ODBC_hEnv = NULL THEN
      ' // Connection pooling
      IF ConnectionPoolingAttr = SQL_CP_ONE_PER_DRIVER OR ConnectionPoolingAttr = SQL_CP_ONE_PER_HENV THEN
         SQLSetEnvAttr(NULL, SQL_ATTR_CONNECTION_POOLING, cast(SQLPOINTER, cast(LONG_PTR, ConnectionPoolingAttr)), SQL_IS_UINTEGER)
      END IF
      ' // Allocates the environment handle
      'SetResult(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, @AFX_ODBC_hEnv))  'Old code . Typo??
      SetResult(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, AFX_ODBC_hEnv))  'AFX_ODBC_hEnv is a pointer(SQLHANDLE) already defined as type SQLHANDLE as any in sqltypes.bi
      ' // Sets the ODBC version
      IF AFX_ODBC_hEnv THEN
         SetResult(SQLSetEnvAttr(AFX_ODBC_hEnv, SQL_ATTR_ODBC_VERSION, cast(SQLPOINTER, cast(LONG_PTR, nODbcVersion)), SQL_IS_INTEGER))
      END IF
   END IF
   IF AFX_ODBC_hEnv THEN
      ' // Allocate a connection handle
      SetResult(SQLAllocHandle (SQL_HANDLE_DBC, AFX_ODBC_hEnv, @m_hDbc))
      IF m_Result <> SQL_ERROR AND m_Result <> SQL_INVALID_HANDLE THEN AFX_ODBC_numConnections += 1
   END IF
   IF m_hDbc THEN
      SetResult(SQLConnectW(m_hDbc, wszServerName, LEN(wszServerName), wszUserName, LEN(wszUserName), wszAuthentication, LEN(wszAuthentication)))
   END IF
END CONSTRUCTOR

José Roca

If you're using te example EX_CODBC_BasicSteps.bas, it is the first that I ever wrote with FreeBasic and I forgot to update it. I'm sorry. Below is an updated version.

pDbc is not a the connection handle, but a reference to the Conecction class. To check for the connection handle you must use pDbc.Handle, which will be null in case of failure.


'#CONSOLE ON
#include once "Afx/COdbc/COdbc.inc"
USING Afx

' // Create a connection object and connect with the database
DIM wszConStr AS WSTRING * 260 = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb"
DIM pDbc AS CODBC = wszConStr
IF pDbc.Handle = NULL THEN PRINT "Unable to create the connection handle" : SLEEP : END

' // Allocate an statement object
DIM pStmt AS COdbcStmt = pDbc
IF pStmt.Handle = NULL THEN PRINT "Unable to create the statement handle" : SLEEP : END

' // Generate a result set
pStmt.ExecDirect ("SELECT * FROM Authors ORDER BY Author")

' // Parse the result set
DIM cwsOutput AS CWSTR
DO
   ' // Fetch the record
   IF pStmt.Fetch = FALSE THEN EXIT DO
   ' // Get the values of the columns and display them
   cwsOutput = ""
   cwsOutput += pStmt.GetData(1) & " "
   cwsOutput += pStmt.GetData(2) & " "
   cwsOutput += pStmt.GetData(3)
   PRINT cwsOutput
LOOP

PRINT
PRINT "Press any key..."
SLEEP


José Roca

> inc file says SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, @AFX_ODBC_hEnv), but AFX_ODBC_hEnv is already defined  as a pointer in sqltypes.bi

> SetResult(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, AFX_ODBC_hEnv))  'AFX_ODBC_hEnv is a pointer(SQLHANDLE) already defined as type SQLHANDLE as any in sqltypes.bi

Unlike PowerBasic, in FreeBasic parameters are passed by value by default. Therefore, for out parameters we must pass the address of the variable, which is the same as passing the variable name if the parameter had been declared as BYREF or using BYVAL VARPTR(variable name) with PowerBasic.

Eigil Dingsor

Hi Jose! Thanks for the clarification. Yes, I used your early example code. I thought I did the correct thing when passing
SetResult(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, AFX_ODBC_hEnv))
You have defined AFX_ODBC_hEnv as SQLHANDLE. SQLHANDLE is defined as ANY PTR in sqltypes.bi. If AFX_ODBC_hEnv had been defined as Long/dword then I’d assume @AFX_ODBC_hEnv  would have been correct. I ‘ll test when back at work.
Eigil

José Roca

It is an handle. Handles are opaque pointers that you will never have to deferece since only the library that implements them know how to deal with them. Can't be defined as long/dword because then it won't work with 64 bit.

> If AFX_ODBC_hEnv had been defined as Long/dword then I’d assume @AFX_ODBC_hEnv  would have been correct.

With PowerBasic you can't define it as a pointer because it doesn't allow to pass a pointer variable by reference, but this limitation does not exist in FreeBasic.

If you want to write applications that work both with 32 and 64 bit, forget using dword/long for pointers, handles, etc. Better use the C++ aliases, such HWND for window handle, HBITMAP for bitmap handles, etc.

Eigil Dingsor

Hi again. Testing from home.
I've made a system DSN called TEST with no UIID or PWD that points to "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\northwind\biblio.mdb".
I want to connect to the DSN and not via connectionstring.

Using this code:

#include once "Afx/COdbc/COdbc.inc"
USING Afx

' // Create a connection object and connect with the database

DIM wszConStr AS wSTRING * 260 = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\northwind\biblio.mdb"
DIM pDbc AS CODBC PTR = new CODBC("TEST","","")
IF pDbc->handle = null tHEN 'end
    Print "Doen't work"
   END
   else
    Print "Works"
END IF

' // Allocate an statement object
DIM pStmt AS COdbcStmt PTR = NEW COdbcStmt(pDbc)
IF pStmt->handle = NULL THEN print "cannot create statement handle" :SLEEP:END
PRINT "Retrieved data blow" 
' // Generate a result set
pStmt->ExecDirect("select *  from Publishers") '("SELECT * FROM Authors ORDER BY Author")
  dim as long n,i
 

  n = pstmt->NumResultCols
 
  for i = 0 to n -1
     print pstmt->ColSchemaName(i)
  NEXT

' // Parse the result set
DIM cwsOutput AS CWSTR
DO
   ' // Fetch the record
   IF pStmt->Fetch = FALSE THEN EXIT DO
   ' // Get the values of the columns and display them
   cwsOutput = ""
   cwsOutput += pStmt->GetData(1) & " "
   cwsOutput += pStmt->GetData(2) & " "
   cwsOutput += pStmt->GetData(3)
   PRINT cwsOutput
LOOP

' // Delete the statement object
Delete pStmt

' // Delete the connection object
Delete pDbc

PRINT
PRINT "Press any key..."
SLEEP


Above code works.

But if I  alter TEST to NOTEST like this:


DIM wszConStr AS wSTRING * 260 = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\northwind\biblio.mdb"
DIM pDbc AS CODBC PTR = new CODBC("NOTEST","","")
IF pDbc->handle = null tHEN 'end
    Print "Doesn't work"
   END
   else
    Print "Works"
END IF
...
.....

then output console shows

Works
cannot create statement handle

pDbc->handle doesn't  return error. Have even tried pDbc->Envhandle option. No luck.
How can I check if connection is valid or not? Obviously there must be something I don't get.

José Roca