LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryanherb

Hi there

I am attempting to connect to a SQL Server database. I have setup an odbc connection called "ABC" and have set the following variables in the DB Query node:

dbuser: my username
dbpassword: my password
dbservice: ABC
dbinterface: odbc

I am getting the following error:
"ERROR opening i/o streams unknown exception caught in main()"

How do I fix this? I'm having no issues connecting to an Oracle database but am struggling to connect to a SQL Server database. Do I need to have the SQL Server client installed?

Hoping to hear from someone asap!

Cheers - Ryan

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryanherb

I have used the ODBC connection to draw data from the same table in MS Access - no problems encountered.

Mario and I have done some more digging. Here is the error log

*****
ERROR opening i/o streams:
unknown exception caught in main()

Installing log (04/20/2010 09:34:53) ...
2010-04-20 09:34:53; Level: 2; Type: 0; Desc: "Unable to translate ODBC type: -9"; File: "\src\brain_d\jigsaw\Code\Source\DB\ODBC\OdbcCodes .cpp"; Line: 62; Context: ""
2010-04-20 09:34:53; Level: 2; Type: 0; Desc: "unable to free statement handle"; File: "\src\brain_d\jigsaw\Code\Source\DB\ODBC\OdbcEnv.c pp"; Line: 352; Context: "OdbcEnv::FreeHandle(ctype)"
2010-04-20 09:34:53; Level: 2; Type: 0; Desc: "...exception seen"; File: "\src\brain_d\ra_dev\code\source\brain\DbNodeIo.cp p"; Line: 70; Context: "Salmon::NED::NodeIo::DbInput::DbInput()"
2010-04-20 09:34:53; Level: 2; Type: 0; Desc: "...exception seen"; File: "\src\brain_d\ra_dev\code\source\brain\QueryDumper \QueryDumperNodeControl.cpp"; Line: 125; Context: "Salmon::NED::QueryDumper::QueryDumperNodeControl: :createQueryInput()"
2010-04-20 09:34:53; Level: 2; Type: 0; Desc: "...exception seen"; File: "\src\brain_d\ra_dev\code\source\brain\QueryDumper \QueryDumperNodeControl.cpp"; Line: 99; Context: "Salmon::NED::QueryDumper::QueryDumperNodeControl: :initializeIo()"
Uninstalling log (04/20/2010 09:34:53) ...
*****

It appears the error occurs when it encounters the nvarchar data type in SQL Server 2005. For example:

[PERIODNAME] [nvarchar](256) NOT NULL,

If we execute:
select periodname from table
we get the error in the log above.

However, if we translate the nvarchar into a stock char:
select char(periodname) from table
Then the statement executes without error

Have you encountered the "unable to translate ODBC type: -9" error before?

Cheers - Ryan

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryanherb

SQL ODBC Connection settings.....

Microsoft SQL Native Client Version 09.00.2047

Data Source Name: ABC
Data Source Description: ABC
Server: wserver3400
Use Integrated Security: No
Database: XYZ
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryanherb

I've done some more digging - my issue is related to the NVARCHAR data type in the field that we are trying to read from....

In SQL Server, the key difference between VARCHAR and NVARCHAR is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 - 16 bits per character.

Would Lavastorm have difficulty processing UTF-16 data? Also, would this impact our ability to write from Lavastorm to a NVARCHAR field in the database?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rabbott

Hi Ryan,

Mario contacted the Lavastorm Desktop support address (DesktopSupport@mda-data.com) on Tuesday for help with this issue and I responded to him yesterday. To assure that you get the correct level of support, please use that address to report these types of problems (e.g. malfunctions, bugs) rather than the Lavastorm Community Forum.

This ODBC problem is a bug that is scheduled to be fixed in LAE release 5.0. You are correct that this only affects unicode and special datatypes in MS SQL Server.

You should be able to work around the problem by using JDBC nodes instead. JDBC drivers can be downloaded from Microsoft here:

http://www.microsoft.com/downloads/d...displaylang=en

Information on how to use the driver can be found here:

http://msdn.microsoft.com/en-us/library/ms378526.aspx

Hope this helps.

- Bob