Solved

TIBQuery input parameter on FireBird 'selectable stored procedure'

Posted on 2008-06-13
5
3,010 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
ID: 21783440
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
ID: 21783447
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
ID: 21784725
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
ID: 21793734
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
ID: 31467601
See my last comment ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

20 Experts available now in Live!

Get 1:1 Help Now