ODBC Connector Stage options

vputta · Post by **vputta** » Thu Nov 14, 2013 11:18 am

Hi- We are using Datastage 8.1. I am using ODBC connector stage to read data. But am unable to see "Read Select statement from file" option in the stage. Is this option available only in later versions of Datastage but not in 8.1?

Thanks

chulett · Post by **chulett** » Thu Nov 14, 2013 12:11 pm

I can't check at the moment but it isn't always a discrete option. In some stages you specify "User Defined" and then put "{FILE} <path to sql file>" in where the SQL itself would normally be. Should be documented if that's how it works there.

vputta · Post by **vputta** » Thu Nov 14, 2013 1:56 pm

I have tried specifying the file path in the Select statement box of ODBC connector stage but it didn't work.
The Oracle connector has the specific option to pick the select query from a file. So, if am using ODBC connector is there any other way to pick the query from a file?

chulett · Post by **chulett** » Thu Nov 14, 2013 2:10 pm

Only whatever is documented - did you check? And since I can't tell from your reply, you have to actually include {FILE} at the front of the pathname rather than just the path - did you? Or it might be "{FILE}=", don't recall off the top of my head.

vputta · Post by **vputta** » Thu Nov 14, 2013 2:38 pm

I have mentioned this in the Select statement-

#SOURCE_QRY_PATH#/selectdynamic.sql

I am passing the source query path during runtime. As I have said this is working in Oracle connector but not in ODBC connector. Plz lemme know if this is correct?

chulett · Post by **chulett** » Thu Nov 14, 2013 5:56 pm

As noted (twice now) you must include the text {FILE} before the pathname... otherwise all you have is an invalid SQL statement.

chulett · Post by **chulett** » Thu Nov 14, 2013 6:00 pm

Examples here: viewtopic.php?t=124375

vputta · Post by **vputta** » Fri Nov 15, 2013 12:12 pm

Thanks for the info. I have tried the below approaches but it's not working.

{FILE}#SOURCE_QRY_PATH#
{FILE}/#SOURCE_QRY_PATH#

Is there any other way to get this resolved?

chulett · Post by **chulett** » Fri Nov 15, 2013 12:32 pm

Define 'not working'... what happens? What error message(s) do you see? What settings are you using in the stage?

vputta · Post by **vputta** » Fri Nov 15, 2013 12:55 pm

I a using ODBC connector stage to read data rom oracle 10g table and writing the data into dataset.

ODBC Connector stage ---> Dataset

Select stmt in ODBC- {FILE}/#SOURCE_QRY_PATH#

Using Run time column propagation.

run time value passing to the parameter SOURCE_QRY_PATH - /home/dsadm/select.sql

chulett · Post by **chulett** » Fri Nov 15, 2013 2:04 pm

Interesting... you managed to not answer any of the questions I asked.

vputta · Post by **vputta** » Fri Nov 15, 2013 3:00 pm

Sorry. This is the error message am getting.

ODBC_Connector_80: ODBC function "SQLPrepare" reported: SQLSTATE = HY000: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver]General error. setStmtType failed

chulett · Post by **chulett** » Fri Nov 15, 2013 3:14 pm

... and exactly what settings are you using in the stage?

vputta · Post by **vputta** » Fri Nov 15, 2013 4:08 pm

am using normal connection settings, transaction record cnt- 2000, session array size- 2000 and am not defining any before/afte SQL.

chulett · Post by **chulett** » Sat Nov 16, 2013 2:45 pm

I don't see anything in the documentation that indicates that the ODBC Connector supports reading SQL from a file. Unless someone here knows otherwise, time to involve your official support provider it would seem, see what they say your options are.