Re: Using SQL CONNECT problem to MS SQL database

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 08/25/2016
03:15:34 PM:

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.

It looks to me like your MS SQL server instance is either trying
to authenticate using Windows authority (which your JDBC connection won't
have any Windows authority established) or the MS SQL server instance has
not authorized SQL_USER with password SqlPwd to access the database. But,
you said it works from STRSQL? You connected with STRSQL but did you try
the SELECT statement in STRSQL, too?

Sincerely,

Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331

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.

--
Darryl Freinkel
--
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.


************************************************************
*********************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary
information. If you have received this message in error, please
immediately notify the sender and delete and destroy the message and all
copies. All unauthorized direct or indirect use or disclosure of this
message is strictly prohibited. No right to confidentiality or privilege
is waived or lost by any error in transmission.
************************************************************
*********************************
--
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.