server routine can not execute sql

lynnliu · Post by **lynnliu** » Wed Jun 16, 2004 8:41 pm

I have two set of system each one with Informix db and
ds 6 on HP UNIX. I can run job extracting/loading data from/to Informix
database using ODBC/informix_cli stage on both machine, but when I test
a sql-routine (the sql is very simple just like "select...."), I found
all is ok until SQLExecDirect, it can connect db but can't execute any
sql (the status return -1) on machine A .On machine B, with same
ds&informix set, the routine run good return the data what I want to get
from Informix db. And I do some test, the results as follows:

1, DS (A machine)--Informix (A machine): the job with cli/odbc stage is
ok, but sql-routine can't execute sql, only can connect db.

2, DS (B)--Informix (B): the job with cli/odbc stage is ok, sql-routine
is ok.

3, DS (A)--Informix (B): job with cli/odbc stage is ok, but sql-routine
can't execute sql, only can connect db.

4, DS (B)--Informix (A): job with cli/odbc stage is ok, but sql-routine
can't execute sql, only can connect db.

I check the both Informix client's sqlhost and both ds
dsenv,.odbc.ini,uvodbc.config, not find anything wrong. Is there
something I should check?

Thanks!

ray.wurlod · Post by **ray.wurlod** » Wed Jun 16, 2004 9:01 pm

Looks like you are writing code using BCI functions.

Any time a BCI function returns anything other than SQL.SUCCESS (0), you need to remove any information from the handle in question using SQLError().

So, after your SQLExecDirect() call, what values are returned by the SQLError() function? It may be that the return value of SQLExecDirect() was SQL.SUCCESS.WITH.INFO, but you still have to remove any information from the statement handle, otherwise anything else you try to do with the handle is doomed to failure.

lynnliu · Post by **lynnliu** » Thu Jun 17, 2004 4:46 am

Ray, thanks for your reply

As what you said, I got the output error of SQLError () function --"The DataDirect ODBC driver you are attempting to access has been provided to you by AscentialSoftware for exclusive use with DataStage. You are not licensed to use this driver with any application other than DataStage. If you would like to purchase a driver for some other use, please call 800-876-3101 or visit DataDirect at www.datadirect-technologies.com". I search on this forum for the topic, and get much information about that. Thanks a lot!

Best Regards
Lynn

roy · Post by **roy** » Thu Jun 17, 2004 2:54 pm

Hi,
in other words in the server that fails no installation of the informix client was made and you used the ODBC driver that comes with DS, so install it and your problem is solved

Good Luck,

ray.wurlod · Post by **ray.wurlod** » Thu Jun 17, 2004 4:43 pm

The ODBC drivers that ship with DataStage can be used for 30 days then "they" want you to pay for the privilege; "they" in this case being the vendors of the drivers, Data-Direct Technologies.

It would be better - in my opinion - were they up-front about this, rather than to suck users in with the unannounced 30-day "free trial" period with no warnings emerging from SQLConnect.

There is no workaround if you want to continue to use BCI functions; you will have to purchase the right to use the ODBC driver. (This is done by connecting to the web site indicated in the error message or, if you prefer, through your software provider, but not through Ascential.)

You could, of course, re-write your application to use a DataStage job to perform the SQL through an ODBC stage (for which the driver IS licensed) and retrieve the results either from the job log or a target file of the job itself.