Re: Use of the SQLDA - Example for SQL DESCRIBE TABLE

Yes, I had looked at that and maybe I dismissed it too quickly. The trick would be to grab the correct information so that I can determine, correctly, all the attributes of the column I want to duplicate. This syntax looks close:

SELECT DATA_TYPE, LENGTH, NUMERIC_SCALE, IS_NULLABLE, HAS_DEFAULT
FROM syscolumns WHERE table_name = #{table_name} and column_name = #{column_name}

I'd have to evaluate the data_type to figure out which of the remaining variables would apply. Identity columns are particularly a challenge. Once that was done, I'd still have to construct the alter table statement to add the column. Tricky, but not impossible.

Dennis had suggested the QUSLFLD API, which I found some examples of and good documentation on in the RPG: APIs Redbook. I guess it comes down to if I am going to do a stored procedure in RPG, I might as well use the API although embedded SQL for the syscolumns retrieval would also be OK and would contain less code.

If I could create a stored procedure that was only in SQL, that would be more portable since I could ship a CREATE PROCEDURE statement that would generate the procedure without needing to also restore RPG source and compile or need a save file with the RPG object in it. So SQL would be cleaner. I just can't figure out how to do everything in SQL so I may just have to bite the bullet and write it all in RPG.

Thanks

Pete



Luis Rodriguez wrote:

Pete,

Wouldn't the use of the SYSCOLUMNS view work for you? IIRC. it should
have all the info you need...

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries



On Thu, Dec 17, 2009 at 11:14 AM, Pete Helgren <Pete@xxxxxxxxxx> wrote:

I am looking for an example of using an SQLDA with a DESCRIBE TABLE
function in an RPG program. I found a few general examples of using an
SQLDA in RPG but nothing specific that would help me unpack the
complexities of the SQLDA. The goal is to eventually package this into
a stored procedure that will mimic the RENAME COLUMN function that is
missing in DB2 for i.

Basically, I want to retrieve the column attribute information about a
specific column in a table and create a duplicate of that column with a
new name and then drop the old column name. I can handle the column
drop and copy of the data but the creation of an exact duplicate of the
column is the difficult part. The suggestion from Midrange-L was to use
an SQL DESCRIBE TABLE function and retrieve the info into an SQLDA.
Having never done that, I need an example to help me along.

I would love to do this only with SQL and without a stored procedure and
I came up with a hack to make it work but there are some potential
issues with the approach I have taken and so a stored procedure is
probably the best route to go.

Anyone have an example or a code snippet that would illuminate the basic
steps?

Thanks

Pete

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.