on 10-24-2016 3:58 PM
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?
User | Count |
---|---|
88 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.