Solved

TIBQuery input parameter on FireBird 'selectable stored procedure'

Posted on 2008-06-13
5
2,969 Views
Last Modified: 2010-04-21
I wrote a FireBird selectable query looking like this :
create procedure MYPROC (INPARM VARCHAR(31))
                            output (OUTPARM VARCHAR(31))
as
for select ...... into OUTPARM
do suspend
end;
When I run it with ISQL like this : select * from MYPROC ('somevalue'); it works...
Now I want to "call" it in a Delphi program with Interbase components
The code look like this :
.../...
 IBQuery := TIBQuery.Create(Application);
 IBQuery.Database := .../...;
 IBQuery.SQL.Clear;
 IBQuery.SQL.Add('select * from MYPROC');
 IBQuery.Params.Clear;
 IBQuery.Params.CreateParam(ftString,'INPARM',ptInput);
 IBQuery.Params.ParamByName('INPARM').AsString := 'somevalue';
 IBQuery.Prepare;
 IBQuery.Open;
When running this, it crashed on the Prepare : dynamic SQL Error, input parameter mismatch for procedure MYPROC !

0
Comment
Question by:LeTay
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
rfwoolf earned 500 total points
Comment Utility
I believe the parameter only exists in dephi, it then gets interpreted to generate the SQL and that SQL gets passed to the actual database - in other words PARAMETERS aren't passed to the database - your Firebird database doesn't know anthing about the parameters you create in delphi - all it's interested in is the SQL itself.
Technically speaking, SQL has different implementations in different databases. For example in Oracle you can have CASE statements and you can ask it to return "pages" of records, whereas in ACCESS for example you can't break up the returned recordset into pages using SQL, or how about the BDE which uses LOCALSQL and doesn't support CASE statements.
In other words, some database implementations may support fancy ways of passing parameterised information with your SQL - I don't think Firebird is one of them, and even if it is, the way you're doing it doesn't communicate anything about parameters to Firebird.
0
 
LVL 13

Expert Comment

by:rfwoolf
Comment Utility
By the way, in Delphi datasets you usually pass parameters by placing a colon ":" before the parameter, and then you insert the parameter as you have been doing.
Here's an example
 IBQuery := TIBQuery.Create(Application);

 IBQuery.Database := .../...;

 IBQuery.SQL.Clear;

 IBQuery.SQL.Add('select * from MYPROC WHERE Fieldname = :MyParameter');

 IBQuery.Params.Clear;

 IBQuery.Params.CreateParam(ftString,'INPARM',ptInput);

 IBQuery.Params.ParamByName('INPARM').AsString := 'HelloWorld';

 IBQuery.Prepare;

 IBQuery.Open;

//The actual SQL that gets passed is:

//select * from MYPROC WHERE Fieldname = HelloWorld;

Open in new window

0
 

Author Comment

by:LeTay
Comment Utility
I will try this at the office on monday, but something like :
select * from MYPROC where FieldName = something reacts I think differently
The selectable procedure MYPROC will run and at the output, filter base on the where condition
Here, MYPROC has some real parameters as any stored procedure allow and the parameter is used internally in MYPROC.
In the beginning, I tried to used a TIBStoredProc instead of a TIBQuery, as I do when running a stored procedure that returns a dataset with BDE or ADO components (accessing MS/SQL), but I got an error message explaining I had to use a TQuery ...
0
 

Author Comment

by:LeTay
Comment Utility
I found the solution by myself, but I will give the points to rfwoolf who helped me to guess it.
In fact, I had to code the select like this in the IBQuery.SQL :
select * from MYPROC (:INPARM);
then define INPARM as parameter and give it a value
Firebird stopped crying and the query worked fine
0
 

Author Closing Comment

by:LeTay
Comment Utility
See my last comment ...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now