problem with sql/print instruction [U9.4]

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

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.

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

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.

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

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)
 

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.

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.