Dan Forbes
2014-02-21 21:14:40 UTC
This is a copy of a StackOverflow post<http://stackoverflow.com/questions/21894220/sql-server-stored-procedure-via-soci-odbc-in-c>...
I am using the SOCI library to programmatically interact with a SQL Server DB via ODBC. I am having trouble getting values via output parameters in stored procedures. Some code (modeled after SOCI documentation<http://soci.sourceforge.net/doc/3.2/statements.html#procedures>)...
/*
* create procedure
* Dan.soci_proc @id int, @name varchar(32) output
* as begin
* set nocount on;
* select @name = name from Dan.soci_test where id = @id
* end
*/
std::string sql = "Dan.soci_proc :id, :name";
std::string name;
int proc_ndx = 1;
soci::procedure proc = (my_soci.get_session().prepare << sql, soci::use(proc_ndx),
soci::use(name));
std::cout << "\nAttempting to execute stored procedure " << size << " times."
<< std::endl;
for (; proc_ndx < adj_size; ++proc_ndx) {
try {
proc.execute();
while (proc.fetch())
std::cout << "Fetched: " << name << std::endl;
} catch (const soci::odbc_soci_error& e) {
std::cerr << "Error executing stored procedure." << std::endl;
std::cerr << e.what() << std::endl;
std::cerr << e.odbc_error_message() << std::endl;
return;
}
}
My code does not throw any errors or exceptions, but nothing is fetched, either. I have tried calling it many different ways (normal exec syntax, ODBC call syntax, etc.), but nothing seems to work. I'm wondering if the error goes back to this from here<http://msdn.microsoft.com/en-us/library/ms131685.aspx>...
"If an input/output parameter is omitted, or if a literal is supplied for the parameter, the driver discards the output value."
Unfortunately, SOCI doesn't really seem to support parameter markers, at least as far as I can tell.
I have made the code work by using this kind of syntax...
std::string sql = "declare @name varchar(32); "
"exec Dan.soci_proc :id, @name = @name output; select @name";
...
soci::procedure proc = (my_soci.get_session().prepare << sql, soci::use(proc_ndx),
soci::into(name));
But that isn't ideal, for reasons that I think are obvious.
Has anyone out there used SOCI and have some input into what I need to do differently to get this to work?
I am using the SOCI library to programmatically interact with a SQL Server DB via ODBC. I am having trouble getting values via output parameters in stored procedures. Some code (modeled after SOCI documentation<http://soci.sourceforge.net/doc/3.2/statements.html#procedures>)...
/*
* create procedure
* Dan.soci_proc @id int, @name varchar(32) output
* as begin
* set nocount on;
* select @name = name from Dan.soci_test where id = @id
* end
*/
std::string sql = "Dan.soci_proc :id, :name";
std::string name;
int proc_ndx = 1;
soci::procedure proc = (my_soci.get_session().prepare << sql, soci::use(proc_ndx),
soci::use(name));
std::cout << "\nAttempting to execute stored procedure " << size << " times."
<< std::endl;
for (; proc_ndx < adj_size; ++proc_ndx) {
try {
proc.execute();
while (proc.fetch())
std::cout << "Fetched: " << name << std::endl;
} catch (const soci::odbc_soci_error& e) {
std::cerr << "Error executing stored procedure." << std::endl;
std::cerr << e.what() << std::endl;
std::cerr << e.odbc_error_message() << std::endl;
return;
}
}
My code does not throw any errors or exceptions, but nothing is fetched, either. I have tried calling it many different ways (normal exec syntax, ODBC call syntax, etc.), but nothing seems to work. I'm wondering if the error goes back to this from here<http://msdn.microsoft.com/en-us/library/ms131685.aspx>...
"If an input/output parameter is omitted, or if a literal is supplied for the parameter, the driver discards the output value."
Unfortunately, SOCI doesn't really seem to support parameter markers, at least as far as I can tell.
I have made the code work by using this kind of syntax...
std::string sql = "declare @name varchar(32); "
"exec Dan.soci_proc :id, @name = @name output; select @name";
...
soci::procedure proc = (my_soci.get_session().prepare << sql, soci::use(proc_ndx),
soci::into(name));
But that isn't ideal, for reasons that I think are obvious.
Has anyone out there used SOCI and have some input into what I need to do differently to get this to work?