Execute Firebird stored procedure with Lazarus IDE
lazarus / 16-Databases

----------------------------------------------------------------------
EdmundasLT · 2014-09-23 14:36

Hi, I managed to find how to create a stored procedure, now i want it
to read data from edit boxes and pass that data to firebird database.

My code compiles without error, but in application if i press  button
to execute Stored procedure i get error:
(http://Photo of the Error I get)

I tried executing stored procedure in FlameRobin and it worked
correctly, so now I just need some guidence on what I did wrong and
how to fix my mistakes.

Heres my button code:
Code: [Select]procedure TForm2.Button4Click(Sender: TObject);
var

   vardas: String;
   pavarde: String;
   ak: String;
   miestas: String;
   telefonas: String;
   elpastas: String;
begin
   vardas:=Edit1.Text;
   pavarde:=Edit2.Text;
   ak:=Edit4.Text ;
   miestas:=ComboBox1.Text;
   telefonas:=Edit5.Text;
   elpastas:=Edit6.Text;


   SQLQuery1.SQL.Add ('INSERT INTO KLINSERT');
   SQLQuery1.Params.ParamValues['vardas']:=vardas;
   SQLQuery1.Params.ParamValues['pavarde']:=pavarde;
   SQLQuery1.Params.ParamValues['ak']:=ak;
   SQLQuery1.Params.ParamValues['miestas']:=miestas;
   SQLQuery1.Params.ParamValues['telefonas']:=telefonas;
   SQLQuery1.Params.ParamValues['elpastas']:=elpastas;

  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
end;       

And here is stored procedure code:
Code: [Select]SET TERM ^ ;
ALTER PROCEDURE KLINSERT (
    CLIENTID Integer,
    VARDAS Varchar(255),
    PAVARDE Varchar(255),
    AK Varchar(255),
    MIESTAS Varchar(255),
    TELEFONAS Varchar(255),
    ELPASTAS Varchar(255) )
AS
begin
    INSERT INTO KLIENTAI (CLIENTID, VARDAS, PAVARDE, AK, MIESTAS,
TELEFONAS, ELPASTAS)
    VALUES (:CLIENTID, :VARDAS, :PAVARDE, :AK, :MIESTAS, :TELEFONAS,
:ELPASTAS);
end^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE KLINSERT TO  TEST;

Thanks for help in advance, I'm stuck really bad here

----------------------------------------------------------------------
BigChimp · 2014-09-23 14:45

Note: typing this without compiler etc, there may be errors.

I'm having real trouble believing running just
Code: [Select]INSERT INTO KLINSERT
works for you in Flamerobin.
The KLINSERT stored procedure you posted requires multiple parameters:
CLIENTID, VARDAS, etc.

Doesn't FLamerobin give you an error message?

Please see in the code;
Code: [Select]procedure TForm2.Button4Click(Sender: TObject);
var

   vardas: String;
   pavarde: String;
   ak: String;
   miestas: String;
   telefonas: String;
   elpastas: String;
begin
   vardas:=Edit1.Text;
   pavarde:=Edit2.Text;
   ak:=Edit4.Text ;
   miestas:=ComboBox1.Text;
   telefonas:=Edit5.Text;
   elpastas:=Edit6.Text;

   //Don't do this - you don't know what is in SQLQuery1.SQL
already!?!?
   //SQLQuery1.SQL.Add ('INSERT INTO KLINSERT');
  // instead do something like this
  //SQLQuery1.SQL.Text:='INSERT INTO KLINSERT';
   //However, are you SURE this works in Flamerobin?

   // The SQL you gave has no parameters - you can recognize
parameters as they start with : eg :vardas
   // so the lines below do nothing... I would suggest something like
  // pass the parameters in the same order KLINSERT expects them
  SQLQuery1.SQL.Text:='EXECUTE KLINSERT
(:CLIENTID,:VARDAS,:PAVARDE,:AK,:MIESTAS,:TELEFONAS,:ELPASTAS);';
//you're missing CLIENTID here... you should add it...
   SQLQuery1.Params.ParamValues['vardas']:=vardas;
   SQLQuery1.Params.ParamValues['pavarde']:=pavarde;
   SQLQuery1.Params.ParamValues['ak']:=ak;
   SQLQuery1.Params.ParamValues['miestas']:=miestas;
   SQLQuery1.Params.ParamValues['telefonas']:=telefonas;
   SQLQuery1.Params.ParamValues['elpastas']:=elpastas;

  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
end;

----------------------------------------------------------------------
Mike.Cornflake · 2014-09-23 15:24

In addition to everything noted by @BigChimp, I'm not convinced the
following line is correct: (unless you've already declared parameters
in the IDE).

Code: [Select]SQLQuery1.Params.ParamValues['vardas']:=vardas;
Looking in code ParamValues[] calls ParamByName, and in dsparams.inc I
note the following code
Code: [Select]function TParams.ParamByName(const Value: string):
TParam;
begin
  Result:=FindParam(Value);
  If (Result=Nil) then
    DatabaseErrorFmt(SParameterNotFound,[Value],Dataset);
end; 
That error is consistent with the one you're reporting.

Looks to me like ParamValues will only work if the parameter is
already defined.

Try adding, errr....  Look, I've never used parameterised queries, how
on earth do you create them?  Only call I can find in source code
is...

Code: [Select]SQLQuery1.Params.CreateParam(FldType: TFieldType; const
ParamName: string; ParamType: TParamType);
If this is the issue - easiest solution is probably to define the
Parameters using the Object Inspector, or others here may advise...

(That's two posts today I've commented without a clue as to the
solution - what is wrong with me??)

----------------------------------------------------------------------
BigChimp · 2014-09-23 15:27

@Mike: I suspect the params are created on the fly by the sql parser
out of the sql (unless parsesql is false) so they should exist.
Haven't looked into that for a while though, so who knows.

Regardless, simply using this should work (air code, mistakes may
arise etc)
Code: [Select]SQLQuery1.Params.ParamByName('vardas').AsString:=vardas;

----------------------------------------------------------------------
Mike.Cornflake · 2014-09-23 15:29

Quote from: BigChimp on September 23, 2014, 05:27:53 pm@Mike: I
suspect the params are created on the fly by the sql parser out of the
sql (unless parsesql is false) so they should exist.
Haven't looked into that for a while though, so who knows.

Regardless, simply using this should work (air code, mistakes may
arise etc)
Code: [Select]SQLQuery1.Params.ParamByName('vardas').AsString:=vardas;

Ahh, which for your code would work (you have the parameters in your
SQL), but for the original won't.   However if those parameters aren't
created (ie parsesql := false), then I still think you'll get an
exception in ParamByName...

----------------------------------------------------------------------
BigChimp · 2014-09-23 15:50

Quote from: Mike.Cornflake on September 23, 2014, 05:29:32 pmAhh,
which for your code would work (you have the parameters in your SQL),
but for the original won't.  
Well, that was more or less my point. Even if you create the params
programmatically, they won't actually do anything as they are just not
used in the SQL.

(And parsesql is on by default; I doubt EdmundasLT has switched it
off)

Which brings us to EdmundasLT: any news? I hope things are working for
you...

----------------------------------------------------------------------
avra · 2014-09-24 08:26

Quote from: Mike.Cornflake on September 23, 2014, 05:24:33 pm(That's
two posts today I've commented without a clue as to the solution -
what is wrong with me??)Showing good will?  ;-)

