Clarion and Microsoft SQL native drivers

code Snippets
,
1

Here is an example of specifying the native client version in the connection string:

! MSS: server,db,user,pwd
  SELF.OwnerString = |
    Clip(SELF.server) & ';' & |
    'Database=' & Clip(SELF.databaseName) & ';' & |
    Choose(Clip(SELF.userName) <> '', 'Uid=' & Clip(SELF.userName) & ';', '') & |
    Choose(Clip(SELF.password) <> '', 'PWD=' & Clip(SELF.password) & ';', '') & |
    'Trusted_Connection=Yes;' & |
    'Driver={{SQL Server Native Client 11.0};'

…and below is a copy of some code from Dennis in the CW-Talk2 chat when we were discussing the native drivers a while back.

In order to use MARS you must use one of the native client drivers, plain ODBC will not allow mars. Below is a simple program that demos the issue, change the server name and datbase name in the connection string to see different results.

program

    include('odbctypes.inc'),once
  map

    MODULE('odbc32')
      SqlAllocEnv(*long he),long,pascal
      SQLAllocHandle(SQLSMALLINT HandleType, SQLHANDLE InputHandle, *SQLHANDLE OutputHandlePtr),SqlReturn,pascal
      SqlSetEnvAttr(long henv, long attr, long valuePtr, long lengthp),long,pascal
      SqlGetEnvAttr(long henv, long attr, *long valuePtr, long lengthp, *long bufferlength),long,pascal
      SQLGetDiagRec(short HandleType, long Handle, short RecNumber, *cstring SQLState, *long NativeErrorPtr, *cstring MessageText, long BufferLength, *long TextLengthPtr),long,pascal,raw,proc
      SQLSetConnectAttr(SQLHDBC hConnect, SQLINTEGER Attribute, SQLPOINTER valuePtr, SQLINTEGER StringLength),sqlReturn,pascal,proc
      SQLDriverConnect(long ConnectionHandle, long WindowHandle, *cstring InConnectionString, short StringLength1, *cstring OutConnectionString, short  BufferLength, *short StringLength2Ptr, Ushort DriverCompletion),sqlReturn,pascal,raw
      SQLExecDirect(long StatementHandle, *cstring StatementText, long TextLength),sqlReturn,pascal,raw
    end
  end


res  short

hdbc  long

sqlState         cstring(6)
MessageText      cstring(2000)
NativeErrorPtr   long
textLengthPtr long(2000)

attr long
attrValue long
outattrValue long

lengthptr long

hEnv   long

hStmt1 long
hStmt2 long
connStr cstring(1024)
outconnStr cstring(1024)

op   short(1024)

sqlcode1  cstring(2000)
sqlcode2  cstring(2000)

  code

  
  res = SQLAllocHandle(1, 0, hEnv)
  if (res <> 0)
    stop('hEnv not allocated ' & res & ' ' & hEnv)
  end

   attr = 200
   attrvalue = 3 !SQL_OV_ODBC3

   res = SqlSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, attrvalue, 0)
   if (res <> 0)
     stop('after set ' & res)
   end

   res = SqlGetEnvAttr(henv, attr, outattrValue, 0, lengthptr)
   if (res <> 0)
     SQLGetDiagRec(1, henv, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
     stop('message ' & res & ' ' & messagetext & ' ' & sqlState)
   end


   res = SQLAllocHandle(2, hEnv, hDbc)
   if (res <> 0)
     SQLGetDiagRec(1, henv, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
     stop('message ' & res & ' ' & messagetext & ' ' & sqlState)
   end

   ! turn on mars
   res = SQLSetConnectAttr(hDbc, 1224, outattrValue, 0)
   ! no error when using version 3.0 or better because the
   ! connection is not actually allocated until the driver connect is called.
   if (res <> 0)
     SQLGetDiagRec(2, hDbc, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
     stop('message ' & res & ' ' & messagetext & ' ' & sqlState)
   end

   ! this connection string will fail, using ODBC driver
   ! but there is no error on the connection string or the actual connection
   ! the error happens when the second statment handle is used
   !connStr = 'Driver={{SQL Server};Server=your server name;Database=ypur db name;trusted_connection=yes;MARS_Connection=yes;'

   ! this connection string works as expected but is using the native client for 2008.
   connStr = 'Driver={{SQL Server Native Client 10.0};Server=your server name;Database=your db name;trusted_connection=yes;MARS_Connection=yes;'

   res = SQLDriverConnect(hDbc, 0, ConnStr, SQL_NTS, outConnStr, size(connstr), op, 0)
   if (res <> 0)
     stop('after driver connect a res of 1 is expected here ' & res & '  ' & outconnstr)
     if (res <> 1)
       SQLGetDiagRec(2, hDbc, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
       stop('message driver connect ' & res & ' ' & messagetext & ' ' & sqlState)
     end
   end


   res = SQLAllocHandle(3,  hDbc, hStmt1)
   stop('Statement 1  ' & hStmt1 & ' ' & hDbc)

   res = SQLAllocHandle(3,  hDbc, hStmt2)
   stop('Statement 2  ' & hStmt2 & ' ' & hDbc)


   sqlcode1 = 'select fldOne, fldTwo from dbo.tableone'

   res = SQLExecDirect(hStmt1, sqlCode1, SQL_NTS)
   if (res <> 0)
     res = SQLGetDiagRec(3, hStmt1, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
     stop('exec two message ' & res & ' ' & messagetext & ' ' & sqlState)
   end

   sqlcode2 = 'select fldOne, fldTwo from dbo.tabletwo tt where tt.tableOneid = 1'

   res = SQLExecDirect(hStmt2, sqlCode2, SQL_NTS)
   if (res <> 0)
     res = SQLGetDiagRec(3, hStmt2, 1, sqlState, NativeErrorPtr, MessageText, size(MessageText), textLengthPtr)
     stop('exec two message ' & res & ' ' & messagetext & ' ' & sqlState)
   end

   stop('end of test')