help with TQuery

[forbvijay]

Hi,
i am trying to create a stored procedure with TQuery, but the code is resulting in the Token Unknown error pointing to the ':' used in the sql.add statement. This same procedure as it is is working fine when directly executed with the SQL Explorer of the INTERBASE(i am useing interbase as my application database), the code snippet is as follows:

stnquery.active := false;
stnquery.SQL.clear;
stnquery.SQL.Add('create procedure sample2(invar varchar(15))'+
' as '+
' declare variable v_coggnr varchar(15);'+
' begin '+
'select signo from tocdsignal76 where signame like :invar into :v_coggnr;'+
'insert into temp(attr1) values(:v_coggnr);'+
' end');
stnquery.Open;
stnquery.ExecSQL;

The code when complied is giving error at the : given in the select statement,(:invar refers to input variable and :v_coggnr refers local var. (interbase procedure)) can any one help me out this.

thanxs in advance,

vj.

[delphiman]

I am not sure I know exatly what the problem is from your code but I have a tip which might help you.

I am assuming that you have an TUpdateSQL component to which you have connected your Query at "UpDateObject" in the Event Handler [in the Object Inspector].

It is my practice to

1. RightClick on the UpDateObject component
2. Click on "UpDateSQLEditor" ->GetTableFields->SelectPrimaryKeys->GenerateSQL.
3. Click on the "SQL"code and you will find DelDelphi has provided the code you require to "Modify", "Insert" and "Delete".

A word of warning!

Where Delphi has included a KeyField you should manually remove it from the provided code.

You might then borow from the following example

    ModifySQL.Add ('set');
    ModifySQL.Add ('LNo =:LNo, PNo = :PNo,');
    ModifySQL.Add ('Inits = :Inits, LName = :LName, RefPerson = :RefPerson, ');

    InsertSQL.Add ('(LNo, LNo2, PNo, Inits, LName, RefPerson)');
// Note the double brackets - or you are dead!
    DeleteSQL.Add ('where');
    DeleteSQL.Add ('LNo2 = :OLD_LNo2');
  end;
  if qryMyKey.UpdatesPending then
    qryMyKey.ApplyUpdates;
end;

Hope this helps. :-)

delphiman