Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TIBQuery input parameter on FireBird 'selectable stored procedure'

Posted on 2008-06-13
5
Medium Priority
?
3,203 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
[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
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
rfwoolf earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
What we learned in Webroot's webinar on multi-vector protection.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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