Skip to Content

Parametrized queries with BoRecordSet

Hello, all

How does one execute a parametrized query against an
SAP HANA Database via the  BoRecordSet  object?   It
having   no  analog  of  DbConnection.AddParameter()
method, with MS SQL  Server  I  implemented  my  own
mechanism for parametrized queries, which internally
calls

EXEC sp_executesql @query, @par_decl,
                   @par1 = <val1>, @par2 = <val2>...

It is much easier and safer to write:

   BoRecordSet rs;
   RecordSetWrapper rsw = new RecordSetWrapper( rs );
   ...
   double GetRate( string currency, DateTime date )
   {  const string Query =
      @" SELECT ""Rate"" FROM ORTT
         WHERE
            ""RateDate"" = :date AND
            ""Currency"" = :currency
      ";
      rsw.AddParameter( "currency", currency);
      rsw.AddParameter( "date",     date    );
      return ( double )rsw.GetValue();
   }

than

   double GetRate( string currency, DateTime date )
   {  const string Query =
      @" SELECT ""Rate"" FROM ORTT
         WHERE
            ""RateDate"" =  '{0}' AND
            ""Currency"" = N'{1}'
      ";
      string query, date_str, cur_str;
      cur_db  = currency.Replace( "'","''" ); // Ugly!
      date_db = date.ToString( "yyyyMMdd" );  // Ugly!
      // Ugly and inefficient and unsafe:
      query = String.Format( QueryTeml, date_db, cur_db );
      return ( double )rsw.GetValue();
   }

I often have to  use  the  DI  SQL  connection,  and
therefore  BoRecordSet rather than DbConnection from
Ado.Net, in situations such as  performing  a  query
from  within  a  DI API  transaction where any other
connection would be locked.

Futhermore, parametrized queries are more  effective
because  they  do  not  waste  the precious SQL-plan
cache, whereas wrapping every single  query  into  a
server-side   procedure   is  utterly  inconvenient.
Therefore, if  HANA  did  not  support  parametrized
queries  it  would  be  a serious flaw, so I must be
missing something.  Am I?
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers