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
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
> 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.
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
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.
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.
Call the GetLastResult method.
Gotcha! Thanks