[JayBee]

Hi,

Has anyone been able to access a SQLServer database from STX?
Either by ODBC or in another way?
If so, please give an example of how this is done.

Regards,

Jan

[cgittinger]

here is an excerpt from some real world code:

Code: Select all

...
session := SQL::ODBCSession new.
session connectWithUsername:user password:password dbname:database.
...
statement := session newStatementFor:('SHOW TABLES LIKE ''%1'';' bindWith:tableName).
cursor := statement execute.
[ cursor next notNil ] whileTrue:[
    row := cursor rowAsArray.
    ....
].
...
statement := session newStatementFor:('SELECT * FROM %1' bindWith:tableName).
cursor := statement execute.
[cursor next notNil] whileTrue:[
    obj := cursor rowAsObject.
   ...
].
...
session disconnect

best regards.

Note: ensure that the libodbc package is loaded (see the launchers system-modules menu):

Code: Select all

Smalltalk loadPackage:'stx:libdb/libodbc'

(normally, unless you changed the modules.stx-file, they are loaded by default)

[JayBee]

When I create an ODBC definition in windows and then evaluate the following code:

| session |
session := SQL::ODBCSession new.
session connectWithUsername:'blabla' password: 'blablaToo' dbname: 'TestDB'.
session disconnect

it ends with an exception:

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

When I change the default language of the ODBC connection into Dutch, the message changes to:

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to nederlands

This should not be handled as an exception, but as a normal message.

[mbeyl]

Hello,
here is a patch for the ODBCSession class.
File in the ".chg" file.
When connecting, "success with info" is now handled in the right way.

The problem is solved in newer versions starting with 5.4.5.

Regards,

Michael

[duetto]

hi all,

this is related to this thread.

my question is, where does the selecting the correct odbc driver happen (i.e. what class)?

i'm trying to connect to an odbc datasource with no success. i have successfully connected to the datasource with other tools.

john cummings

[JayBee]

Hi John,

I would expect the ODBCSession.
What error do you get when connecting to the ODBC datasource?

Best regards,

Jan

[duetto]

hi jan,

thanks for getting back to me.

first let me point out that the datasource is a sqlite database.

the bottomline is that it's failing in new. the environment handle is nil.

here is the code.

john

***********************

|session|

session := SQL::ODBCSession new. <====== failing here environmentHandle is nil
session initializeDatabaseHandle.
session connectWithUsername:'' password:'' dbname:'stxtestdb'. "odbc user dsn"
Transcript showCR:'Connected'.
session disconnect.
Transcript showCR:'Disconnected'.

[JayBee]

John,

I assume you are running STX under some Windows version (XP, Vista, Server ...) and have defined an ODBC datasource.
Can you communicate with the database e.g. from Excel?

Jan

[duetto]

hi jan,

yes, i am able to access table structure and data using 2 other tools that i have.

i still don't understand where STX learns about "DSN=...." or "DRIVER=...".

also, why does the odbc session have a nil handle returned from new?

i guess these are more rhetorical questions.

anyway, thanks for your help.

john

[JayBee]

Hi John,

Make sure you have loaded the SQL stuff by evaluating the following in a workspace:

Code: Select all

Smalltalk loadPackage:'stx:libdb/libodbc'

If you don't, STX will not know the class ODBCSession.

Then evaluate the following

Code: Select all

| session |
session := SQL::ODBCSession new.
self halt.

And you will get a popup showing the ODBC session (and not nill).
This should happen even without defining the ODBC datasource.

I am running Vista (32 bit) and this works fine.

Regards,

Jan