Setting SQL statement and parameters in TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
jscrump · 2010-08-23 21:25

I am having problems with TSQLQuery when I try to execute a stored
procedure that returns a singleton parameter.

The statement fails with an exception on ExecSQL, it also fails if the
Open/Close methods are used. The exception appears to be related to a
variant parameter, but every thing appears to me be straight forward.

I reviewed the source in sqldb to try and see how the parameters are
parsed, but don't see any obvious problems.

The following code section lists the original sql statement using
replaceable parameters, commented out, and the working embedded sql
statement which doesn't return a value.

Can anyone tell me what I'm doing wrong. The stored procedure works
fine in FlameRobin. The database is Firebird 2.5 64 bit on Window
Vista 64.


 
  Result := false;
  iProject := 0;
  dbServer.Transaction.StartTransaction;
  try
    // Statement that fails on ExecSQL is:
    // execute procedure ADD_PROJECT
(:ProjectName,:Folder,:Description,:Date) returning_values :iProject
    {
    spAddProject.Params.ParamByName('PROJECTNAME').AsString :=
aParams.AsString('PROJECTNAME');
    spAddProject.Params.ParamByName('FOLDER').AsString :=
aParams.AsString('FOLDER');
    spAddProject.Params.ParamByName('DESCRIPTION').AsString :=
aParams.AsString('DESCRIPTION');
    spAddProject.Params.ParamByName('DATECREATED').AsString :=
'08/23/2010';
    }
    spAddProject.SQL.Text := Format('execute procedure ADD_PROJECT
(%s,%s,%s,null);',
      [QuotedStr(aParams.AsString('PROJECTNAME')),
       QuotedStr(aParams.AsString('FOLDER')),
       QuotedStr(aParams.AsString('DESCRIPTION'))
       ]);
    spAddProject.ExecSQL;
    //iProject := spAddProject.Fields[0].AsInteger;
    //Result := iProject > 0;
    Result := true;
    dbServer.Transaction.Commit;
  except
    on E:Exception do
      begin
        dbServer.Transaction.Rollback;
        SysLog.LogMsg(UNT_NAME,'ADD_PROJECT',E.Message);
      end;
  end;
  aParams.SetParameter('SUCCESS',Result);
  if Result then aParams.SetParameter('IPROJECT',iProject);
  aMessage.AddParams(PART_PARAMS,aParams);

----------------------------------------------------------------------
Lacak2 · 2010-08-24 05:56

Which is the text of exception ?
Which version of FPC ?

----------------------------------------------------------------------
jscrump · 2010-08-24 11:46

Lazarus version 0.9.29 2010-07-27
FPC version 2.4.3
SVN Revision 26727


Lazarus Error:
  Project Server raised exception class 'Exernal: SIGSEGV'

upon continuing:

Lazarus Error:
  Project Server raised exception class 'RunError(0)'

upon continuing:

Debuffer Exception Notification:
  Project Server raised exception class '\' with message:
H?tl

upon continuing:

Lazarus Error::
  Project Server raised exception class 'RunError(35752)'

The exception message is
  'Control-C hit'

The exception is raised by the prepare method. The SQLParser code
doesn't explicitly handle stored procedures. It uses a state machine
to look for "select" lexems to change parsing states. It may be an
issue with replacement parameter selection or type assignment.

----------------------------------------------------------------------
jscrump · 2010-08-24 19:48

Rewriting the stored procedure without returning a value allows the
TSQLQuery to work.

The problem is probably in how the TSQLQuery parses the source line in
determining parameter replacement.

To move forward with Lazarus I rewrote the affected stored procedures.
Not an optimal solution, but a acceptable work around.