Topic
11 replies Latest Post - ‏2012-03-09T20:08:21Z by scjt2001
scjt2001
scjt2001
18 Posts
ACCEPTED ANSWER

Pinned topic Does any body has Example of Blocked Insert using SQLCLI in RPGLE

‏2012-03-05T05:37:17Z |
I am trying to write a blocked Insert using SQLCI in RPGLE, for some reason i was able to write upe 4 records at a time with out any probelm but if it is more than that it gives Receiver value too small to hold result error.

So far this is what i have tried i have Physical file with 2 fields FLD1 char(15) and FLD2 char(10)

D wind S 5I 0 Dim(8)

Sql='Insert into TempFile ? rows values(?,?);
SQLAllocEnv (henv);
SQLSetEnvAttrI(henv:SQL_ATTR_OUTPUT_NTS:SQL_FALSE:0);
SQLAllocConnect(henv: hdbc);
rc=SQLSetConnectAttrI(hdbc:SQL_ATTR_DBC_SYS_NAMING:SQL_TRUE:0);
rc=SQLSetConnectAttrI(hdbc:SQL_AUTOCOMMIT_OFF:SQL_TRUE:0);
rc=SQLConnect(hdbc:'*LOCAL':SQL_NTS:*null:SQL_NTS:*null:SQL_NTS);
rc = SQLAllocStmt(hdbc:hstmti)
rc = SQLPrepare(hstmti:sql:SQL_NTS);
Rows=8;
SQLParamOptions(hstmti:rows:*omit);
//buffer containing 8 record of data to write in the file
buffer='row1col1 row1col2 row2col1 row2col2 row3col1 row3col2 row4col1 row4col2
row5col1 row5col2 row6col1 row6col2 row7col1 row7col2 row8col1 row8col2 "
ptr=%addr(buffer);
SQLParamOptions(hstmti:rows:*omit);
rc = SQLBindParameter(hstmti:1:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR::15:0:ptr:15:wind(1));
rc = SQLBindParameter(hstmti:2:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR::10:0:ptr+10:10:wind(2));
rc = SQLExecute(sqlinfo.hstmti);
Updated on 2012-03-09T20:08:21Z at 2012-03-09T20:08:21Z by scjt2001
  • scjt2001
    scjt2001
    18 Posts
    ACCEPTED ANSWER

    Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

    ‏2012-03-05T05:42:14Z  in response to scjt2001
    buffer="row1col1 row1col2 row2col1 row2col2 row3col1 row3col2 row4col1 row4col2
    row5col1 row5col2 row6col1 row6col2 row7col1 row7col2 row8col1 row8col2 "
    • scjt2001
      scjt2001
      18 Posts
      ACCEPTED ANSWER

      Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

      ‏2012-03-05T05:45:15Z  in response to scjt2001
      for some reason the space in the buffer are gettring stripped off when i post it, it is of the format ROW1COL1xxxxxxxROW1COL2xxROW2COL1xxxxxxxROW2COL2xxROW3COL1xxxxxxxROW3COL2xxROW4COL1xxxxxxxROW4COL2xxROW5COL1xxxxxxxROW5COL2xxROW6COL1xxxxxxxROW6COL2xxROW7COL1xxxxxxxROW7COL2xxROW8COL1xxxxxxxROW8COL2xx
      x is the space
  • barbara_morris
    barbara_morris
    372 Posts
    ACCEPTED ANSWER

    Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

    ‏2012-03-05T14:48:18Z  in response to scjt2001
    To post code in this forum, put a "code" tag at the beginning and ending of the code. The end tag is exactly the same as the beginning tag. Like this, but with no spaces.
    { c o d e }

    Here is your code the way you first posted it.
    
    D wind            S              5I 0 Dim(8)   Sql=
    'Insert into TempFile ? rows values(?,?); SQLAllocEnv (henv); SQLSetEnvAttrI(henv:SQL_ATTR_OUTPUT_NTS:SQL_FALSE:0); SQLAllocConnect(henv: hdbc); rc=SQLSetConnectAttrI(hdbc:SQL_ATTR_DBC_SYS_NAMING:SQL_TRUE:0); rc=SQLSetConnectAttrI(hdbc:SQL_AUTOCOMMIT_OFF:SQL_TRUE:0); rc=SQLConnect(hdbc:
    '*LOCAL':SQL_NTS:*
    
    null:SQL_NTS:*
    
    null:SQL_NTS); rc = SQLAllocStmt(hdbc:hstmti) rc = SQLPrepare(hstmti:sql:SQL_NTS); Rows=8; SQLParamOptions(hstmti:rows:*omit); 
    //buffer containing 8 record of data to write in the file buffer=
    'row1col1       row1col2  row2col1       row2col2  row3col1       row3col2  row4col1       row4col2   row5col1       row5col2  row6col1       row6col2  row7col1       row7col2  row8col1       row8col2  
    "         ptr=%addr(buffer); SQLParamOptions(hstmti:rows:*omit); rc = SQLBindParameter(hstmti:1:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR::15:0:ptr:15:wind(1)); rc = SQLBindParameter(hstmti:2:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR::10:0:ptr+10:10:wind(2)); rc = SQLExecute(sqlinfo.hstmti);
    
    • scjt2001
      scjt2001
      18 Posts
      ACCEPTED ANSWER

      Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

      ‏2012-03-05T15:27:03Z  in response to barbara_morris
      Thanks Barbara, here is my final corrected version

      
      D wind            S              5I 0 Dim(16) D SQL             S           1000A D henv            S                   like(SQLHENV  ) inz D hdbc            S                   like(SQLHDBC  ) inz D hstmtI          s                   like(SQLHSTMT ) inz D rc              S                   like(SQLRETURN) inz D ptr             S               * D rows            S 10I 0 D Buffer          S            200A /free Sql=
      'Insert into TempFile ? rows values(?,?)'; SQLAllocEnv (henv); SQLSetEnvAttrI(henv:SQL_ATTR_OUTPUT_NTS:SQL_FALSE:0); SQLAllocConnect(henv: hdbc); rc=SQLSetConnectAttrI(hdbc:SQL_ATTR_DBC_SYS_NAMING:SQL_TRUE:0); rc=SQLSetConnectAttrI(hdbc:SQL_AUTOCOMMIT_OFF:SQL_TRUE:0); rc=SQLConnect(hdbc:
      '*LOCAL':SQL_NTS:*
      
      null:SQL_NTS:*
      
      null:SQL_NTS); rc = SQLAllocStmt(hdbc:hstmti); rc = SQLPrepare(hstmti:sql:SQL_NTS); Rows=2; SQLParamOptions(hstmti:rows:*omit); 
      //buffer containing 8 record of data to write in the file              buffer=
      'row1col1xxyyyyyrow1col2xxrow2col1xxyyyyyrow2col2xx+            row3col1xxyyyyyrow3col2xxrow4col1xxyyyyyrow4col2xx+ row5col1xxyyyyyrow5col2xxrow6col1xxyyyyyrow6col2xx+ row7col1xxyyyyyrow7col2xxrow8col1xxyyyyyrow8col2xx
      ';                   ptr=%addr(buffer); SQLParamOptions(hstmti:rows:*omit); rc = SQLBindParameter(hstmti:1:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR :15:0:ptr:15:wind(1)); rc = SQLBindParameter(hstmti:2:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR :10:0:ptr+15:10:wind(2) ); rc =SqlExecute(hstmti); close_sqlcli(hstmti); SQLDisconnect (hdbc); SQLFreeConnect (hdbc); SQLFreeEnv (henv); SQLReleaseEnv(henv); *inlr=*on; /end-free
      
  • scott_klement
    scott_klement
    241 Posts
    ACCEPTED ANSWER

    Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

    ‏2012-03-05T23:15:00Z  in response to scjt2001
    Hello scjt2001,

    I see a few issues:

    1) 'wind' is defined as 5i 0, but the documentation says that the 10th parameter to SQLBindParameter() needs to be an SQLINTEGER (which is 10i 0, not 5i 0)

    2) Your code calls a routine named 'close_sqlcli' that you didn't post. I'm confused about what this routine would do, since you close the CLI environment (disconnect, delete the handles, etc) after calling close_sqlcli.

    3) You have rows=2 in your code, but your buffer contains 8 rows. As long as rows=2 is set, it'll only write the first 2 rows. (of course!)

    I'll see if I can change your code to make it run properly on my box.
    • scott_klement
      scott_klement
      241 Posts
      ACCEPTED ANSWER

      Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

      ‏2012-03-05T23:36:00Z  in response to scott_klement
      Okay, here's my modified version. This version successfully inserts 8 rows on my machine. (I tested it.)

      Most of the modifications I added here were just comments. In some places I rearranged the code a little bit, though your code would've worked -- I just rearranged it because it made it easier for me to read (which was important for me to make it work.)

      
      H DFTACTGRP(*NO) ACTGRP(
      'CLIINSARRY') H OPTION(*SRCSTMT:*NODEBUGIO)   /copy sqlcli_h   
      // changed 'wind' from 5i 0 to 10i 0 D wind            S             10I 0 Dim(16) D SQL             S           1000A D henv            S                   like(SQLHENV  ) inz D hdbc            S                   like(SQLHDBC  ) inz D hstmtI          s                   like(SQLHSTMT ) inz D rc              S                   like(SQLRETURN) inz D ptr             S               * D rows            S             10I 0 D Buffer          S            200A   /free     
      // Create a CLI environment and connect to the local database. SQLAllocEnv (henv); SQLSetEnvAttrI(henv:SQL_ATTR_OUTPUT_NTS:SQL_FALSE:0); SQLAllocConnect(henv: hdbc); rc=SQLSetConnectAttrI(hdbc:SQL_ATTR_DBC_SYS_NAMING:SQL_TRUE:0); rc=SQLSetConnectAttrI(hdbc:SQL_AUTOCOMMIT_OFF:SQL_TRUE:0); rc=SQLConnect(hdbc:
      '*LOCAL':SQL_NTS:*
      
      null:SQL_NTS:*
      
      null:SQL_NTS);   
      // Create a statement handle & prepare the insert statement. Sql=
      'Insert into TempFile ? rows values(?,?)'; rc = SQLAllocStmt(hdbc:hstmti); rc = SQLPrepare(hstmti:sql:SQL_NTS);   
      // This tells CLI that I want to insert 8 rows (instead of 1) 
      // for this to work, all of my row data must be contiguous. Rows=8; SQLParamOptions(hstmti:rows:*omit);   
      // buffer containing 8 record of data to write in the file 
      // (table should have two char(15) columns in each row.)   buffer=
      'row1col1xxyyyyyrow1col2xxrow2col1xxyyyyyrow2col2xx+ row3col1xxyyyyyrow3col2xxrow4col1xxyyyyyrow4col2xx+ row5col1xxyyyyyrow5col2xxrow6col1xxyyyyyrow6col2xx+ row7col1xxyyyyyrow7col2xxrow8col1xxyyyyyrow8col2xx
      ';   ptr=%addr(buffer);   
      // Removed: I didn't understand why this was called twice. 
      // SQLParamOptions(hstmti:rows:*omit);   
      // Defines the columns (via parameter markers) 
      //   we only need to give the addresses of the first row, here 
      //   (the system will calculate the subsequent rows.)   rc = SQLBindParameter(hstmti:1:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR :15:0:ptr:15:wind(1)); rc = SQLBindParameter(hstmti:2:SQL_PARAM_INPUT:SQL_C_DEFAULT:SQL_CHAR :10:0:ptr+15:10:wind(2));   
      // execute the insert rc = SqlExecute(hstmti);   
      // clean up the SQL CLI statement, connection and environment. SQLFreeStmt(hstmti: SQL_DROP);    
      //was: close_sqlcli(hstmti);   SQLDisconnect (hdbc); SQLFreeConnect (hdbc); SQLFreeEnv (henv);   
      // SQLReleaseEnv(henv);  <-- this won't do anything, since 
      //                        the handle is already freed.   *inlr=*on; /end-free
      


      To test this code, I ran the following:
      
      create table tempfile ( col1 
      
      char(15), col2 
      
      char(15) )
      


      I used the CLI prototypes from the following copy book:
      http://www.scottklement.com/rpg/copybooks/sqlcli_h.rpgle.txt

      I compiled the (above) RPG code as follows:
      
      CRTBNDRPG PGM(CLIINSARRY) SRCILE(QRPGLESRC) DBGVIEW(*LIST)
      


      I ran it, and it inserted all 8 rows correctly. Since your code doesn't check for errors, I don't know if any of the statements failed -- but I assume that none did, since everything seemed to work fine.

      The output of DSPPFM TEMPFILE:
      
      Display Physical File Member File . . . . . . :   TEMPFILE            Library  . . . . :   SCOTTTEST Member . . . . . :   TEMPFILE            Record . . . . . :   1 Control  . . . . .                       Column . . . . . :   1 Find . . . . . . . *...+....1....+....2....+....3 row1col1xxyyyyyrow1col2xx row2col1xxyyyyyrow2col2xx row3col1xxyyyyyrow3col2xx row4col1xxyyyyyrow4col2xx row5col1xxyyyyyrow5col2xx row6col1xxyyyyyrow6col2xx row7col1xxyyyyyrow7col2xx row8col1xxyyyyyrow8col2xx ****** END OF DATA ****** Bottom F3=Exit   F12=Cancel   F19=Left   F20=Right   F24=More keys
      


      So... it all worked, I'm guessing the main problem you had was with the "10i 0" vs "5i 0" in the definition of 'wind'. With my copy book, I couldn't even compile the program without changing 'wind' to 10i 0, because my prototype of SQLBindParameter requires a 10i 0. Other than that, it all seemed to work okay.
    • scjt2001
      scjt2001
      18 Posts
      ACCEPTED ANSWER

      Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

      ‏2012-03-05T23:36:17Z  in response to scott_klement
      Thankyou very much. It was the problem with wind, i changed it to 10I 0, and it works fine with any number of blocked inserts.

      Close_SQLCLI was just doing SQLFreeStmt(hstmt:SQL_DROP);
      with Wind set to 5I 0, it was working when rows were set to 2, but fails with "receiver value too small error" when i set it to 8 rows

      I would also like to get your advice what will be the value that need to be passed in SQLBindParameter when the datatype is Decimal, Numeric , Date, Time or TimeStamp.
      • scott_klement
        scott_klement
        241 Posts
        ACCEPTED ANSWER

        Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

        ‏2012-03-05T23:39:55Z  in response to scjt2001
        > scjt2001 wrote:
        > Thankyou very much. It was the problem with wind, i changed it to 10I 0, and it works fine with any number of blocked inserts.

        yep... see my most recent post, you'll see that I discovered the same thing.
        > I would also like to get your advice what will be the value that need to be passed in
        > SQLBindParameter when the datatype is Decimal, Numeric , Date, Time or TimeStamp.

        I would use a data structure to define them. Is that what you're asking? Maybe I just don't get what you're asking.
        • scjt2001
          scjt2001
          18 Posts
          ACCEPTED ANSWER

          Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

          ‏2012-03-05T23:54:17Z  in response to scott_klement
          What will be value in ColSize,Scale,BufferLen parameters of SqlBindParameter Procedure

          For example
          if the field is Decimal (7,2) precision and scale, i am assuming it will be Colsize=7,scale=2 and bufferlen=4 (since its packed)
          if the field is Numeric (7,2) precision and scale, i am assuming it Colsize=7,scale=2 and bufferlen=7
          if the field is Date, i am assuming it will be Colsize=10,scale=0 and bufferlen=10
          if the field is Time, i am assuming it will be Colsize=8,scale=0 and bufferlen=8
          if the field is TimeStamp, i am assuming it will be Colsize=26,scale=0 and bufferlen=26
          • scott_klement
            scott_klement
            241 Posts
            ACCEPTED ANSWER

            Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

            ‏2012-03-06T01:19:50Z  in response to scjt2001
            The SQLBindParameter API is documented here:
            http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/cli/rzadpfnbndpm.htm

            It says that the bufferlen is unused. (But, I think your values are reasonable... I'd just let RPG figure it out with %size)

            I agree with your colsize and scale values for the decimal/numeric examples.

            I believe ColSize is ignored for date/time/timestamp

            I believe Scale is ignored for date/time. For timestamp it indicates the number of digits in the "fraction of a second" field. (i.e. 3 if you include milliseconds, 6 if you include microseconds.) But, I must admit, I haven't used timestamps in CLI... So I'm pretty much just reciting what it says in the manual.
            • scjt2001
              scjt2001
              18 Posts
              ACCEPTED ANSWER

              Re: Does any body has Example of Blocked Insert using SQLCLI in RPGLE

              ‏2012-03-09T20:08:21Z  in response to scott_klement
              Thanks Scott.

              I had written a RPG Open access SQL Handler to transform Open,Close,Read, Write open to it SQL version with Record blocking functionality. The initial version was written in Embedded SQL, but since in Embbeded version you can't name a cursor dynamically, it had some limitation. So i re-wrote the Handler using SQLCLI, but I am finding the perfromance of Embedded version is much better than SQLCLI version when you read a block of records. For instance if I read 25000 records from a file, the embedded verion does only 1 I/O but the SQLCLI version does around 490 I/O.

              Will SQL API QSQPRCED give much better performace?