[soci-users] Bulk inserting into decimal fields on SQL Server via ODBC Backend

[Andrew Grafham]

Hi,

Is there a way of accurately bulk inserting into a decimal field in SQL
server? I'm trying to insert into a table called nets, which has 2 column,
an integer column (id) and a decimal(23,6) column (net). This is the code
I'm using:-

try
{
session sql("odbc", "DSN=socitest;Uid=sa;Pwd=XXXXX;");

vector<int> ids;
vector<string> nets;

for (int i = 0; i != 1000; ++i)
{
ids.push_back(i);
nets.push_back("1234.123456");
}

sql << "insert into nets(id, net) values(:id, :net)",
use(ids), use(nets);


}
catch (soci::odbc_soci_error const& e)
{
cerr << "ODBC Error Code: " << e.odbc_error_code() << endl
<< "Native Error Code: " << e.native_error_code() << endl
<< "SOCI Message: " << e.what() << std::endl
<< "ODBC Message: " << e.odbc_error_message() << endl;
}
catch (exception const &e)
{
cerr << "Error: " << e.what() << '\n';
}

And I get:-

ODBC Error Code: 01000
Native Error Code: 0
SOCI Message: Statement Execute
ODBC Message: [SOCI]: No error.

If I change the string vector to doubles, it works fine, but will lose
precision (and it works fine via the MySQL backend).

Thanks

Andy

[Vadim Zeitlin]

On Mon, 27 Jan 2014 17:13:29 +0000 Andrew Grafham <***@gmail.com> wrote:

AG> Is there a way of accurately bulk inserting into a decimal field in SQL
AG> server?

Sorry for the bad news but no, there is no support for inserting string
values in SQL_DECIMAL columns in the current ODBC backend code.

Worse, I'm not actually sure how would one modify the code to do it as
currently it assumes a one-to-one mapping between SQL and C++ types, but
this is just not true for SQL_DECIMAL which should be convertible to both
double (if the potential accuracy loss doesn't matter) and string (if we
really need to store the exact representation).

Regards,
VZ

[Andrew Grafham]

Post by Vadim Zeitlin
On Mon, 27 Jan 2014 17:13:29 +0000 Andrew Grafham <
AG> Is there a way of accurately bulk inserting into a decimal field in SQL
AG> server?
Sorry for the bad news but no, there is no support for inserting string
values in SQL_DECIMAL columns in the current ODBC backend code.
Worse, I'm not actually sure how would one modify the code to do it as
currently it assumes a one-to-one mapping between SQL and C++ types, but
this is just not true for SQL_DECIMAL which should be convertible to both
double (if the potential accuracy loss doesn't matter) and string (if we
really need to store the exact representation).
Regards,
VZ

Thanks for the quick response. I've just realised I can work around this by
doing a "convert" in the SQL, e.g. :-

sql << "insert into nets(id, net) values(:id, convert (decimal
(32,6), :net ))",

use(ids), use(nets);

Cheers

Andy