Re: Using SQL CONNECT problem to MS SQL database

I changed the compile options and still get the same error.

---------------
PGM

/* CRTSQLRPGI OBJ(QTEMP/LIBSRVC01) SRCFILE(LIBERTY/QSRC) +
COMMIT(*ALL) RDB(AXTRN01) +
OBJTYPE(*MODULE) USER(CCAXAX) +
PASSWORD('ccaxax') OPTION(*SQL) +
CLOSQLCSR(*ENDMOD) SQLCURRULE(*STD) +
DBGVIEW(*SOURCE) DYNUSRPRF(*OWNER)
*/

CRTSQLRPGI OBJ(QTEMP/LIBSRVC01) SRCFILE(LIBERTY/QSRC) +
COMMIT(*CS) RDB(*LOCAL) OBJTYPE(*MODULE) +
OPTION(*SQL *PERIOD *CVTDT) +
CLOSQLCSR(*ENDMOD) DATFMT(*ISO) +
TIMFMT(*ISO) TOSRCFILE(*CURLIB/QSQLTEMP1) +
COMPILEOPT('DBGVIEW(*SOURCE)')

MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSG('Module creation failed for LIBSRVC01')
GOTO CMDLBL(ENDPGM)
enddo

CRTSRVPGM SRVPGM(LIBERTY/LIBSRVC01) +
MODULE(QTEMP/LIBSRVC01) +
SRCFILE(LIBERTY/QSRVSRC) TEXT('Sub +
Procedures for Liberty project') +
ACTGRP(LIBERTY) ALWLIBUPD(*YES)
MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSG('Service program not created +
LIBERTY/LIBSRVC01')
GOTO CMDLBL(ENDPGM)
enddo

SNDPGMMSG MSG('Service program LIBERTY/LIBSRVC01 +
created.')
endpgm:
ENDPGM

---------

The RPG is relatively simple.
---------


Sql_User = 'CCAXAX';
SqlPwd = 'ccaxax';
SqlTableName = 'AXTRN01..UNITOFMEASURE';

Exec Sql CONNECT TO AXTRN01
USER :SQL_USER
Using :SqlPwd;
Exec Sql Set Connection AXTRN01;

zSqlCmd = 'Select count(*) into ? from AXTRN01..UNITOFMEASURE'
+ ' where Symbol = '
+ Q + %trim(pUOM) + Q;
Exec Sql Prepare SQLRMTCMD from :zSqlCmd;
Exec Sql Execute SQLRMTCMD using :Count ;

it fails here with the -551.

Exec Sql COMMIT;

If Count = 0;
Return *off;
Else;
pReturn_Code = '01';
Return *on;
Endif;

Return;
---------------


On Thu, Aug 25, 2016 at 2:43 PM, D*B <dieter.bender@xxxxxxxxxxxx> wrote:

... to clearify: we are talking about ArdGate
- just follow the examples in the distribution, there you will find:
* Create Command is important!!!
* additional features:
* Parameter Markers for prepared statements
* insert update delete without null indicators
D*B CRTSQLRPGI TESTARDTYP
D*B+ OBJTYPE(*MODULE)
D*B+ COMMIT(*CS)
D*B+ OPTION(*SQL *PERIOD *CVTDT)
D*B+ DBGVIEW(*NONE)
D*B+ DATFMT(*ISO)
D*B+ TIMFMT(*ISO)
D*B+ TOSRCFILE(*CURLIB/QSQLTEMP1)
D*B+ RDB(*LOCAL)
D*B+ COMPILEOPT('DBGVIEW(*SOURCE)')
D*B CRTPGM TESTARDTYP
D*B+ ACTGRP(TESTARDTYP)
* username case sensitive in MYSQL has to be upper case
d myuser s 10 inz('TEST400')
d mypwd s 10 inz('denkste')
d prepareString s 200
d rec e ds extname(testtyp)
d n1 s 4b 0
d n2 s 4b 0
d n3 s 4b 0
d n4 s 4b 0
d n5 s 4b 0
d n6 s 4b 0
d n7 s 4b 0
d n8 s 4b 0
c/exec sql
c+ connect to ardmysql
c+ user :myuser
c+ using :mypwd
c/end-exec
c/exec sql
c+ declare C1
c+ cursor for S1
c/end-exec
/free
prepareString =
'select INTNN, CHARNN, DECNN, NUMERICNN, VARCHARNN'
+ ' , DATENN, TIMENN'
// timestamp works in DB2, does not work with mysql (has no
microseconds)
// + ' , TIMESTAMPNN'
+ ' , INTN, CHARN, DECN, NUMERICN, VARCHARN'
+ ' , DATEN, TIMEN'
// + ' , TIMESTAMPN'
+ ' from testtyp';
/end-free
c/exec sql
c+ prepare S1 from :PrepareString
c/end-exec
c/exec sql
c+ open C1
c/end-exec
c/exec sql
c+ fetch next from C1
c+ into :INTNN, :CHARNN, :DECNN, :NUMERICNN, :VARCHARNN
c+ , :DATENN, :TIMENN
c* , :TIMES00001
c+ , :INTN :N1, :CHARN :N2, :DECN :N3
c+ , :NUMERICN :N4, :VARCHARN :N5
c+ , :DATEN :N6, :TIMEN :N7
c* , :TIMESTAMPN :N8
c/end-exec
* just for adding break point to debugger
c eval user = user
c/exec sql
c+ fetch next from C1
c+ into :INTNN, :CHARNN, :DECNN, :NUMERICNN, :VARCHARNN
c+ , :DATENN, :TIMENN
c* , :TIMES00001
c+ , :INTN :N1, :CHARN :N2, :DECN :N3
c+ , :NUMERICN :N4, :VARCHARN :N5
c+ , :DATEN :N6, :TIMEN :N7
c* , :TIMESTAMPN :N8
c/end-exec
c eval name = name
c/exec sql
c+ commit
c/end-exec
c/exec sql
c+ close C1
c/end-exec
C return

just use the create command, from the beginning in the source, or you
could use the little programm CRTCPP, it will pull teh create commands out
of the source and execute. You could make your own pdm option, or configer
the create command in RDI.

As I stated before, not all parts of embedded SQL are supported, there are
some hints in the docs to be found at SourceForge.

Have fun with ArdGate

Dieter




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.