Using DacSession to access data via ODBCDBE

Xbase++ 2.0 Build 554 or later
Message
Author
pauld
Posts: 15
Joined: Thu Jul 31, 2014 2:48 pm

Using DacSession to access data via ODBCDBE

#1 Post by pauld »

I'm trying to access a dataset using dacsession and odbcdbe. The dataset uses .SAM flat files (which may be the issue; I'm not sure).

I can connect fine using the following code:

Code: Select all

   /*** Set connection string ***/

   cConnect := "DBE=ODBCDBE"
   cConnect += ";DRIVER=AdagioDataSourceDriver"
   cConnect += ";COLUMNS=COMMON"
   cConnect += ";DBDIRECTORY=C:\Softrak\ODBCV2\SAMDATA"
   cConnect += ";DBQ=C:\Softrak\ODBCV2\SAMDATA\SAM"
   cConnect += ";DBFILTER=ar9*"
   cConnect += ";DBSELELECTOR=SAM"
   cConnect += ";NAMES=SHORT"
   cConnect += ";USERID=SYS"
   cConnect += ";PASSWORD=SYS"
   cConnect += ";RAWPASSWORD=1"
   cConnect += ";TABLESALL=FALSE"

   /*** Create dacsession ***/

   oSession := DacSession():New(cConnect)

   /*** Test for connection success ***/

   IF ! oSession:IsConnected()
      DC_Winalert("Connection failed")

      wtf "ERROR code: " + AllTrim(Str(oSession:GetLastError())), ;
         "Description: " + AllTrim(oSession:GetLastMessage()) pause

      RETURN NIL
   ENDIF

   /*** Success message ***/

   DC_Winalert("Connection to server established")
However, when I try to access the data I run into issues.

Using DacSession's :ExecuteQuery() or :ExecuteStatement() like the following:

Code: Select all

   oSession:ExecuteQuery('SELECT Name FROM ar92acst') 
Gives me the error message: Operation not supported by this dbe (operation: dacExecuteQuery)

When I try accessing the data using the commands USE or SQL like the following:

Code: Select all

   USE 'ar92acst'

   SQL "SELECT Name FROM ar92acst"
I get the following error: File can not be opened (operation: DBUseArea)

I tried the same thing in Python, and it works:

Code: Select all

import pyodbc

# Create statement to connect to driver
driverConnect = 'DRIVER={AdagioDataSourceDriver}' + \
	';COLUMNS=COMMON' + \
	';DBDIRECTORY=C:\\Softrak\\ODBCV2\\SAMDATA' + \
	';DBQ=C:\\Softrak\\ODBCV2\\SAMDATA\\SAM' + \
	';DBFILTER=ar9*' + \
	';DBSELELECTOR=SAM' + \
	';NAMES=SHORT' + \
	';USERID=SYS' + \
	';PASSWORD=SYS' + \
	';RAWPASSWORD=1' + \
	';TABLESALL=FALSE'

# Connect to odbc using driver
#connection = pyodbc.connect(driverConnect)

# Get cursor
cursor = connection.cursor()

# Execute select statement
cursor.execute("SELECT Name FROM ar92acst")

row = cursor.fetchone()

while row:
	print(row[0])
	row = cursor.fetchone()
The Python code retrieves the data with no issue, so it doesn't look like an issue in the connection string. Does anyone know why this isn't working in Xbase?

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using DacSession to access data via ODBCDBE

#2 Post by rdonnay »

That ODBC driver possibly does not support SQL.

Is that a possibility?

Try using SqlQuery.exe to see if you get different results.

http://bb.donnay-software.com/sqlquery/sqlquery20.zip
http://bb.donnay-software.com/sqlquery/sqlquery19.zip

Set up your connection in SQLQuery.ini like this:

Code: Select all

[ODBC_1]
Name=Ar92
Driver=AdagioDataSourceDriver
ConnectString=COLUMNS=COMMON;DBDIRECTORY=C:\Softrak\ODBCV2\SAMDATA;DBQ=C:\Softrak\ODBCV2\SAMDATA\SAM;DBFILTER=ar9*;DBSELELECTOR=SAM;NAMES=SHORT;USERID=SYS;PASSWORD=SYS;RAWPASSWORD=1;TABLESALL=FALSE
Also Click on ODBC - List ODBC Drivers

This may tell you a bit more information.
odbcdrivers.jpg
odbcdrivers.jpg (107.28 KiB) Viewed 17356 times
The eXpress train is coming - and it has more cars.

pauld
Posts: 15
Joined: Thu Jul 31, 2014 2:48 pm

Re: Using DacSession to access data via ODBCDBE

#3 Post by pauld »

When I run sqlquery, I get a connection failed error (5381)

I've attached an image of what the driver info (there's not much there).
Attachments
odbc_driver_list.PNG
odbc_driver_list.PNG (45.07 KiB) Viewed 17343 times

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using DacSession to access data via ODBCDBE

#4 Post by rdonnay »

It appears that the ODBC driver provides very little information.

You should not be getting a 5381 error if you didn't get that error in your own code.

Possibly SqlQuery.exe is not building the connect string properly.

You can determine this by adding WTF cSQLConnect in SqlQuery.prg (Method SqlQueryConnection:ConnectODBC())

Add this just prior to the DacSession():new( cSQLConnect )
The eXpress train is coming - and it has more cars.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using DacSession to access data via ODBCDBE

#5 Post by rdonnay »

I recommend the following change to SqlQuery.prg.
I am adding statements in the connection string that probably are not acceptable to your ODBC driver.

