Long names and Data structures based on SQL Table problem

[Filip Drzewiecki]

Hello,

I have an SQL table with long names. Based on below article I've tried to declare Data Structure based on this table so I can select all columns to my Data stucture in service program. Problem is that
I always get Error "XXX is not defined or no usable" during srvpgm compilation where XXX is the name of DS which is based on another DS with extname and alias. Even if I use main DS(the one with extname) error is the same. I thought that adding alias will fix problem with long names. When I declared the same DS but I've specified all fields explicitly, compilation was successful.

http://www.rpgpgm.com/2018/05/how-to-sql-tables-long-names-in-rpg.html

Dcl-Ds myTable extname('tableName':*ALL) qualified alias;
End-Ds;

Dcl-ds myTable_X likeds myTable);

Exec SQL
select * into : myTable_X from tableName where ID= :UserId;

This on is failing. SQL always return one row of data. After I change DS and declare all fields (70...) myself, it starts to compile. I really don't know why. Any idea? Maybe this can't be done with srvpgm? I handle nulls in table by using ALWNULL(*INPUTONLY).

Best Regards
Filip

[Vectorspace]

Your myTable_X definition is missing an opening bracket, but I assume that's not in your actual program.

What is the value of SQLSTT and SQLCOD after the SQL statement fails? They will tell you exactly why it failed.

Are there any unusual data types among the columns in your table?

Does the compile listing list any warnings about the myTable DS?


Does your SQL table only have long names? Or does it have short names too?
long name only column definition:
myLongColumnName char(10),
long and short name column definition:
myLongColumnName for column ABCOLM char(10),

I have only ever done the latter. And when I declare a DS based on the table, it uses the short name:

  Dcl-Ds myFileDS  extname('myFile') qualified end-ds;
  Dcl-Ds myFileDS2 likeDS(myFileDS);

  myFileDS.ABCOLM = 'abc';
  myFileDS2.ABCOLM = 'def';

I do not know what happens to a table with only long column names, but my guess would be it auto-generates short column names based on the long name. So myLongColumnName may actually be called MYLONGC001 or something as far as the program is concerned. The compile listing should tell you that.

I don't know what the alias keyword does, I have never used it. My example above was all I ever needed to do to declare a qualified DS based on a file.

[Filip Drzewiecki]

Have You read link which I provided?
What I see there is that when they have created a new table without short names, after they add alias program can see and use actual long names. My table does have short names but anyway I can't select * into this data structure. Unfortunately because this is sqlrpgi service program, I only got message that viariable myTable_X not defined or not usable.

I have DS with extname in copy file which is of course declated in srvpgm.

[Vectorspace]

Apologies, I missed the link.

I really hate that error as all it tells you is that something is wrong with the DS, it doesn't tell you what.

SQLRPGLE program/module compilation is a two stage process - first the SQL precompile happens (adds declarations for SQL variables like SQLSTT, validates every embedded SQL statement, and replaces every embedded SQL statement with procedure calls and assignments), and then the regular RPGLE compile happens.

I don't know, but my best guess would be that maybe the SQL precompiler does not understand the Alias keyword. I have seen cases before where the SQL precompiler cannot understand some kinds of data structure setups.
If that is the case, that would mean the SQL precompile does not have a definition for that DS, which would mean that it can't parse the embedded SQL statement as it doesn't know what the DS subfields are and if they match the table columns returned by the SQL.

Maybe someone more knowledgeable than me can confirm

[wegrace]

I use alias for my Data structure data types as I prefer the longer more descriptive names.

Try changing extname('tableName':*ALL) to extname('TABLENAME')

Note the upperCase of the table name.

Walt