Using SQL in the Real World

[eagarcia@earthlink.net]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Remove the comma after CVPLNAME: d inz('Select + d cvSVNCD, CVSFCCD, CVPLNAME+ d from filecco/cvdemmpi')

[Pauly D]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
sql should read: 'Select cvSVNCD, CVSFCCD, CVPLNAME from filecco/cvdemmpi') no comma's after list of field names. A good way to test your SQl is to put it in a SQL editor and run it. Once it is clean you can cut & paste into your source. Also if you are running 5vr4 onwards you can run sql in free format by putting "exec sql" in front of your sql statements. Also you may be able to simplify your Cursor declare if you are not having to dynaically build it. I also tend to just use sqlstt <>zeros for a successful read. (a bit lazy, but it covers unexpected return codes) * * SQL return codes d SQLSuccess c '00000' d SQLNoData c '02000' d SQLNoMoreData c '02000' d SQLDupRecd c '23505' d SQLRowLocked c '57033' /free exec sql declare mainCursor Cursor for Select cvSVNCD, CVSFCCD, CVPLNAME from filecco/cvdemmpi; exec sql open mainCursor; exec sql Fetch mainCursor into :data; dow sqlstt = sqlsuccess; // do some stuff exec sql Fetch mainCursor into :data; enddo; /end-free

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Thanks!! I got it to work. Thanks for the return code information. I need to check duplicate records, too. I'll probably have more question on the way of the development. Helen

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Can a call statement using data structure as parm. I have a list of parameter to pass and I have this list of parameters in a data structure. Thanks!!

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
I have embedded SQL read a file successfully but now I just don't the insert statement work. Here are pieces from my program. * MP Person vendor DMPCVMPI_DS E DS EXTNAME(MPCVMPIV1) PREFIX(M_) * D MPSPVEN_DS DS D MPVVNCD LIKE(M_MPSVNCD) D MPVFCCD LIKE(M_MPSFCCD) D MPVPID LIKE(M_MPSPID) D MPVCHRTN LIKE(M_MPSCHRTN) D MPVVACCT LIKE(M_MPSVACCT) D MPVAUDTS LIKE(M_MPMAUDTS) D MPVAUDUS LIKE(M_MPMAUDUS) D MPVAUDWK LIKE(M_MPMAUDWK) D data DS d CVSVNCD 5A d CVSFCCD 5A d CVSCHRTN 20A d CVSVACCT 22A D pcounter S like(m_mpspid) d sqlstring s 32000a varying d inz('Select * + d from cvtmp/cvdemmpi') * C/EXEC SQL c+ declare mainCursor Cursor c+ for c+ mainStatement C/END-EXEC * C/EXEC SQL c+ prepare mainStatement c+ from :sqlString C/END-EXEC * C/EXEC SQL c+ open mainCursor C/END-EXEC * * initial read * C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC c dow sqlstt = '00000' * c if sqlstt = '02000' c eval *inlr = *on c leave c endif * c exsr mainsr * C/EXEC SQL c+ insert into mpcvmpi_ds c+ (m_mpsvncd, m_mpsfccd, m_mpspid, c+ m_mpchrtn, m_mpmaudts, m_mpmaudus, c+ m_mpmaudwk) c+ select c+ mpvvncd, mpvfccd, mpvpid, mpvchrtn, c+ mpvvacct, mpvaudts, mpvaudus, mpvaudwk c+ from mpspven_ds C/END-EXEC * C/EXEC SQL c+ fetch next c+ from mainCursor c+ into ATA C/END-EXEC * C enddo C/EXEC SQL c+ close maincursor C/END-EXEC C mainsr begsr * c eval pcounter = pcounter + 1 c exsr vndmpisr c exsr personsr * C endsr * * populate vendor mpi information * C vndmpisr begsr * c eval mpvvncd = cvsvncd c eval mpvfccd = cvsfccd c eval mpvpid = pcounter c eval mpvchrtn = cvschrtn c eval mpvvacct = cvsvacct c* eval mpvaudts = sys_timdat c eval mpvaudus = userid c eval mpvaudwk = job_name * C endsr Thanks in advance!!