----------------------------------------------------------------------
EdmundasLT · 2014-09-24 17:17

Hello everybody, sorry for late response, had some trouble with
internet provider
Let's start from beginning.
This is a stored procedure executable. This one i get when i press RMB
on procedure in flamerobin and press execute. This is with data in it
to add to table. If i press commit, it apears in DB.
Code: [Select]EXECUTE PROCEDURE
KLINSERT('Tom','Jeferson','555555','Berlin','789456','456@555.org')
Mike.Cornflake Yes I added parameters in Object inspector. I used this
code as example from another part of my code in which data is read
from edit boxes and passed to DB by this code: 
(P.S. I excluded parameters part to keep post smaller)
 Code: [Select]SQLQuery1.SQL.Add ('INSERT INTO KLIENTAI');
SQLQuery1.SQL.Add ('(VARDAS, Pavarde, ak, Miestas, telefonas, elpastas
)');
SQLQuery1.SQL.Add ('VALUES
(:vardas,:pavarde,:ak,:miestas,:telefonas,:elpastas)');

Also BigChimp, I intentionally left out CLIENTID, because it is a
primary key and is added automatically, sorry for not mentioning it
earlier.
Code: [Select]//you're missing CLIENTID here... you should add it...
Using your codes I managed to fix my mistakes and make it work.
Thank you very much.

This is a working code if you are interested:
Code: [Select]procedure TForm2.Button4Click(Sender: TObject);
var
   clientid: string ;
   vardas: String;
   pavarde: String;
   ak: String;
   miestas: String;
   telefonas: String;
   elpastas: String;
begin
   vardas:=Edit1.Text;
   pavarde:=Edit2.Text;
   ak:=Edit4.Text ;
   miestas:=ComboBox1.Text;
   telefonas:=Edit5.Text;
   elpastas:=Edit6.Text;

   SQLQuery1.SQL.Text:='EXECUTE PROCEDURE KLINSERT
(:VARDAS,:PAVARDE,:AK,:MIESTAS,:TELEFONAS,:ELPASTAS)';
   SQLQuery1.Params.ParamByName('vardas').AsString:=vardas;
   SQLQuery1.Params.ParamByName('pavarde').AsString:=pavarde;
   SQLQuery1.Params.ParamByName('ak').AsString:=ak;
   SQLQuery1.Params.ParamByName('miestas').AsString:=miestas;
   SQLQuery1.Params.ParamByName('telefonas').AsString:=telefonas;
   SQLQuery1.Params.ParamByName('elpastas').AsString:=elpastas;

  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
end;

----------------------------------------------------------------------
BigChimp · 2014-09-24 17:41

Hi EdmundasLT,

Going to be a bit blunt below, please don't take it as an insult...
it's been a long day here. Summary: glad you got it working
Quote from: EdmundasLT on September 24, 2014, 07:17:51 pmCode:
[Select]EXECUTE PROCEDURE
KLINSERT('Tom','Jeferson','555555','Berlin','789456','456@555.org')Ok.

Quote from: EdmundasLT on September 24, 2014, 07:17:51 pm(P.S. I
excluded parameters part to keep post smaller)
 Code: [Select]SQLQuery1.SQL.Add ('INSERT INTO KLIENTAI');
SQLQuery1.SQL.Add ('(VARDAS, Pavarde, ak, Miestas, telefonas, elpastas
)');
SQLQuery1.SQL.Add ('VALUES
(:vardas,:pavarde,:ak,:miestas,:telefonas,:elpastas)');
Bad idea to not include the entire SQL! How can we guess what was
there? How can we guess something was even missing?

Quote from: EdmundasLT on September 24, 2014, 07:17:51 pmAlso
BigChimp, I intentionally left out CLIENTID, because it is a primary
key and is added automatically, sorry for not mentioning it earlier.
Sorry, then you should have posted the proper stored procedure
definition instead of the one you did post - where CLIENTID is a
mandatory parameter and is used in the INSERT INTO KLIENTAI part
inside the stored procedure.

Note: please always copy/paste exact code. If you don't (think it is
too long, irrelevant etc), please indicate where you have cut/what you
have modified. Otherwise it's a guessing game...

Quote from: EdmundasLT on September 24, 2014, 07:17:51 pmUsing your
codes I managed to fix my mistakes and make it work.
Thank you very much.
Great - our pleasure

Quote from: EdmundasLT on September 24, 2014, 07:17:51 pmUsing your
codes
This is a working code if you are interested:
Excellent, thanks for posting the code - others facing the same
problem will also be able to find things easier this way.

----------------------------------------------------------------------
EdmundasLT · 2014-09-24 17:57

Also a whole Stored Procedure code from FlameRobin, to help other
people like me (begginers at Delphi programming)
Code: [Select]SET TERM ^ ;
CREATE/ALTER PROCEDURE KLINSERT (
    VARDAS Varchar(255),
    PAVARDE Varchar(255),
    AK Varchar(255),
    MIESTAS Varchar(255),
    TELEFONAS Varchar(255),
    ELPASTAS Varchar(255) )
AS
begin
    INSERT INTO KLIENTAI (VARDAS, PAVARDE, AK, MIESTAS, TELEFONAS,
ELPASTAS)
    VALUES (:VARDAS, :PAVARDE, :AK, :MIESTAS, :TELEFONAS, :ELPASTAS);
end^
SET TERM ; ^


GRANT EXECUTE ON PROCEDURE KLINSERT TO  TEST;

CREATE/ALTER  - choose one or other depending on your needs.
TEST  - is user name from Database, also same name you use to connect
with SQLConnection1.

Also If you don't want to spend lots of time writing procedures you
can use Firebird SQL Studio. This tools takes data from DB and writes
SQL code for data you selected. THIS IS NOT AD!!! Just a tip.  No
offence intended to RULES or such.

----------------------------------------------------------------------
mangakissa · 2014-09-25 07:06

Maybe the SQLdb developers can create a component as TSQLProc in the
future

----------------------------------------------------------------------
BigChimp · 2014-09-25 07:07

What is TSQLProc?

----------------------------------------------------------------------
rvk · 2014-09-25 08:40

Quote from: BigChimp on September 25, 2014, 09:07:27 amWhat is
TSQLProc?
Probably TSQLStoredProc.

----------------------------------------------------------------------
BigChimp · 2014-09-25 08:44

Reading that quickly, I don't really see the advantage over a regular
TSQLQuery object, but I may well be missing something...

@mangakissa: how would using a TSQLStoredProc have helped EdmundasLT
write code quicker/faster/easier?

----------------------------------------------------------------------
rvk · 2014-09-25 08:54

Quote from: BigChimp on September 25, 2014, 10:44:24 amhow would using
a TSQLStoredProc have helped EdmundasLT write code
quicker/faster/easier?
Using TSQLStoredProc you don't need to specify the complete SQL for
executing the stored procedure.
You can do something like this:
Code: [Select]SQLStorecProc1.StoredProcName := 'KLINSERT';
SQLStorecProc1.Prepare;
SQLStorecProc1.ParamByName('vardas').AsString := vardas;
SQLStorecProc1.ParamByName('pavarde').AsString := pavarde;
SQLStorecProc1.ParamByName('ak').AsString := ak;
SQLStorecProc1.ParamByName('miestas').AsString := miestas;
SQLStorecProc1.ParamByName('telefonas').AsString := telefonas;
SQLStorecProc1.ParamByName('elpastas').AsString := elpastas;
SQLStorecProc1.ExecProc;

Or even easier:
Code: [Select]SQLStoredProc1.ExecProc('KLINSERT', [vardas, pavarde,
ak, miestas, telefonas, elpastas]);

----------------------------------------------------------------------
BigChimp · 2014-09-25 09:01

Mmmm, ok.

Well, we already can fairly easily obtain metadata (note: not
implemented equally in all sql connectors)
http://www.freepascal.org/docs-
html/fcl/sqldb/retrievingschemainformation.html

so writing a descendent should probably not be too hard.
(Edit: clarification: ... but I'm not going to do it)

Haven't kept track of latest sqldb developments so don't know if e.g.
Michael Van Canneyt already implemented something like this...

----------------------------------------------------------------------
rvk · 2014-09-25 09:11

Quote from: rvk on September 25, 2014, 10:54:59 amOr even easier:
Code: [Select]SQLStoredProc1.ExecProc('KLINSERT', [vardas, pavarde,
ak, miestas, telefonas, elpastas]);
F.Y.I. That last example with parameters directly to ExecProc isn't
possible in the standard TSQLStoredProc in Delphi.

It's something i saw in the TFDStoredProc from FireDAC i found here.
(There .ExecProc accepts the parameters directly and doesn't get in
the way of the standard .ExecProc)

----------------------------------------------------------------------
BigChimp · 2014-09-25 09:13

Ok. Suggest anybody interested in implementing this get in touch
through the fpc-devel (or fpc) mailing list...