Stored Procedure Returning Record Set and column n
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.
You can name the returning variables like:
RETURNING integer AS LinkId,
Varchar(255) AS LinkUrl,
VARCHAR(255) AS LinkDesc
Not sure if C# will recognise these but web services and SSRS do
--------------------------
Cheers Graeme
Graeme Norton | Senior Solution Architect | Channels Technology - Technology |
ITV plc
Television Centre, Kirkstall Road | Leeds | LS3 1JS | Tel: 0113 222 7157 |
Mob: 07764 256655 | Graeme.Norton@ITV.COM
ITV plc Head Office Tel +44 (0) 20 7156 6000 http://www.itv.com/
Please consider the environment before printing this email
----- Original Message -----
From: ids-bounces@iiug.org <ids-bounces@iiug.org>
To: ids@iiug.org <ids@iiug.org>
Sent: Sat Aug 01 14:21:01 2009
Subject: Stored Procedure Returning Record Set and column n [16551]
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.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
--------------------------------------------------------------------------
ITV Broadcasting Limited (Registration No. 955957) (âITVâ) is incorporated
in England and Wales with its registered office at 200 Grays Inn Road, London
WC1X 8HF. Please visit the official ITV website at http://www.itv.com/ for the
latest company news.
The contents of this email and any attachments are confidential, may be
privileged, may be subject to copyright and are intended solely for the use of
the individual to whom they are addressed. If you have received this email and
you are not the intended recipient please notify mailto:postmaster@itv.com and
delete this email and you are notified that disclosing, copying, distributing
or taking any action in reliance on the contents of this email are strictly
prohibited.
Although ITV routinely screens for viruses, recipients should scan this email
and any attachments for viruses. ITV makes no representation or warranty that
this email or any of its attachments is free of viruses or defects and does
not accept any responsibility for any damage caused by any virus or defect
transmitted by this email. ITV reserves the right to monitor all e mails and
the systems upon which such e mails are stored or circulated.
Any views or opinions presented in this email are solely those of the author
and do not necessarily represent those of ITV.
Thank You.
--------------------------------------------------------------------------