[Pauly D]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
The syntax for insert is: (taken from the IBM SQL reference manual) INSERT INTO table-name view-name , ( column-name ) OVERRIDING SYSTEM VALUE OVERRIDING USER VALUE , VALUES expression DEFAULT isolation-clause NULL , ( expression ) DEFAULT NULL insert-multiple-rows isolation-clause select-statement Anyway, I am no SQL guru but it seems to me you are inserting into a Data structure not a table. Just because you have defined an internal DS as the same as an external table does NOT mean that the table will get updated when you update the DS. Data Structure DMPCVMPI_DS is merely an internal storage for local data. To get that data into an external table you must use SQL to update the table (you can't update an internal DS with SQL - I would be surpised if the SQL pre-compiler will actually compile your program). You have populated your DS(MPSPVEN_DS) with values. You now want create e new record in your file with those values. Use the following: exec sql insert into MPCVMPIV1 values :MPSPVEN_DS This only works as long as the DS is an exact replica of the file format. If it isn't you would list the values by column/field: exec sql insert into MPCVMPIV1 values (:field1, :field2,:field3, etc,etc) Note also the colon( before each field or data structure. This tells the SQL precompiler that this is a local variable to be used as a value. Without this it wont work.

[Pauly D]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
absolutely. Just define the DS in both programs. This example passes a 256 char parm even though it is only using the first 40 characters. eg IW7SLST DS I P 1 82W1APP I P 9 162W1UNAL I P 17 242W1WOFF I P 25 322W1TGLA I P 33 402W1DSCT I 41 256 W1FIL2 in the calling program : /free Call program2 (w7slst) /end-free in the called program either: C *ENTRY PLIST C PARM W7SLST Or if using procedure interface D Main PR extpgm('ECMF48B') d 256a D Main PI d inParm1 256a /free w7slst = inParm1; /end-free You may even be able to define inParm2 the same as the DS using the "likeds" keyword. I am not sure of this though. I usually just move the incoming parm to the DS and then use the fields.

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Thanks all!! I really appreciate. Helen

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
Hi, I need another help again. I need to verify data existence using SQLRPGLE. Is there a function like "CHAIN" is RPGLE? Thanks in advance!! Helen

[nrn0622]

Re:Using SQL in the Real World

** This thread discusses the article: Using SQL in the Real World **
HI, Please ignore previous "help". I don't need to do that part. I have another question. Please refer to the following code. I have SQLCOD = -440 after I execute the statement. What can I do to fix or what is the problem? Thanks!! C executesr begsr * * preapre call mpsp_vendor_person_match_error C EVAL SQLSTR = 'CALL MPSP_MATCH_ERROR ' + C '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' C/EXEC SQL c+ PREPARE CALL_MPSP_V_P_M FROM :SQLSTR C/END-EXEC * c if sqlcod < 0 c eval objname = 'V_P_M SQLSTR' c exsr cssqlerr c endif * c eval ivall_match1 = *zeros C/EXEC SQL c+ EXECUTE CALL_MPSP_V_M_I c+ using :mpspmatch1_ds :ivall_match1 C/END-EXEC *

[nrn0622]

Re:Using SQL in the Real World

Please ignore all the message I posted today. I figured out what is PROCEDURE. Thanks for all the help anyway.

[efnkay]

Re:Using SQL in the Real World

This may be helpful to you. The SQLCOD values returned (usually on errors) are numbers that reflect the message found in QSQLMSG message file. Just take the absolute value of the SQLCOD: (-440) = SQL0440 Message Id. Message ID . . . . . . . . . : SQL0440 Message file . . . . . . . . : QSQLMSG Library . . . . . . . . . : QSYS Message text . . . . . . . . : Routine &1 in &2 not found with specified para meters.