Informix SPL & lots of data through a parameter

[llevity]

I'm not very familiar with informix SPL, but I need to do a procedure that does what seems to be fairly simple.

I am going to pass as a parameter a long, delimited string of text. I need the procedure to take this, parse it, and insert each item into a table. So the first item it comes to, it inserts into field a, the next, field b, the next, field c. Once it gets to C, it starts a new r with the next three items it comes to.

Anyone have any tips on how to start?

 

[olded]

Llevity:

First, let me tell you about the string handling capabilities of Informix SPL; It sucks!

Unfortunately you can NOT index a string with a variable:

if string = "|"
then
..

although you can do this:

if string[1] = "|"
then
..

it doesn't do you much good.

You're going to have to do something like this:

CREATE PROCEDURE parse_string(str CHAR(80))

DEFINE xstr CHAR(30);
DEFINE delim CHAR(1);
DEFINE dpick CHAR(1);
DEFINE col_value CHAR(1);
DEFINE ilen smallint;
DEFINE index smallint;
DEFINE cnter smallint;
DEFINE col1 smallint;
DEFINE col2 smallint;
DEFINE col3 smallint;

set debug file to "/tmp/trace.data";

let col1 = 0;
let col2 = 0;
let col3 = 0;
let delim="|";
let ilen=length(str);

create temp table b (
newstr char(80)
) with no log;
insert into b values (str);

let cnter = 1;
for index = 1 to ilen
select substr(newstr,index,1) INTO dpick FROM b;
if dpick = delim
then
trace on;
select substr(newstr,cnter,(index-cnter)) INTO col_value FROM b;
trace off;
let cnter=index+1;
end if

end for
drop table b;
END PROCEDURE;

1) pass the string you want to parse to the SP.
2) insert the string into a temp table.
3) using the informix substring function, substr, check each character for the substring. substr is defined as:

substr(input_string, start_position, length)

if you don't give it a length, it goes to the end of the string.
4) if a delimiter is found, select the string from cnter with the length equal index - cnter
5) bump the cnter


All I've done is the parsing; I'll leave inserting the parsed data into your table to you.

I've turned the trace on and off, so if you look at the file you'll see that

execute procedure parse_string("12345|6789|0ABC|"

returns:
"12345"
"6789"
"0ABC"

Let me know if you need anything else.

Regards,


Ed