[soci-users] Basic SELECT not working.

[Nicolas Deroche]

Hello,
I'm trying to execute a basic SELECT statement on a MySQL backend, like
this:

*dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",
soci::use(linkname, "ii"),
soci::into(server->linkname),
soci::into(server->name),
soci::into(server->engine_type),
soci::into(server->listening_ip),
soci::into(server->listening_port),
soci::into(server->auth_id),
soci::into(server->auth_pw),
soci::into(server->max_clients),*
*soci::into(server->parent_linkname),
soci::into(server->childs),
soci::into(server->id));
st.execute();
if(!st.got_data())
{
std::cout << "SQL = SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`='" << linkname << "'"
<< std::endl;**
delete server;
throw Server::does_not_exist();*
}

Thing is, this request works in mysql workbench, but not here?
What could be wrong ? I don't get it.
thanks, nico

[Mateusz Loskot]

Post by Nicolas Deroche
Hello,
I'm trying to execute a basic SELECT statement on a MySQL backend, like
dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,

You make the sql object connected with MySQL, don't you?

Post by Nicolas Deroche
Thing is, this request works in mysql workbench, but not here?

Specify "not working" in greater details, please.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Nicolas Deroche]

Yes the connection does not trow, and therefor is connected to mysql!
Well, got_data() returns 0.

Post by Mateusz Loskot

Post by Nicolas Deroche
Hello,
I'm trying to execute a basic SELECT statement on a MySQL backend, like
dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,

You make the sql object connected with MySQL, don't you?

Post by Nicolas Deroche
Thing is, this request works in mysql workbench, but not here?

Specify "not working" in greater details, please.
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net
------------------------------------------------------------------------------
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev
_______________________________________________
soci-users mailing list
https://lists.sourceforge.net/lists/listinfo/soci-users

[Mateusz Loskot]

Post by Nicolas Deroche
Yes the connection does not trow, and therefor is connected to mysql!
Well, got_data() returns 0.

Do you fetch()?

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Nicolas Deroche]

The query should return only one row, is t really necessary ?

Post by Mateusz Loskot

Post by Nicolas Deroche
Yes the connection does not trow, and therefor is connected to mysql!
Well, got_data() returns 0.

Do you fetch()?
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net
------------------------------------------------------------------------------
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev
_______________________________________________
soci-users mailing list
https://lists.sourceforge.net/lists/listinfo/soci-users

[Mateusz Loskot]

Post by Nicolas Deroche
The query should return only one row, is t really necessary ?

Please, check the docs. It's all there what calls are necessary.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Nicolas Deroche]

Well using the fetch function right after execute is not working either

dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",
soci::use(linkname, "ii"),
soci::into(server->linkname),
soci::into(server->name),
soci::into(server->engine_type),
soci::into(server->listening_ip),
soci::into(server->listening_port),
soci::into(server->auth_id),
soci::into(server->auth_pw),
soci::into(server->max_clients),
soci::into(server->parent_linkname),
soci::into(server->childs),
soci::into(server->id));
*st.execute();
st.fetch();
if(!st.got_data())
{
std::cout << "SQL = SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`='" << linkname << "'"
<< std::endl;
delete server;
throw Server::does_not_exist();*
}
else
{
Server::gets().insert({server->linkname, server});
server->init_load();
return *server;
}

Post by Mateusz Loskot

Post by Nicolas Deroche
The query should return only one row, is t really necessary ?

Please, check the docs. It's all there what calls are necessary.
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net
------------------------------------------------------------------------------
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev
_______________________________________________
soci-users mailing list
https://lists.sourceforge.net/lists/listinfo/soci-users

[Nicolas Deroche]

And the version like this, isn't working either:
sql.once << "....";
if(!sql.got_data())
{
/// throws
}

