?
Solved

Firebird with .NET Interface - How to query a row with more than 1 line from a stored procedure

Posted on 2005-04-02
9
Medium Priority
?
868 Views
Last Modified: 2013-12-09
Hi

I need to know how to return multiple Values from a stored procedure and how to query them with the .NET Firebird Interface. I mean not multiple variables, i mean multiple rows in one variable
in SQL server you can do this easily with the procedure Code
SELECT field1 FROM TestTable

but in Firebird you re not allowed to use SELECT without FOR

FOR SELECT field1 INTO :returnvar DO SUSPEND;

Please post the SQL code of the procedure and the code of the .NET programm( C# or VB.NET or C++.NET)

thanks mfg maZe
0
Comment
Question by:maZe84
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:Nick Upson
ID: 13690762
here is a complete SP. (you can do select without a for but it must return 1 row),
to call this just do "select surname from outlook_contacts" as if outlook_contacts was a table

CREATE PROCEDURE OUTLOOK_CONTACTS
returns (SURNAME      CHAR(15))
AS
begin
FOR SELECT contactsurname
  FROM contact inner join companyadd
    on contact.companyadd_u = companyadd.companyadd_u inner join company
    on companyadd.company_u = company.company_u
  ORDER BY contactsurname
  INTO :surname
do
begin
  suspend;
end
end
0
 
LVL 1

Author Comment

by:maZe84
ID: 13691105
Yes, thank you NickUpson that is the SQL Code.

But can you post the .NET code to read SURENAME. If i read it with OutputDirection.Output i can only get 1 row.
In SQL server its possible to return more than one row from a stored procedure. This code is possible in MSSQL

CREATE Procedure ProductCategoryList
AS
SELECT
    CategoryID,
    CategoryName
FROM
    Categories
ORDER BY
    CategoryName ASC
GO
(out of IBuySpyPortal)

And you can read it with SqlDataReader .Read()
But with .NET firebird Provider its not possible that way, or did i make a mistake?
Please post the .NET code to read this.

mfg thanks maZe
0
 
LVL 19

Expert Comment

by:Nick Upson
ID: 13691683
I don't know .net but I do know firebird, the SP I posted above will return more than one row provided the data
is in the table.  What you have posted is more commonly known as a view, which can be done in firebird as
a view like this:

CREATE view ProductCategoryList (    CategoryID,    CategoryName)
AS
SELECT
    CategoryID,
    CategoryName
FROM
    Categories
ORDER BY
    CategoryName ASC
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Author Comment

by:maZe84
ID: 13691824
k i ask in C# topic again thanks

mfg maZe
0
 
LVL 1

Author Comment

by:maZe84
ID: 13701525
I have the correct answer now!

FbCommand cmd2 = new FbCommand();
 cmd2.Connection = con;
 cmd2.CommandType = CommandType.StoredProcedure;
 cmd2.CommandText = "SELECT * FROM \"SP_COUNTTO10\";";

con.Open();
FbDataReader reader = cmd2.ExecuteReader();
con.Close();

// This works
while (reader.Read())
{
      Console.WriteLine(reader.GetValue(0).ToString());
}
reader.Close();


But thanks
mfg maZe
0
 
LVL 19

Expert Comment

by:Nick Upson
ID: 13704669
similiar questions are common, I'd like it to be searchable as both the firebird and .net information is there
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 13736226
Question answered by asker or dialog deemed valuable.
Closed, 150 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question