04130324.txt 13-Apr-00


SQLSelect:Execute(), NT and MSAccess 2000

From willchapman@dial.pipex.com Fri Mar 31 00:51:56 2000
Subject: SQLSelect:Execute(), NT and MSAccess 2000

I have a call to SQLSelect:execute() in an app that runs
perfectly on my system which is Win98 and has Access 97
installed.

On a customers system - running NT and MS Access 2000 - the
SQLSelect:Execute() refuses to work. Can anyone give me any
clues as to why this might be? I've seen posts on other
forums that suggest that Access is not fully backward
compatible.

Thanks in advance..

Will Chapman


From alison@bradburyphillips.co.uk Fri Mar 31 03:41:09 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

There is a bug in SQLSelect to do with parameters - is that
relevant? I have a workaround for it, if you need it

I'm running under NT with Access 97, and the system is
working on Win98 machines with Access 97. - Also I tried to
look at my Access database using an early version of Access
and couldn't get in - clearly there are some issues with
backward compatibility.

Alison


From willchapman@dial.pipex.com Fri Mar 31 21:15:18 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

Alison

It might be a factor so if you could let me see the work-
around I'll take a look.

By the way if you are accessing MDB files I have found
VO2Jet to be much faster than the in-built VO-SQL
methods/classes (I'm using it almost entirely in this
current app but need VO's SQLSelect to grab hold of a Text
CSV file via ODBC).

Thanks..

Will


From alison@bradburyphillips.co.uk Sat Apr 01 00:52:23 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

Adding this code into my app has allowed me to use
parameters with SQLSelect. Credit goes to John Parker.


METHOD NewSelectedCount( ) CLASS SQLSelect
  LOCAL cStatement    AS STRING
  LOCAL cCount        AS STRING
  LOCAL nPos          AS INT
  LOCAL l             AS LONG
  LOCAL nRet          AS LONG
  LOCAL nRetCode      AS LONG
  LOCAL oCount        AS SQLStatement
  LOCAL oConn         AS SQLConnection
  LOCAL pLong         AS PTR
  LOCAL pData         AS PTR
  LOCAL aStatArg  AS ARRAY
  LOCAL nCount  AS INT
  LOCAL pNullInd  AS PTR

  IF ! lCsrOpenFlag
    IF ! SELF:Execute()
      RETURN 0
    ENDIF
  ENDIF

  IF (oStmt:RecCount > -1)
    RETURN oStmt:RecCount
  ENDIF

  pNullInd := NULL_PTR
  nRet := 0L
  cStatement := SELF:oStmt:SQLString

  nPos := AtC("ORDER BY", cStatement)
  IF nPos > 0
    cStatement := Left(cStatement,nPos-1)
  ENDIF

  nPos :=  AtC("FROM", cStatement)

  IF nPos > 0
    cCount := "SELECT COUNT (*) " + SubStr(cStatement, nPos)
  ELSE
    cCount := "SELECT COUNT (*) " + SELF:cTableName
  ENDIF

  IF oStmt:Connection:nActiveStmts = 1
    oConn := SQLConnection{ oStmt:Connection:DataSource, ;
    oStmt:Connection:UserID,     ;
    oStmt:Connection:Password }
    IF !oConn:Connected
      SELF:__CopySQLError( #__RecCount, oConn:ErrInfo )
      RETURN nRet
    ENDIF

    oCount := SQLStatement{ cCount, oConn }
  ELSE
    oCount := SQLStatement{ cCount, oStmt:Connection }
  ENDIF
  nCount := ALen( oStmt:__Params )
  aStatArg := oStmt:__Params
  IF nCount > 0

    pNullInd := MemAlloc( nCount* _sizeof(LONGINT) * 2 )

    IF pNullInd = NULL_PTR
     RETURN nRet
    ENDIF

    IF ! oCount:__SetParameters( AStatArg, pNullInd )
     MemFree( pNullInd )
     RETURN nRet
    ENDIF
  ENDIF

  oCount:SQLString := SELF:PreExecute( oCount:SQLString )

  pData := MemAlloc( _SIZEOF(LONG) )

  IF pData = NULL_PTR
    IF pNullInd != NULL_PTR
      MemFree( pNullInd )
    ENDIF
    RETURN nRet
  ENDIF

  pLong := MemAlloc( _SIZEOF(LONG) )

  IF pLong == NULL_PTR
    IF pNullInd != NULL_PTR
      MemFree( pNullInd )
    ENDIF
    MemFree(pData)
    RETURN nRet
  ELSE
    LONG(pLong) := 0L
  ENDIF

  nRetCode := SQLBindCol( oCount:StatementHandle, 1,  ;
    SQL_C_SLONG ,               ;
    pData,                      ;
    4,                          ;
    pLong )

  IF nRetCode == SQL_SUCCESS
    oCount:ScrollConcurrency := SQL_CONCUR_READ_ONLY
    l := SLen( cCount )
    nRetCode := SQLExecDirect( oCount:StatementHandle, ;
      PTR( _CAST, cCount ), l )

    IF nRetCode = SQL_SUCCESS .or. ;
      nRetCode = SQL_SUCCESS_WITH_INFO
      nRetCode := SQLFetch( oCount:StatementHandle )
      IF nRetCode == SQL_SUCCESS
          nRet := LONG(pData)
          SELF:nlastRecNum   := nRet
          SELF:lLastRecFound := .T.
      ENDIF
    ENDIF
  ENDIF

  MemFree(pData)
  MemFree(pLong)

  IF pNullInd != NULL_PTR
    MemFree( pNullInd )
  ENDIF

  oCount:FreeStmt( SQL_DROP )

  IF oStmt:Connection:nActiveStmts = 1
    oConn:__Free()
  ENDIF

RETURN nRet


From willchapman@dial.pipex.com Sat Apr 01 02:49:34 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

Thanks Alison (and John too!)

Cheers..

Will


From ferber@richsoftware.com Fri Mar 31 04:47:44 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

Will,

I just had a similiar problem with Access2000 and Windows
2000 and I created a SQL test application and there the name
of the table was in delimited with the [`] character. Took
it into my code and works now fine.

Michael


From willchapman@dial.pipex.com Fri Mar 31 21:18:51 2000
Subject: Re: SQLSelect:Execute(), NT and MSAccess 2000

That looks promising - I'll investigate...

Thanks...

Will