× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Here is a sample program.  the first describe initializes the SQLDA with the 
select information (i.e. number of variables,etc.)

The second describe actually allocates the storage needed by setting the SQLN 
appropriately.

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyra Brodsky
Sent: Friday, June 17, 2005 12:16 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQLDA question


I don't think so.  How do I allocate storage?

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy
Sent: Friday, June 17, 2005 10:14 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQLDA question

did you allocate storage???

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyra Brodsky
Sent: Friday, June 17, 2005 11:30 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQLDA question


I am trying to use the SQLDA to dynamically create SQL statements.  I am
getting the following error when running the program 

                                Message ID . . . . . . :   SQL0822
Severity . . . . . . . :   30         
                                Message type . . . . . :   Diagnostic

                                Date sent  . . . . . . :   06/16/05
Time sent  . . . . . . :   12:26:47   
        

                                Message . . . . :   Address in the SQLDA
not valid.                            
                                Cause . . . . . :   The SQLDA contains
an address, SQLDATA value, or SQLIND    
                                  value in entry number 1 that is not
valid.  The incorrect address or value   
                                  is type 1.

                                    -- Type 1 indicates that the SQLDATA
address is not valid.                 
                                    -- Type 2 indicates that the SQLIND
address is not valid.                  
                                    -- Type 3 indicates that the SQLDA
address is not valid.                   
                                    -- Type 4 indicates that the row
storage area is not large enough.         
                                    -- Type 5 indicates that the
indicator area for a blocked FETCH statement  
                                  is not large enough.

                                    -- Type 6 indicates that the SQLDATA
field was not initialized to a value  
                                  in a REXX procedure 
                                     -- Type 7 indicates that the SQLIND
field was not initialized to a value   
                                   in a REXX procedure.

                                     -- Type 8 indicates that the
SQLDATAL address is not valid.                
                                 Recovery  . . . :   For types 1, 2, 3,
or 8, change the address in entry 1 to  
                                   a valid address. For types 4 and 5,
allocate enough area for all of the rows 
                                   being requested. For types 6 and 7,
initialize the SQLDATA or SQLIND fields  
                                   to a valid value. Try the request
again.                                     
                                 Technical description . . . . . . . . :
When an SQLDA is used on FETCH,      
                                   OPEN, or EXECUTE, the address in each
SQLDATA entry in the SQLDA must be set 
                                   to a valid address.  If the SQLTYPE
value indicates that an indicator        
                                   variable is also provided, the
corresponding SQLIND entry must contain a     
                                   valid address in addition to the
SQLDA being set to a valid address. In the  
                                   case of a REXX procedure, the SQLDATA
and SQLIND fields are not pointers,    
                                    but are buffers containing the
variable and indicator values. When an SQLDA  
                                is used on an OPEN or EXECUTE, these
variables must be initialized to a      
                                valid value.             

                                So, I can tell that I have a problem
with the address in SQLDATA.  I think the error is in reference to my
open statement.  I assume I need to have an eval statement for the
SQLDATA, but I don't have a clue what to put there.... Here is a snippet
of my code.  Any ideas of what I'm doing wrong?  I've also tried it with
sqldabc = 1 * 80 + 16 and sqldabc = 2 * 80 + 16 for the second
occurance.  Same error


                                C                   eval      SQLN = 2

                                C                   eval      SQLD = 2

                                C                   eval      SQLTYPE =
452              
                                C                   eval      SQLLEN  =
1                
                                C                   eval      SQLNAME =
'SAMPLE'         
                                C                   eval      sqldabc =
SQLN*80+16       
                                C                   movea     sqlvar
sql_var(1)   
                                C                   eval      SQLTYPE =
452              
                                C                   eval      SQLLEN  =
1                
                                C                   eval      SQLNAME =
'SAMPLE1'        
                                C                   eval      sqldabc =
SQLN*80+16       
                                C                   movea     sqlvar
sql_var(2)   
                                C/Exec SQL

                                C+        PREPARE SQL_Stmt FROM
:SQL_String              
                                C/End-Exec

        

                                C/Exec SQL  
                                C+        DECLARE SQL_Read SCROLL CURSOR
for SQL_Stmt   
                                C/End-Exec

        

                                C/Exec SQL

                                C+        OPEN SQL_Read Using DESCRIPTOR
