Generic / Dynamic Host Variables

[mjhaston]

Looking to create something dynamic/generic enough to handle any file thrown at it. I'd like to dump a physical file to Excel and populate the headings and then data. I'm running into issues with the host variable.

Compile error:

SQL0312  30     304  Position 41 Variable FIELDS not defined or not usable.

My goal is to load up an array with the meta data and use it later in the program to output column headings in an Excel spreadsheet. I'll have this same need later when reading data from the file.

d                 ds                                             
d fields                              dim( 999 )                 
d  columnName                   50a   overlay( fields )          
d  columnText                   50a   overlay( fields : *next )  
d  dataType                     50a   overlay( fields : *next )  
d  length                       10i 0 overlay( fields : *next )  
d  numericScale                  5i 0 overlay( fields : *next ) 



exec sql                              
 declare c1 cursor for                
                                      
 select                               
   c.column_name                      
   , c.column_text                    
   , c.data_type                      
   , c.length                         
   , c.numeric_scale                  
   , t.table_schema                   
   , t.table_name                     
   , t.system_table_name              
   , c.system_column_name             
                                      
 from qsys2/systables t               
      , qsys2/syscolumns c              
                                      
 where                                
   t.table_name = c.table_name        
   and t.table_schema = c.table_schema
   and t.table_schema = :library      
   and t.table_name  = :file ;        

   
exec sql                  
 open c1;                 
                          
exec sql                  
 fetch c1 into :fields ;

[tomholden]

Re: Generic / Dynamic Host Variables

check out http://sourceforge.net/projects/iseriestoexcel it uses the service program Scott Klement wrote to interface with POI and is a command. it uses dynamic SQL in a similar fashion to what you are proposing. but i haven't figured out a way to fetch more than a single row at a time with using the SQLDA...

[mjhaston]

Re: Generic / Dynamic Host Variables

Ah, you know, I thought this was the same one I'm currently checking out by Scott that goes the opposite direction. It grabs an IFS spreadsheet and creates a physical file out of it.

[mjhaston]

Re: Generic / Dynamic Host Variables

Getting this error message. Not sure I know what I'm doing

                    Create XLS Spreadsheet From PF (XLCRT)                  
                                                                            
Type choices, press Enter.                                                  
                                                                            
File To Convert: . . . . . . . . > JCP812PF      Name                       
  Library: . . . . . . . . . . . >   HB2320BFUS  Name, *LIBL, *CURLIB       
Document to Save As: . . . . . . > JCP812PF.XLS                             
                                                                            
                                                                            
Operation To Perform:  . . . . . > *COMPLETE     *COMPLETE, *SHEET, *OPEN...
Company Defaults Key . . . . . . > BETSON        Character value

Message . . . . :   Environment variable name exists.                           
Cause . . . . . :   You tried to add an environment variable (ADDENVVAR),       
  'CLASSPATH', of level *JOB, that already exists. Recovery  . . . :   Do one   
  of the following: - Specify the REPLACE(*YES) option to replace the existing  
  value with the new value. - Use the CHGENVVAR command to change the           
  specified environment variable. - Retry the command with an environment       
  variable that does not exist.

[tomholden]

Re: Generic / Dynamic Host Variables

does it bomb or just issue that as a warning/diagnostic message?

[mjhaston]

Re: Generic / Dynamic Host Variables

That appears in the job log, but the .xls doesn't get created.

Do I need to download the POI.jar file? In the setup I pointed the tool to our existing jt400.jar file.

[tomholden]

Re: Generic / Dynamic Host Variables

yep you have to have the poi jar file on your system. the ADDENVVAR command is what's failing and putting the message in your job log. if you setup the defaults using the XLDFT command and put in the path to the poi jar file then you should be good.

[FaStOnE]

Re: Generic / Dynamic Host Variables

Hey Tom!!

I'm tellin' BC you're harking the enemy!

LMAO!!!

[tomholden]

Re: Generic / Dynamic Host Variables

Hey Tom!!

I'm tellin' BC you're harking the enemy!

LMAO!!!

BC has a very huge ego problem...everyone looks at problems/design differently and considering 100s (if not 1000s) of people can compile and use the tools makes me think that since BC can't get it to compile he's not as savvy as he thinks.

