Solved

TIBQuery input parameter on FireBird 'selectable stored procedure'

Posted on 2008-06-13
5
3,083 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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