Re: Using SQL CONNECT problem to MS SQL database

[Darryl Freinkel <dhfreinkel@xxxxxxxxx>]

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.