Stored Procedure Returning Record Set and column names?
In SQL Server I can create a procedure that would select all rows matching an input parameter, for
example:-
CREATE PROCEDURE [dbo].[GetLinks] @UserId int AS
BEGIN
SELECT LinkId, LinkUrl, LinkDesc FROM Links WHERE UserId = @UserId
END
Then from C# I can call
SqlDataReader rs = linkCmd.ExcuteReader();
int LinkDescriptionOrdinal = rs.GetOrdinal("LinkDesc");
int LinkUrlOrdinal = rs.GetOrdinal("LinkUrl");
while (rs.Read)
{
string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(LinkUrlOrdinal),
rs.GetString(LinkDescriptionOrdinal));
}
or from an ASPX Eval's
<asp:HyperLink runat="server" NavligateUrl='<%# Eval("LinkUrl")%>' Text='<%# Eval("LinkDesc")%>'/>
However I've now got to do the same against an Informix Table, I've been told that the syntax should
be:-
CREATE FUNCTION GetLinks ( userId integer ) RETURNING integer, char(255), char(255)
DEFINE linkId integer; DEFINE linkUrl char(255);
DEFINE linkDesc char(255);
FOREACH cursor1 FOR
SELECT LinkId, LinkUrl, LinkDesc
INTO linkId, linkUrl, linkDesc
FROM Links
WHERE UserId = userId
RETURN linkId, linkUrl, linkDesc WITH RESUME; END FOREACH;
END FUNCTION;
This seems to work, albeit very ugly and verbose, I get the data I need however using this I'm not
able to reference the Column name? All that is available is:-
IfxDataReader rs = linkCmd.ExcuteReader();
while (rs.Read)
{
string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(1), rs.GetString(2));
}
or from an ASPX Eval's
<asp:HyperLink runat="server" NavligateUrl='<%# Eval("Column1")%>' Text='<%# Eval("Column2")%>'/>
Is this the only way to return data from Informix?
It makes the code very unreadable, and if someone changes the order of the call or adds a column in
the middle everything breaks.
I'm hope someone can help, although from what I've seen so far whilst working with Informix I'm not
expecting anything.
On Aug 1, 6:14 pm, mc <m...@community.nospam> wrote:
> In SQL Server I can create a procedure that would select all rows matching an input parameter, for
> example:-
>
> CREATE PROCEDURE [dbo].[GetLinks] @UserId int AS
> BEGIN
> SELECT LinkId, LinkUrl, LinkDesc FROM Links WHERE UserId = @UserId
> END>
> Then from C# I can call
>
> SqlDataReader rs = linkCmd.ExcuteReader();
>
> int LinkDescriptionOrdinal = rs.GetOrdinal("LinkDesc");
> int LinkUrlOrdinal = rs.GetOrdinal("LinkUrl");
>
> while (rs.Read)
> {
> string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(LinkUrlOrdinal),
> rs.GetString(LinkDescriptionOrdinal));
>
> }
>
> or from an ASPX Eval's
>
> <asp:HyperLink runat="server" NavligateUrl='<%# Eval("LinkUrl")%>' Text='<%# Eval("LinkDesc")%>'/>
>
> However I've now got to do the same against an Informix Table, I've been told that the syntax should
> be:-
>
> CREATE FUNCTION GetLinks ( userId integer ) RETURNING integer, char(255), char(255)> DEFINE linkId integer;
> DEFINE linkUrl char(255);
> DEFINE linkDesc char(255);
>
> FOREACH cursor1 FOR
> SELECT LinkId, LinkUrl, LinkDesc
> INTO linkId, linkUrl, linkDesc
> FROM Links
> WHERE UserId = userId> RETURN linkId, linkUrl, linkDesc WITH RESUME;
> END FOREACH;
> END FUNCTION;
>
> This seems to work, albeit very ugly and verbose, I get the data I need however using this I'm not
> able to reference the Column name? All that is available is:-
>
> IfxDataReader rs = linkCmd.ExcuteReader();
> while (rs.Read)
> {
> string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(1), rs.GetString(2));
>
> }
>
> or from an ASPX Eval's
>
> <asp:HyperLink runat="server" NavligateUrl='<%# Eval("Column1")%>' Text='<%# Eval("Column2")%>'/>
>
> Is this the only way to return data from Informix?
Try naming your return parameters and accessing them.
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_1717.htm
Thanks,
That worked a treat!
Regards
Mike
Krishna wrote:
> On Aug 1, 6:14 pm, mc <m...@community.nospam> wrote:
>> In SQL Server I can create a procedure that would select all rows matching an input parameter, for
>> example:-
>>
>> CREATE PROCEDURE [dbo].[GetLinks] @UserId int AS
>> BEGIN
>> SELECT LinkId, LinkUrl, LinkDesc FROM Links WHERE UserId = @UserId
>> END>>
>> Then from C# I can call
>>
>> SqlDataReader rs = linkCmd.ExcuteReader();
>>
>> int LinkDescriptionOrdinal = rs.GetOrdinal("LinkDesc");
>> int LinkUrlOrdinal = rs.GetOrdinal("LinkUrl");
>>
>> while (rs.Read)
>> {
>> string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(LinkUrlOrdinal),
>> rs.GetString(LinkDescriptionOrdinal));
>>
>> }
>>
>> or from an ASPX Eval's
>>
>> <asp:HyperLink runat="server" NavligateUrl='<%# Eval("LinkUrl")%>' Text='<%# Eval("LinkDesc")%>'/>
>>
>> However I've now got to do the same against an Informix Table, I've been told that the syntax should
>> be:-
>>
>> CREATE FUNCTION GetLinks ( userId integer ) RETURNING integer, char(255), char(255)
>> DEFINE linkId integer;>> DEFINE linkUrl char(255);
>> DEFINE linkDesc char(255);
>>
>> FOREACH cursor1 FOR
>> SELECT LinkId, LinkUrl, LinkDesc
>> INTO linkId, linkUrl, linkDesc
>> FROM Links
>> WHERE UserId = userId
>> RETURN linkId, linkUrl, linkDesc WITH RESUME;>> END FOREACH;
>> END FUNCTION;
>>
>> This seems to work, albeit very ugly and verbose, I get the data I need however using this I'm not
>> able to reference the Column name? All that is available is:-
>>
>> IfxDataReader rs = linkCmd.ExcuteReader();
>> while (rs.Read)
>> {
>> string links += string.Format("<a href=\\"{0}\\">{1}</a> ", rs.GetString(1), rs.GetString(2));
>>
>> }
>>
>> or from an ASPX Eval's
>>
>> <asp:HyperLink runat="server" NavligateUrl='<%# Eval("Column1")%>' Text='<%# Eval("Column2")%>'/>
>>
>> Is this the only way to return data from Informix?
>
> Try naming your return parameters and accessing them.
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_1717.htm
>