mORMot Open Source

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Trying to pass parameters in SELECT for Oracle...

Hello,

Having succesfully connected to Oracle am now trying to run
a simple query with parameters (w/o parameters works fine).
The query is a simple select with one parameter as shown in below.

When running it, get the following error:
'Prepare called with wrong ExpectedResults'
which apparently means that Mormot does not
recognize it as a legitimate select statement.

Any help would be greatly appreciated.
Regards,
Sami

The code is as follows (actually the sql statement sql_ is read from an inifile)
=============

Var
sql_    : RawUTF8;
Props  : TSQLDBOracleConnectionProperties;
BEGIN
sql_       := 'select * from tbl.studentId where accepted_date > To_date(?, 'DD-MM-YYYY')';
Props     := TSQLDBOracleConnectionProperties.Create(MyAlias,'',
                MyUser,Myencrp_str);

Props.ExecuteNoResult(sql_ ,['01.09.2009']); // Error here
With Props.Execute(sql_ ,[],@row) Do
    While Step Do
    Begin (* Step thru results *)
     // .....
  
    End; (* while *)
End; (* with *)
END;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Trying to pass parameters in SELECT for Oracle...

Is not the right format '01/09/2009'?

You can use directly date/time parameters with TDate/TDateTime values, by using DateToSQL(), DateTimeToSQL() and Iso8601ToSQL() functions, as such:

sql_  := 'select * from tbl.studentId where accepted_date > ?';
Props.ExecuteNoResult(sql_ ,[DateToSql(Now)]);

And it will work with all kind of databases, not only Oracle.

jonsafi

Member

Registered: 2011-07-26

Posts: 58

Re: Trying to pass parameters in SELECT for Oracle...

Many thanks! Was looking for the error in the wrong place, thinking that
some additional binding was required.