This will only add those statements if they exist in SqlQuery.Ini.
Just rebuild SqlQuery.exe by running Pbuild SqlQuery.

Code: Select all

 METHOD SqlQueryConnection:ConnectODBC()

LOCAL cSQLConnect, cName

dbeSetDefault('ODBCDBE')

cSQLConnect := 'DBE=ODBCDBE; '
IF !Empty(::Driver)
  cSQLConnect += 'DRIVER=' + ::Driver + '; '
ENDIF
IF !Empty(::Server)
  cSQLConnect += 'SERVER=' + ::Server + '; '
ELSEIF !Empty(::ServerDict)
  cSQLConnect += 'SERVER=' + ::ServerDict + '; '
ENDIF
IF 'ADVANTAGE STREAMLINESQL ODBC' $ Upper(::Driver)
  cSQLConnect += 'DataDirectory=' + ::Database + '; '
ELSEIF !Empty(::Database)
  cSQLConnect += 'DATABASE=' + ::Database + '; '
ENDIF
IF !Empty(::UID)
  cSQLConnect += 'UID=' + ::UID + '; '
ENDIF
IF !Empty(::PWD)
  cSQLConnect += 'PWD=' + ::PWD + '; '
ENDIF
IF !Empty(::Network)
  cSQLConnect += 'NETWORK=' + ::Network + '; '
ENDIF
IF !Empty(::ConnectTimeout)
  cSQLConnect += 'CONNECTTIMEOUT=' + Alltrim(Str(::ConnectTimeOut)) + '; '
ENDIF
cSQLConnect += ::connectString

::SessionDict := DacSession():new( cSQLConnect )

cName := ::name

IF !::SessionDict:isConnected()
  DC_WinAlert('ODBCDBE Connection Failed to ' + cName + Chr(13) + ;
              'Error code: ' + Alltrim(Str(::SessionDict:getLastError())) + Chr(13) + ;
              ::sessionDict:getLastMessage())
   RETURN .f.
ENDIF

RETURN .t.
The eXpress train is coming - and it has more cars.

User avatar
hz_scotty
Posts: 107
Joined: Thu Jan 28, 2010 8:20 am
Location: Wr.Neustadt / Österreich

Re: Using DacSession to access data via ODBCDBE

#6 Post by hz_scotty »

Need some .CH files to Rebuild v1.9 :think:

adsdbe.ch
sqlcmd.ch
odbcdbe.ch

Please
best regards
Hans

pauld
Posts: 15
Joined: Thu Jul 31, 2014 2:48 pm

Re: Using DacSession to access data via ODBCDBE

#7 Post by pauld »

I'm getting a compile error.

Unresolved external symbol DC_XBPBROWSEFILTERED

pauld
Posts: 15
Joined: Thu Jul 31, 2014 2:48 pm

Re: Using DacSession to access data via ODBCDBE

#8 Post by pauld »

So it looks like there were two issues.

First, the dac methods :ExecuteQuery() and :ExecuteStatement() have not been implemented yet.

Second, the default concurrency and scrollable were not supported by the odbc. Changing the properties as follows worked:

oSession:SetProperty( ODBCSSN_CONCURRENCY, ODBC_CONCUR_READONLY ) // I only need to read the data
oSession:SetProperty( ODBCSSN_SCROLLABLE, ODBC_NONSCROLLABLE )

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Using DacSession to access data via ODBCDBE

#9 Post by rdonnay »

First, the dac methods :ExecuteQuery() and :ExecuteStatement() have not been implemented yet.
Are you saying that you have not implemented them in your code or they have not been implemented by Xbase++?

I don't use this methods.
I use the SqlStmtExec() function.
Changing the properties as follows worked:

oSession:SetProperty( ODBCSSN_CONCURRENCY, ODBC_CONCUR_READONLY ) // I only need to read the data
oSession:SetProperty( ODBCSSN_SCROLLABLE, ODBC_NONSCROLLABLE )
Why would you need these? I would think it would still work without them.
The eXpress train is coming - and it has more cars.

pauld
Posts: 15
Joined: Thu Jul 31, 2014 2:48 pm

Re: Using DacSession to access data via ODBCDBE

#10 Post by pauld »

rdonnay wrote:
First, the dac methods :ExecuteQuery() and :ExecuteStatement() have not been implemented yet.
Are you saying that you have not implemented them in your code or they have not been implemented by Xbase++?

I don't use this methods.
I use the SqlStmtExec() function.
:ExecuteQuery() and :ExecuteStatement() are not implemented in Xbase++.

I used SQL cStatement (from sqlcmd.ch).
rdonnay wrote:
Changing the properties as follows worked:

oSession:SetProperty( ODBCSSN_CONCURRENCY, ODBC_CONCUR_READONLY ) // I only need to read the data
oSession:SetProperty( ODBCSSN_SCROLLABLE, ODBC_NONSCROLLABLE )
Why would you need these? I would think it would still work without them.
It might be that the ODBC I'm using does not support the default values. I turned on the ODBC tracing to find where it was failing. This is a snippet of the log:

odbcdbe_test 4570-2e64 EXIT SQLSetStmtAttr with return code -1 (SQL_ERROR)
SQLHSTMT 0x00BA9590
SQLINTEGER 7 <SQL_ATTR_CONCURRENCY>
SQLPOINTER 3 <SQL_CONCUR_ROWVER>
SQLINTEGER -6

DIAG [HY024] [Softrak Systems][ODBC] (10280) Invalid attribute value. (10280)

Post Reply