IDS Forum

Re: Stored Procedure Returning Record Set and colu

Posted By: Norton, Graeme
Date: Saturday, 1 August 2009, at 10:24 a.m.

In Response To: Stored Procedure Returning Record Set and column n (MIKE CADDY)

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.
--------------------------------------------------------------------------

Messages In This Thread

• Stored Procedure Returning Record Set and column n
  MIKE CADDY -- Saturday, 1 August 2009, at 9:21 a.m.
  • Re: Stored Procedure Returning Record Set and colu
    Norton, Graeme -- Saturday, 1 August 2009, at 10:24 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.