Post by Nicolas Deroche
Well using the fetch function right after execute is not working either
dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",
soci::use(linkname, "ii"),
soci::into(server->linkname),
soci::into(server->name),
soci::into(server->engine_type),
soci::into(server->listening_ip),
soci::into(server->listening_port),
soci::into(server->auth_id),
soci::into(server->auth_pw),
soci::into(server->max_clients),
soci::into(server->parent_linkname),
soci::into(server->childs),
soci::into(server->id));
*st.execute();
st.fetch();
if(!st.got_data())
{
std::cout << "SQL = SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`='" << linkname << "'"
<< std::endl;
delete server;
throw Server::does_not_exist();
*
}
else
{
Server::gets().insert({server->linkname, server});
server->init_load();
return *server;
}

Post by Mateusz Loskot

Post by Nicolas Deroche
The query should return only one row, is t really necessary ?

Please, check the docs. It's all there what calls are necessary.
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net
------------------------------------------------------------------------------
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev
_______________________________________________
soci-users mailing list
https://lists.sourceforge.net/lists/listinfo/soci-users

[h***@code-styling.de]

st.got_data() simply returns the current member and doesn't any action.

If your request expects only 1 record returned back, than you are at this
secanios:

1.) only st.got_data() -> false because nothing executed so far
2.) st.fetch(); st.got_data() -> false too because fetch() returned true for the
one record and got_data() still false because it's no bulk.

try this:

st.execute();
if (st.fetch())
{
printf("well done!");
}
else
{
printf("sucks again!");
}

Post by Nicolas Deroche
sql.once << "....";
if(!sql.got_data())
{
/// throws
}

Post by Nicolas Deroche
Well using the fetch function right after execute is not working either
dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",
soci::use(linkname, "ii"),
soci::into(server->linkname),
soci::into(server->name),
soci::into(server->engine_type),
soci::into(server->listening_ip),
soci::into(server->listening_port),
soci::into(server->auth_id),
soci::into(server->auth_pw),
soci::into(server->max_clients),
soci::into(server->parent_linkname),
soci::into(server->childs),
soci::into(server->id));
st.execute();
st.fetch();
if(!st.got_data())
{
std::cout << "SQL = SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`='" << linkname << "'"
<< std::endl;
delete server;
throw Server::does_not_exist();
}
else
{
Server::gets().insert({server->linkname, server});
server->init_load();
return *server;
}

Post by Mateusz Loskot

Post by Nicolas Deroche
The query should return only one row, is t really necessary ?

Please, check the docs. It's all there what calls are necessary.
Best regards,
--
Mateusz Loskot,<http://mateusz.loskot.net>
------------------------------------------------------------------------------
Build for Windows Store.
<http://p.sf.net/sfu/windows-dev2dev>
_______________________________________________
soci-users mailing list
<https://lists.sourceforge.net/lists/listinfo/soci-users>

[Mateusz Loskot]

Post by h***@code-styling.de
st.got_data() simply returns the current member and doesn't any action.
If your request expects only 1 record returned back, than you are at this
1.) only st.got_data() -> false because nothing executed so far
2.) st.fetch(); st.got_data() -> false too because fetch() returned true for
the one record and got_data() still false because it's no bulk.

sql.prepare is dedicated to repeated execution, indeed, but it also work
with single time execution, but it requires explicit fetch(), as here:

sql << "insert into test(val, str) values(12, \'abc\')";
int val;
std::string str="abc";
statement st = (sql.prepare <<
"select val from soci_test where str=:ii", use(str, "ii"), into(val));
st.execute();
bool g = st.fetch();
assert(g == st.got_data()); // simply check what st.fetch() set in previous call
assert(val == 12);

IMHO, it's just waste of time to prepare if one does not need to
prepare, but should work.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Mateusz Loskot]

Post by Nicolas Deroche
Well using the fetch function right after execute is not working either
dbs::Server* server = new dbs::Server();
soci::statement st =
(sql.prepare << "SELECT linkname, name, engine, listening_ip,
listening_port, auth_id, auth_pw, max_clients, parent_linkname, childs, id
FROM `" << Server::table_name << "` WHERE `linkname`=':ii'",

-------------------------------------------------------------------------------------^^^^^^^^^^^^

You should not need to single-quote the placeholders as in ':ii'
soci::use(linkname, "ii").
Try with simple linkname=:ii

Post by Nicolas Deroche
st.execute();
st.fetch();
if(!st.got_data())
{

Just if (!st.fetch()) is enough.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net