Column displays blank or NULL as data after update

[furei1976]

Hi Everyone,

I need your expertise again.

Here is the problem, Column displays blank or NULL as data after update. the raw data comes from a fixed width formatted text file. the current code for this by the previous programmer is invoking the trim function below:
ls_col054 = trim(mid(ls_filestr,999,11)) //Salesman ID

This works okay most of the time in all columns. I was asked to investigate the cause of the null data, because it actually has data. I found out that the data on the text contains a different character, it shows up as a space or blank when you look at it normally. But when you hit the backspace key, it will backspace 2 times. So I am thinking it might be a special character a normal text editor does not see or even Powerbuilder. I have used Ultra Edit, this has a feature to convert the current text file to switch to HEX mode. After toggling to HEX mode it showed up the data with a "." at the beginning.

Are there any built-in PB function that can fix this and have Powerbuilder save the data as it should be?

Any help will be greatly appreciated.

thanks in advance,

furei

 

[furei1976]

I found an MS SQL function that might fix the problem (

http://sqltrim.codeplex.com)

but I don't know how to invoke a user-defined MS SQL function in PB. I tested the function in MS Query Analyzer, it works but this needs to be tested while uploading the test files. Can someone also teach me on how to call a user-defined MS SQL function?

thanks,
furei

 

[mbalent]

If your SQL function is called uf_whatever you can call it in a normal sql statement similar to:

SELECT part_no, part_code, uf_whatever(part_text) as part_desc FROM part_master where ....

Matt


"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[furei1976]

thanks Matt, for the reply.
I tried that before:
SELECT dbo.trimmer ls_trim) from dbo.trimmer INTO :ls_col054 USING SQLCA;

it gave me a syntax error.

 

[mbalent]

Creating embedded SQL for just a db function call can be problematic. To get this recognized by PB you need to select from an actual table in the database and limit the results to a single row.

You may be better off parsing your string in PB and skipping the character which gives you the problem (something like IF AscA (badchar) = 007 THEN...)

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[furei1976]

thanks, Matt. I have tried it as "IF AscA (badchar) = 009..." but still no go. that is what Ultra Edit displayed in Hex Mode of the text file, it seems to be a horizontal tab in a fixed width formatted text file. It seems like PB does not read the rest of the data after that horizontal tab, all are blank even I change the original position of "mid(ls_filestr,999,11)".

 

[furei1976]

PB reads it as null. it returns 0 when I did AscA (badchar). unless you modify the text file before upload manually to get rid of that horizontal tab non-printing character. If manually removed, it will finally display the rest of the data. this is really weird.