Why do my SQL results show pointer?

At least one a month I get a question that is something like this:

My SQL results do not look like yours, mine have the word pointer in them. Why?

This gives you away as someone who is still using the old Start SQL command, STRSQL, interface in your 5250 emulator session, rather than Access Client Solutions' Run SQL Scripts. I am pretty sure that the vast majority of us are using ACS for our 5250 "green screen" sessions, so why are you still using STRSQL when Run SQL Scripts is there too?

Pointer is displayed in STRSQL results when it cannot interpret the data from a particular data type. In my experience these are:

• BLOB:  Binary large object type
• CLOB:  Character large object type
• DBCLOB:  Double byte character large object type

A good example is the path name column, PATH_NAME, returned by the IFS.OBJECT_STATISTICS table function. This is a DBCLOB type column.

I can use the same statement in Run SQL Scripts and STRSQL:

SELECT PATH_NAME FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/Simon','NO'))

Run SQL Scripts returns:

PATH_NAME ----------------------- /home/Simon /home/Simon/test.csv

The path names are clearly shown.

On the other hand STRSQL returns:

PATH_NAME ---------- *POINTER *POINTER

To translate the pointer value I would need to cast the path name:

SELECT CAST(PATH_NAME AS VARCHAR(500)) FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/Simon','NO'))

Why did I cast the path name to Variable Character type, VARCHAR, rather than just character, CHAR?

Character is fixed width, therefore, in this example the width of the character column dsiplayed in the results would be 500 characters. With a variable character column the width shown is the length of the longest value contained within.

Not the "end of the world", but an extra thing to add to my statement.

Another example of how STRSQL makes it more troublesome is when I use the IFS_READ table functions. IFS_READ returns the data from the IFS file in a CLOB type, and IFS_READ_BINARY in a BLOB type.

This simple Common Table Expression, CTE, show how I can get both the CLOB and BLOB types from my IFS files:

01 WITH T0 AS 02 (SELECT LINE FROM TABLE(QSYS2.IFS_READ('/home/Simon/test.csv')) 03 LIMIT 1), 04 T1 AS 05 (SELECT LINE FROM TABLE(QSYS2.IFS_READ_BINARY('/home/Simon/test.csv')) 06 LIMIT 1) 07 SELECT T0.LINE AS "CLOB",T1.LINE AS "BLOB" 08 FROM T0,T1

Lines 1 – 3: In the first part of the CTE I am returning the contents of the first row of the IFS file by IFS_READ that returns the data as CLOB.

Lines 4 – 6: In the second part I am returning the contents of the first tow of the IFS file using IFS_READ_BINARY that returns the data as BLOB.

Lines 7 and 8: The Select statement displays the results, in this case the results from IFS_READ and IFS_READ_BINARY as two columns in one set of results.

With Run SQL Scripts the results look like:

CLOB BLOB -------- ---------------- FLD001 464C443030310...

With STRSQL I am returned:

CLOB BLOB -------- ---------------- *POINTER *POINTER

In STRSQL I can cast the columns to VARCHAR, and I will see the results. But why would I want to do that when I can display it without anything extra in Run SQL Scripts?

Still not convinced... then save the results that are displayed in STRSQL to a file to share with others. With a lot of extra work I can. But why would I want to do that when with Run SQL Scripts I can just:

It has been time to STOP using STRSQL for a long time. This is just another reason why you need to abandon the old "AS400" of thinking and move to the newer "IBM i" way of doing things.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.