Tuesday, May 23, 2023

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.

4 comments:

  1. Do you get the *POINTER thing in QMQRY? or Embedded SQL? Thanks.

    ReplyDelete
    Replies
    1. The results returned by QMQRY for embedded SQL will not return pointer.
      I have only see the pointer when using STRSQL.

      Delete
  2. It's archaic to still be using STRSQL. The history of your past SQL statements is enough reason to switch. I have a .sql file for every table I've queried and it's made my life easier to use a previously saved file when I need to again.

    ReplyDelete
    Replies
    1. Another excellent reason why using Run SQL Scripts is better than STRSQL.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.