THe task below has two parts. The first runs a SQL query.
The second is a dataset loop with the same query to the same datasource.
The SQL query works OK but the dataset loop fails with "unspecified error" dialog box. Even by pressing the "Test SQL" button within the task editing screen it fails with this message.
Oddly, if I enter a bogus SQL statement such as enter a <<select * from faketable>> where faketable does not exist in the database, the ODBC driver correctly tells me that the table does not exist. And if I <<select * from largetable>> where largetable is a table with 100,000 rows it fails immediately, it does not seem to run the sql at all.
The ODBC driver is a SCO Vision ODBC driver for Informix. It does not need a user/password within Robotask, it takes it from the Windows data source definition.
Any ideas?
Millsey
;**********************
;* RoboTask Task file *
;* Do not edit! *
;**********************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|385352816
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task2"
Hide=INTEGER|0
ID=INTEGER|1251647078
Name=STRING|"SQL Query..."
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
[Actions\Action1]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER
[Actions\Action1\Params]
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=arista32"
override=STRING|"0"
password=STRING|"2005620365203652062032382"
sql=STRING|"""select * from fmsuser"""
[Actions\Action2]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action2\Params]
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=arista32"
override=STRING|"0"
password=STRING|"1903218830208762062506528"
sql=STRING|"""select * from fmsuser"""
var1=STRING|"FNAMES"
var2=STRING|"FVALUES"
withmemo=STRING|"0"
[Actions\Action3]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
Dataset loop does not work on Informix
Dataset loop does not work on Informix
Last edited by millsey on Fri Jul 22, 2005 4:31 am, edited 1 time in total.
Dataset loop does not work on Informix
Also, when trying to edit the SQL statement within the task edit box, the first time trying Test SQL gives >>unspecified error<< dialogue box, if I edit the SQL and try Test SQL again, I get >>ERROR Object was Open<<
if I close the edit box and reopen it the Test SQL works (or rather, it doesn't - I get >>unspecified error<< or >>Table does not exist<< which is correct).
if I close the edit box and reopen it the Test SQL works (or rather, it doesn't - I get >>unspecified error<< or >>Table does not exist<< which is correct).
Dataset loop does not work on Informix
SQL Query action have sense for queries such as INSERT, DELETE, UPDATE, EXECUTE PROCEDURE. I'm afraid that ODBC (or ADO) ignore SELECT statements in Execute SQL operations. Therefore you didn't got an error in first step
You are using ODBC data source. I think that you have some incorrect settings in your ODBC administrator for this data source.
You are using ODBC data source. I think that you have some incorrect settings in your ODBC administrator for this data source.
Dataset loop does not work on Informix
I can use the SQL query - in fact I use it quite extensively to process comma-delimited text files and insert or update this database for each line in the database. THis works OK.
But it seems that Informix or the driver for Informix is saying something back that Robotask is not expecting, hence the >>unspecified error<< I have checked every variation of the settings for ODBC in Windows and the connection string in Robotask but it simply will not work. Again, if I use an invalid table name the ODBC driver correctly reports back that the table does not exist, so the fundamental connection is OK.
This is a bit of a nightmare as I have promised that I can do certain database stuff which it appears I cannot. Has anyone else on this forum reported such problems and if so, did you get around them? And I have looked for a native OLE driver for this "old" version of Informix without any luck.
Millsey
But it seems that Informix or the driver for Informix is saying something back that Robotask is not expecting, hence the >>unspecified error<< I have checked every variation of the settings for ODBC in Windows and the connection string in Robotask but it simply will not work. Again, if I use an invalid table name the ODBC driver correctly reports back that the table does not exist, so the fundamental connection is OK.
This is a bit of a nightmare as I have promised that I can do certain database stuff which it appears I cannot. Has anyone else on this forum reported such problems and if so, did you get around them? And I have looked for a native OLE driver for this "old" version of Informix without any luck.
Millsey
Dataset loop does not work on Informix
RoboTask uses ADO technology for "SQL query" and "Database Loop" actions. We tested this on Access, Paradox, dBase, Interbase, Oracle. I thought that there are not problems. I shall test this on Informix too.
I think that troubles are in ODBC driver, because we had similar problems when we tested different ODBC drivers for Oracle.
I think that troubles are in ODBC driver, because we had similar problems when we tested different ODBC drivers for Oracle.
Dataset loop does not work on Informix
Oleg,
Don't spend too much time on this! We have 75 users running that ODBC driver with custom software so it may be the case that this is just not possible. We are using Informix SE version 5 which is very old- about 10-12 years old! I am trying to find a cheap supplier of and OLE driver which would probably solve the problem.
Thanks once again
Brian
Don't spend too much time on this! We have 75 users running that ODBC driver with custom software so it may be the case that this is just not possible. We are using Informix SE version 5 which is very old- about 10-12 years old! I am trying to find a cheap supplier of and OLE driver which would probably solve the problem.
Thanks once again
Brian