Re: Dynamic SQL

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
You're probably stringing together numerics and alpha's.  In the process
convert your numerics into alpha.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




bdietz@3x.com
Sent by: rpg400-l-admin@midrange.com
11/07/2002 10:53 AM
Please respond to rpg400-l

        To:     rpg400-l@midrange.com
        cc:
        Fax to:
        Subject:        Dynamic SQL


I'm having a brain cramp trying to get the following SQL to work.  First a
little history.....

I am writing a program that outputs the results of the NETSTAT API
(QtocLstNetCnn),  When this works I will make it available to the iSeries
community.
I am trying to output some data from a RPGLE program to a file(table) that
is created in the program.  I have done this before but in this case the
data types are both CHAR and NUMERIC.

I don't  want to have the file created before hand as it is a varible to
the program.  The biggest issue is how to construct the EVAL statement so
that I can mix both CHAR and numeric data while keeping the varible file
name.

The error is     RNF7416 --- The types of the right and left hand side do
not match in the EVAL operation.

first the create table:  (this works)
 eval      sqlStm = 'create table ' +
              %trim(##lib) + '/' +
              %trim(##File) + '('    +
              ' LclAddr    CHAR(15)  NOT NULL,' +
              ' RmtAddr    CHAR(15)  NOT NULL,' +
              ' LclPort    DEC(5,0)  NOT NULL,' +
              ' RmtPort    DEC(5,0)  NOT NULL,' +
              ' BytesIn    DEC(10,0) NOT NULL,' +
              ' BytesOut   DEC(10,0) NOT NULL,' +
              ' IdleHour   DEC(3,0)  NOT NULL,' +
              ' IdleMin    DEC(2,0)  NOT NULL,' +
              ' IdelSec    DEC(2,0)  NOT NULL,' +
              ' ConnStat   CHAR(10)  NOT NULL,' +
              ' ConnType   CHAR(10)  NOT NULL,' +
              ' OutPutTime TIMESTAMP +
                NOT NULL WITH DEFAULT ' +
               ') '
then the INSERT INTO  (which is where the problem is..)

 eval      sqlStm1 = 'insert into ' +
              %trim(##lib) + '/' +
              %trim(##File) + '('    +
              ' LclAddr,' +
              ' RmtAddr,' +
              ' LclPort,' +
              ' RmtPort,' +
              ' BytesIn,' +
              ' BytesOut,' +
              ' IdleHour,' +
              ' IdleMin,' +
              ' IdelSec,' +
              ' ConnStat,'+
              ' ConnType' +
               ') Values('+ tick +
              c1locadr + tick + ', ' + tick +
              c1rmtadr + tick + ', ' + tick +
              lclport  + tick + ', ' + tick +
              rmtport  + tick + ', ' + tick +
              bytein   + tick + ', ' + tick +
             byteout  + tick + ', ' + tick +
             idleh    + tick + ', ' + tick +
             idlem    + tick + ', ' + tick +
             idles    + tick + ', ' + tick +
             fld006   + tick + ', ' + tick +
             c1netcnntyp + tick + ')'






-------------------------
 Bryan Dietz
3X Corporation
614-410-9205



_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.