RE: Simple question - can't find answer
Additional-
I forgot I was writing SPL. Assume that all of those lines end
with a ";"...
--EEM
> -----Original Message-----
> From: Everett Mills [mailto:eemills@nationalbeef.com]
> Sent: Friday, July 21, 2006 4:54 PM
> To: informix-list@iiug.org
> Subject: RE: Simple question - can't find answer
>
> Well...
> I suppose you could return a big*ss char variable and parse it on
> the other end:
>
> CREATE FUNCTION test()
> DEFINE
> Ret_val CHAR(3000),
> My_rec RECORD LIKE mytable.*>
>
> LET ret_val = ""
>
> FOREACH my_select_statement
> IF ( LENGTH(ret_val > 2900 ) THEN
> RETURN "Error: too many rows."
> END IF
>
> LET ret_val = ret_val + my_table.var1 + "|"
> LET ret_val = ret_val + my_table.var2 + "|"
> .....
> LET ret_val = ret_val + "EndLine" + "|"
>
> ####Use EndLine to mark the end of each row####
> END FOREACH
>
> RETURN ret_val
>
> END FUNCTION
>
>
> --EEM
>
> > -----Original Message-----
> > From: zackary.evans@gmail.com [mailto:zackary.evans@gmail.com]
> > Sent: Friday, July 21, 2006 3:02 PM
> > To: informix-list@iiug.org
> > Subject: Re: Simple question - can't find answer
> >
> > The result of the proc is actually going to be consumed by SQL
Server
> > 2000 (via linked server).
> >
> > I certainly could use a work table, but I was trying to avoid that.
I
> > can't have any on disk tables. The Informix (IDS 10) DB is read
only.
> >
> > Everett Mills wrote:
> > > Zachary-
> > > Why don't you select your data into a holding table, and have
> > > your procedure return a pointer to it, you would need another
table
> with
> > > just a serial column to make it happen (or if your on 9.X, 10.X
you
> > > could use sequences instead):
> > >
> > > CREATE FUNCTION test()
> > > DEFINE
> > > Ser_no INTEGER,
> > > My_rec RECORD LIKE mytable.*> > >
> > >
> > > INSERT INTO serial_table VALUES (0)> > >
> > > SELECT dbinfo('sqlca.sqlerrd1') INTO ser_no
> > > FROM serial_table
> > >
> > > DELETE FROM serial_table WHERE serial_no = ser_no> > > ####This keeps the serial table clean
> > >
> > > INSERT INTO my_output ( SELECT ser_no, * FROM mytable
> > > WHERE my_where_clause )> > >
> > > RETURN ser_no
> > >
> > > END FUNCTION
> > >
> > > Outside the function you can do something like this (you didn't
say
> what
> > > you were returning to, so I did it as a 4gl):
> > >
> > > Ser_no = test()
> > >
> > > DECLARE foo_curs CURSOR FOR
> > > SELECT * FROM my_output
> > > WHERE serial_key = ser_no> > >
> > > FOREACH foo_curs INTO whatever
> > > ...
> > > END FOREACH
> > >
> > > DELETE FROM my_output WHERE serial_key = ser_no> > >
> > >
> > > I didn't test this, but it should give you some ideas. I used
> something
> > > like it in a 4gl that required a function to return an indefinite
> number
> > > of values.
> > >
> > > --EEM
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: zackary.evans@gmail.com [mailto:zackary.evans@gmail.com]
> > > > Sent: Friday, July 21, 2006 1:45 PM
> > > > To: informix-list@iiug.org
> > > > Subject: Simple question - can't find answer
> > > >
> > > > How do I return a set of data from a stored procedure? Basically
> all I
> > > > want to do is return the results of a select query which will
> contain
> > > > multiple rows.
> > > >
> > > > This does not work in informix, it gives error #659. I know it
> > > > basically means i have to select the result into another table,
> but
> > > > then how does that actually get returned?
> > > >
> > > > CREATE PROCEDURE test()> > > >
> > > > SELECT *
> > > > FROM table> > > >
> > > > END PROCEDURE
> > > >
> > > > _______________________________________________
> > > > Informix-list mailing list
> > > > Informix-list@iiug.org
> > > > http://www.iiug.org/mailman/listinfo/informix-list
> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list