?
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
?
865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
9 Comments
 
LVL 19

Expert Comment

by:NickUpson
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:NickUpson
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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:NickUpson
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

719 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