Solved

TIBQuery input parameter on FireBird 'selectable stored procedure'

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
send click   without cursor 6 371
Listen keywords are been pressed 3 334
copy one tchart to another 1 528
Using idhttp to login to instagram 2 89
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This problem is more common than not and I will show you some things to check to solve this problem.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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