[mjhaston]

Re: Generic / Dynamic Host Variables

Thanks Tom. All working now. Just one final question ....

I downloaded the entire POI zip file to our system. It's quite big. Do I just need the actual .jar files on the system? I'm wondering if I can remove what's under the /poi/docs and /poi/lib folders?

[tomholden]

Re: Generic / Dynamic Host Variables

you shouldn't have to unzip it...just put it there as a jar file...

[mjhaston]

Re: Generic / Dynamic Host Variables

I downloaded the entire POI package. So I think I'm safe in removing the other folders. The whole package is over 70 meg.

[gcraill]

Re: Generic / Dynamic Host Variables

but i haven't figured out a way to fetch more than a single row at a time with using the SQLDA...

One assumes you can't just throw the fetch command in a loop then ?

[tomholden]

Re: Generic / Dynamic Host Variables

One assumes you can't just throw the fetch command in a loop then ?

that's what it does now, but fetching single row at a time is sooo slow compared to fetching multiple rows at a time (a la blocking )

[mjhaston]

Re: Generic / Dynamic Host Variables

Same questions years later!

My goal is still .... build an ad-hoc type application. I would offer the user a file or files to choose from. Based on their selection I would like to build the SQL statement.

Putting together bits and pieces, this is where I'm at. I don't think I'm using the "descriptor" or "describe" keywords properly.

My problem still lies with trying to create the generic host variables.

     h DFTACTGRP(*NO)

     d Enquote         pr          1000a   varying
     d  string                     1000a   value varying

      *  -------------------------------------------------------------
      *  -¹ Variable declarations                                    -
      *  -------------------------------------------------------------

      * 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(255)
     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

     d sqlSelect       s           5000a   varying
     d element         s             10i 0


      /free

        sqlSelect =
           ' select                                                            '
        +  '   c.column_name                                                   '
        +  '   , case when c.column_text is not null then c.column_text        '
        +  '          else ' + Enquote( *blank )
        +  '     end                                                           '
        +  '   , c.data_type                                                   '
        +  '   , c.length                                                      '
        +  '   , case when c.numeric_scale is not null then c.numeric_scale    '
        +  '          else 99999                                               '
        +  '     end                                                           '
        +  '   , t.table_schema                                                '
        +  '   , t.table_name                                                  '
        +  '   , t.system_table_name                                           '
        +  '   , c.system_column_name                                          '
        +  ' from qsys2/systables t                                            '
        +  ' , qsys2/syscolumns c                                              '
        +  '                                                                   '
        +  ' where                                                             '
        +  '   t.table_name = c.table_name                                     '
        +  '   and t.table_schema = c.table_schema                             '
        +  '   and t.table_schema = ' + Enquote( 'MMDF' )
        +  '   and t.table_name  = ' + Enquote( 'EMPMAS' )
        ;

        exec sql  prepare sqlExtract from :sqlSelect ;

        sqlN = 1;

        exec sql  describe sqlExtract into :SQLDA using all ;

        sqlN = sqlD;
        SQLABC = SqlN * %Len(SqlVar) +16 ;

        exec sql  describe sqlExtract into :SQLDA using all ;

        element = 0;
        dou ( element = SqlD );
          element = element +1 ;
          sqlVar = SQL_VAR(Element);
          sqlData = %alloc(1000);
          sqlInd = %alloc(1);
          SQL_VAR(element) = SqlVar;
        enddo;

        exec sql  declare SQL_Extract cursor for sqlExtract ;

        exec sql  open SQL_Extract;

        dou ( sqlCod = 100 );
          exec sql  fetch SQL_Extract into descriptor :SQLDA ;
          //exec sql  fetch SQL_Extract using descriptor :SQLDA ;
        enddo;

        exec sql  close SQL_Extract;


        *inlr = *on;

      /end-free
      *  -------------------------------------------------------------
      *  -¹ Enquote                                                  -
      *  -------------------------------------------------------------
     p Enquote         b                   export
     d Enquote         pi          1000a   varying
     d  string                     1000a   value varying

     d apos            c                   const( X'7D' )

      /free

         return  apos + %trim(string) + apos ;

      /end-free

     p Enquote         e