problem with sql/print instruction [U9.4]

Author: spanish_uniface@hotmail.es (uniface8)

Hi, i have a problem with sql/print instruction in U9.4; i launch the instruction and it works (returns the right values), but $result have somethig like

00001
·!·!·!·!·!·!
998

after, with U8.4 $result had

00001
======
998

we use Iseries and DB2. Isn´t there any place where i can define the separation caracter in the result of sql/print instruction?

Regards,Rafa.

8 Comments

  1. Hi Rafa,

    AFAIK, the Header and the underline in SQL/print comes from the database itself;
    but perhaps it is an effect of the characterset conversion how the underline looks like.

    In general the $result from sql/print consists of:

    the first "line" (read as characters up to the first %%^) is the header,
    the next is the underline and
    from line 3 on there are your data.

    SUccess, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Thanks Uli,

    Our real problem is that we convert $result in a list ( in eg: my_list="00001·;·!·!·!·!·!·!·;998  ·;") and then eliminate the two first elements (delitem my_list,1), with the new format when we eliminate the second element we obtend an empty list, first delitem instruction eliminate "00001·;" and the second "·!·!·!·!·!·!·;998  ·;" 

    So i think that we have to change our procs ^_^

    Regards,Rafa.


    Author: uniface8 (spanish_uniface@hotmail.es)
  3. Hi Rafa,

    now we come to the REAL issue:

    the underline Character you get is a gold-! which masks the following gold-;

    So do not only replace %%^ with gold-;
    but also gold-! with an equal sign.

    But perhaps you can set in DB2 the underline character to equal signs
    or covert charactersets

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. Yes, i changed the new character separator to equal sign and it works, but it´s extrange, we don´t change the database so, i think that it´s for the new driver.

    Ty,Uli.

    Regards,Rafa.


    Author: uniface8 (spanish_uniface@hotmail.es)
  5. Quoting Uli:

    "now we come to the REAL issue:"

    almost...

    I think the real issue is using sql/print and therefore creating database(driver) dependency.

    Also keep in mind that when your SQL statement delivers large results, $result will flow over and your SQL will probably return an negative status.

    The better way of doing this would be to create a service to do whatever your sql/print stement is doing and have that service return the list you want.

    Database(driver) dependency gone...

    Of course using the 'sql' or 'sql/print' statement can be very convenient, but the maintenance afterwards due to database(driver) dependency is obviously your tradeoff here...

    Best Regards,

    Arjen


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  6. Arjen is right, try to keep sql, sql/print and read where to a minimum.

    I really can't say why your output changed, I think it must have something to do with character set conversions.

    What i do when i need to put sql output in a list:

    sql/print vSQL, pSqlPath
    pResult = $result

    ; Remove first two lines
    pResult = pResult[$scan(pResult, "%%^")+1]
    pResult = pResult[$scan(pResult, "%%^")+1]

    ; Remove spaces (assuming there are no spaces inside you data!)
    pResult = $replace(pResult, 1, " ", "", -1)

    ; Make a Uniface List
    pResult = $replace(pResult, 1, ",", "·!·;", -1)
    pResult = $replace(pResult, 1, "%%^", "·;", -1)
     


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  7. I know that is not a good idea use sql/print or sql all the time, but sometimes are useful (in e.g.:create cursors, make joins,grant,...) there are a lot of complicated sql instructions.

    But i think that all these instructions (or most of them) are in one service is not a problem to maintenance.


    Author: uniface8 (spanish_uniface@hotmail.es)
  8. That's right. It is good practise to put all your database DEPENDED stuff in a separate component,
    so you know what to change when you switch databases.


    Author: Theo Neeskens (tneeskens@itblockz.nl)