:SQLDA         
                                C/End-Exec

        


     H DFTACTGRP(*NO)
     H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)

      /copy prodkr/qcopysrc,clcmdexec
      /copy prodkr/qcopysrc,getcurdb

     ?* SQLDA Descriptor Area
     D SQLDA           DS
     D  SQLDAID                1      8A
     D  SQLDABC                9     12B 0
     D  SQLN                  13     14B 0
     D  SQLD                  15     16B 0
     D  SQL_VAR                      80A   DIM(400)
     D  SQL_Data_Type         17     18B 0
     D  SQL_Var_Len           19     20B 0
     D  SQL_Resrvd            21     32A
     D  SQL_Data_Pntr         33     48*
     D  SQL_Null_Ind          49     64*
     D  SQL_Name_Len          65     66B 0
     D  SQL_Col_Name          67     96A

     ?* SQLDA Variable Data
     D SQLVAR          DS
     D  SQLTYPE                1      2B 0
     D  SQLLEN                 3      4B 0
     D  SQLRES                 5     16A
     D  SQLDATA               17     32*
     D  SQLIND                33     48*
     D  SQLNAMELEN            49     50B 0
     D  SQLNAME               51     80A

     ?* SQLDA Variable Data (Extended)
     D SQLVAR2         DS
     D  SQLLONGL               1      4B 0
     D  SQLRSVDL               5     32A
     D  SQLDATAL              33     48*
     D  SQLTNAMELN            49     50B 0
     D  SQLTNAME              51     80A

     ?* Work Variables
     D File            s             10a
     D Lib             s             10a
     D TempFile        s             10a
     D TempLib         s             10a
     Drowcountx        s             10I 0
     DOrdinal          s             10I 0
     DColName          s             10a
     DDataType         s              8a
     DLength           s             10i 0
     DScale            s             10i 0
     Dn1               s              4b 0
     DPuNo             s              6a
     DDatabaseName     s            100a
     D XLSPath         s            100a
     D SQLSelect       s           5000a   Varying
     DColumnNumber     s             10i 0
     D RecordData      s          32766a
     DElement          s             10i 0

     ?* Parameter Entry
     C     *Entry        PList
     C                   Parm                    FileName         20
     C                   Parm                    TemplateFile     20
     C                   Parm                    XLSPath

     ?* SQL Error Trapping
     c/exec sql
     c+ Whenever SQLError Continue
     c/end-exec

      /free

       //?*    Extract The Input File Name
       //?*     & Load The Output Path If Not Specified

       If XLSPath=*Blanks;
          XLSPath='/tmp/xl.xls';
       EndIf;
       File=%Subst(FileName:1:10);
       Lib=%Subst(FileName:11:10);
       TempFile=%Subst(TemplateFile:1:10);
       TempLib=%Subst(TemplateFile:11:10);

       //?*  Override The Database File Being Processed

       CmdStr='OVRDBF '+%Trim(File)+' '+
             %Trim(Lib)+'/'+%Trim(File)+
             ' Share(*Yes)';
       CmdStrSize=%Len(%Trim(CmdStr));
       monitor;
       Run(CmdStr:CmdStrSize);
        on-error;
       endmon;

       //?* Build The Dynamic Excel Generation Program

       ExSR SQLSelectBuild;
      /end-free

     c/exec sql
     c+ Prepare SQLExtract From :SQLSelect
     c/end-exec
     c                   Eval      SqlN=1
     C/exec sql
     c+ Describe SQLExtract Into :SQLDA
     c/end-exec
     c                   Eval      SqlN=SqlD
     c                   Eval      SQLABC=SqlN*%Len(SqlVar)+16
     c/exec sql
     c+ Describe SQLExtract Into :SQLDA
     c/end-exec
     c/exec sql
     c+ Declare SQL_Extract Cursor For SQLExtract
     c/end-exec
     c/exec sql
     c+ open SQL_Extract Using Descriptor :SQLDA
     c/end-exec
     c                   DoU       SqlCod=100
     c/exec sql
     c+ Fetch SQL_Extract Into Descriptor :SQLDA
     c/end-exec
     c                   If        SqlCod=100
     c/exec sql
     c+ Close SQL_Extract
     c/end-exec
     c                   Leave
     c                   EndIf
     c                   ExSR      ExtractData
     c                   EndDo
      /free

       //?* Terminate

        *inlr = *on;

       BegSR ExtractData;
           For Element = 1 By 1 To SqlD;
              SQLVAR=SQL_VAR(Element);
           EndFor;
       EndSR;

      /end-free

     ?*
     ?*  Build SQL Select Statement From Template File
     C     SQLSelectBuildBegSR
     C                   Eval      SQLSelect='Select '

     ?* Declare The SQL Cursor
     c/exec sql
     c+ Declare StringBuild Cursor
     c+   For Select Column_Name,Data_Type,Length,Numeric_Scale,
     c+           Ordinal_Position From SYSCOLUMNS
     c+        Where System_Table_Name= :TempFile And
     c+                 System_Table_Schema = :TempLib
     c+         Order By Ordinal_Position
     c/end-exec

     ?* Open The SQL Cursor
     c/exec sql
     c+ Open StringBuild
     c/end-exec

     ?* Error Occurred On Open
     c                   If        SQLCod<0
     c                   LeaveSR
     c                   EndIf

     ?* Load The Source Code For All Fields In the File
     c                   Eval      rowcountx=4
     c                   DoU       SQLCod=100
     c/exec sql
     c+ Fetch StringBuild Into :ColName,:DataType,:Length,
     c+             :Scale:n1,:Ordinal
     c/end-exec

     ?* End Of Records
     c                   If        SQLCod=100
     c                   Leave
     c                   EndIf

     ?* Error On Fetch
     c                   If        SQLCod<0
     c                   Iter
     c                   EndIf
     c                   Eval      SQLSelect=%Trim(SQLSelect)+' '+
     c                             %Trim(ColName)+','
     c                   EndDo
     c                   Eval      Length=%Len(%Trim(SQLSelect))
     c                   Eval      SQLSelect=%Subst(SQLSelect:1:Length-1)
     c                   Eval      SQLSelect=%Trim(SQLSelect)+
     c                             ' From '+%Trim(Lib)+'/'
     c                             +%Trim(File)
     c                   EndSR

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.