Re: Using SQL CONNECT problem to MS SQL database

<Dave Clark>

Can't PREPARE host variables in dynamic SQL, either. At least,
I've gotten errors when I tried to do it before.

Sincerely,

Dave Clark

</Dave Clark>

Dave,
you are right, a prepare String can't include hostvars (:soemVar).

... the answer out of my head was not precise. In the meantime I've had the time to reproduce the situation (it's easier, if a complete minimalized programm and the java log is provided).
the OP had the problem to do a select count(*) into someInt from ... against an MS SQL database:
1.) the simpelst way is working, something like this:
D*B CRTSQLRPGI TSTDARRYL
D*B+ OBJTYPE(*MODULE)
D*B+ DBGVIEW(*SOURCE)
D*B+ COMMIT(*CS)
D*B+ RDB(*LOCAL)
D*B+ OPTION(*SQL)
D*B CRTPGM TSTDARRYL
D*B+ ACTGRP(TSTDARRYL)
d myuser s 10 inz('DBMSSQL')
d mypwd s 10 inz('denkste')
d someInt s 10i 0 inz
exec sql
connect to dbmssql
user :myuser
using :mypwd;
exec sql
SELECT COUNT(*)
into :someInt
from covelenz;
if someInt > 0;
dsply 'success';
else;
dsply sqlcode;
endif;
exec sql
release DBMSSQL;
exec sql commit;
return;
(this was not included in early versions of ArdGate)
2.) trying to use dynamic sql (like OP did) will fail on MS SQL, because MS SQL does not follow the SQL standard (select into works like create table as (select ...) with data on as/400)
3. ) trying to fix this with replacing select into by values into, will fail, because MS SQL doesn't support this
4.) using set is not recognized by ArdGate (mainly because ArdGate is supporting only input parameters for prepare execute.
5.) return parameters should work for calling stored procedures (with the current release)
6.) using a cursor and fetch would work for all releases of ArdGate

finally) It seems to be possibel to support in out parameters for prepare execute and I will take it to the todo list (priority isn't really high, because there are alternatives available.

Dieter