Re: [sqlite] How do you guys use sqlite from C++?
Vinnie-4 wrote:
>
>> From: Neville Franks
>
> Apparently I did come up with an original idea. Because none of the
> wrappers from the archives are using variable argument lists.
>
That's because many C++ programmer don't like using printf-like vararg calls
which are not type safe. I for one want to ensure the compiler catches my
mistake, instead of getting a crash at runtime if I don't provide the right
format string (wrong type, wrong number of args), or the wrong addresses.
{
enum { DERIVED = 0, BASE, DISTANCE }; // just for easier code
reading
typedef boost::tuple
Row;
std::vector rset;
get_rows(
"select type_id, id_is_a, distance from rtti where type_id = :1
and id_is_a = :2",
make_tuple(some_id, another_id), rset
);
CPPUNIT_ASSERT_EQUAL(1, (int)rset.size());
CPPUNIT_ASSERT_EQUAL(some_id, boost::get(rset[0]));
CPPUNIT_ASSERT_EQUAL(another_id, boost::get(rset[0]));
CPPUNIT_ASSERT_EQUAL((unsigned short)1,
boost::get(rset[0]));
}
In the code above both the binds and the gets are routed to the proper
SQLite calls based on the types of the variables, thanks to sqlite3pp (one
of the wrappers listed in the wrapper page) and our own extensions using the
magic of templates and boost, all done at compile time. There can be no
crashes, and any SQLite error is translated into a C++ exception being
thrown (sqlite3pp does not do that, we do) that we catch higher up (actually
cppunit does the catching). That's the C++ way ;-) --DD
--
View this message in context:
http://www.nabble.com/How-do-you-guys-use-sqlite-from-C%2B%2B--tp23253633p23302398.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
Just stumbled over SOCI (http://soci.sourceforge.net/) in my search for an easy to use C++ interface to relational DBs. Scales from simple scalar queries to OR mapping and STL/Boost integration. Supports SQLite as backend. Haven't tested it yet, but the concept sounds clean and promising. MfG H. Nehring ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
Just stumbled over SOCI (http://soci.sourceforge.net/ [http://soci.sourceforge.net/]) in my search for an easy to use C++ interface to relational DBs. Scales from simple scalar queries to OR mapping and STL/Boost integration. Supports SQLite as backend. Haven't tested it yet, but the concept sounds clean and promising. MfG H. Nehring Pt! Schon vom neuen WEB.DE MultiMessenger gehört? Der kann`s mit allen: *http://www.produkte.web.de/messenger/?did=3123* [http://www.produkte.web.de/messenger/?did=3123] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
> > From: Neville Franks > implementations are meant to save > prepared statements in a cache and IIUC most do. Thats exactly what I am doing. I still pass the original statement every time. The same call either creates a new statement or re-uses the cached version. > The trend is more for function call > chaining. I have seen at least one wrapper besides one that > I have written that copies the idea of overloaded shift operators > for formatted input/output I've been amazingly resistant to the use of the standard c++ template library, and boost. However, in this situation it sounds like overloaded shift operators is a great idea! It solves some of the deficiencies in my approach. > > Hasn't anyone else used variable argument lists > for binding parameters > > and what not? > > There is a built-in API for that: > http://sqlite.org/c3ref/mprintf.html Hmm actually I am not composing the SQL statement text using variable arguments. I am using variable arguments to pass in what is essentially a list of pointers to be used in calls to bind..() and fetch_column...(). > Part of the reason you may find that var-arg binding and > similar > techniques are not widely supported is that string-based > SQL > manipulation is considered dangerous. SQL injection is a > very common > and ridiculously successful attack, especially in the web > world. Yeah but like I said I am not composing the statement text. > Personally, I don't use var-args in C++ code. You lose > type-safety, can't use user-defined types, and can't detect when > the wrong number of arguments is passed, not even at run-time. These are exactly the problems I want to solve in my current implementation. I am going to explore the idea of using overloaded shift operators with function chaining. Just to give you an idea of what I have currently: bRow=m_db.Select( err, &stmt, "SELECT " " NAME, " " FULLPATH, " " PARENTID " "FROM DIR " " WHERE DIRID=?;", "D,SSD", dirRid, &strName, &dirStrPath, &parentRid ); The string "D,SSD" tells the function about the data types of the following arguments. The comma is used to separate the parameter binds from the column binds. So as you can see I am not composing the SQL text. But as it was pointed out this approach lacks the type safety. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
Personally, I don't use var-args in C++ code. You lose type-safety, can't use user-defined types, and can't detect when the wrong number of arguments is passed, not even at run-time. Instead, I'd create a class you can 'feed' variables too in much the same way that boost::format works. See http://www.boost.org/doc/libs/1_38_0/libs/format/doc/format.html for more details. Logan Ratner | +1 713 839 9656 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie Sent: Monday, April 27, 2009 7:07 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How do you guys use sqlite from C++? > From: Neville Franks > Subject: Re: [sqlite] How do you guys use sqlite from C++? > I use a modified version of the C++ wrapper > http://www.codeproject.com/KB/database/CppSQLite.aspx Apparently I did come up with an original idea. Because none of the wrappers from the archives are using variable argument lists. All these wrappers are basically doing the same thing, a very thin layer on top of SQlite. My goal for a wrapper was to allow, using only a single function call, all of the parameter binds and column values to get assigned. Having a separate function call to retrieve each column or bind each parameter isn't much better than straight SQLite (not that I'm complaining about SQLite, it rocks!). Hasn't anyone else used variable argument lists for binding parameters and what not? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
On Mon, Apr 27, 2009 at 05:06:31AM -0700, Vinnie scratched on the wall: > Hasn't anyone else used variable argument lists for binding parameters > and what not? There is a built-in API for that: http://sqlite.org/c3ref/mprintf.html Part of the reason you may find that var-arg binding and similar techniques are not widely supported is that string-based SQL manipulation is considered dangerous. SQL injection is a very common and ridiculously successful attack, especially in the web world. It would also be nearly be non-existent if everyone used bound parameters. Generally, the issue comes down to properly quoting and escaping special characters within the values that are being passed in. It is a much harder problem than most people think, as evident by the tens-of-thousands of hacked sites out there. Bound parameters largely solve this problem as the parameter value is never inserted into the SQL statement, meaning that a string representation of the parameter value is never pushed through the SQL parser. This makes injection essentially impossible. It is possible to build a var-arg style wrapper that is based off bound parameters under the hood (if you return a statement, rather than a string), but most people are going to assume you're doing it via string manipulation, and shy away from it. If you don't like the standard '?' syntax, don't forget you can explicitly number or name your parameters. Personally I think this is the better approach anyways. Numbering the parameters explicitly also allows you to re-use them, which can be useful in complex SELECT statements. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
Hi, On Mon, Apr 27, 2009 at 8:06 PM, Vinnie wrote: > Apparently I did come up with an original idea. Because none of the wrappers > from the archives are using variable argument lists. All these wrappers are > basically doing the same thing, a very thin layer on top of SQlite. > > My goal for a wrapper was to allow, using only a single function call, all of > the parameter binds and column values to get assigned. Having a separate > function call to retrieve each column or bind each parameter isn't much > better than straight SQLite (not that I'm complaining about SQLite, it > rocks!). > > Hasn't anyone else used variable argument lists for binding parameters and > what not? The thing is, variable argument lists are not terribly popular in "modern" C++. The trend is more for function call chaining. I have seen at least one wrapper besides one that I have written that copies the idea of overloaded shift operators for formatted input/output, but applied to a database wrapper instead of the I/O stream library. (Actually, considering that the prepared statement text is effectively a format string, it would be more like copying Boost.Format.) Regards, Eugene Wee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
On 27/04/2009 10:06 PM, Vinnie wrote: >> From: Neville Franks >> Subject: Re: [sqlite] How do you guys use sqlite from C++? >> I use a modified version of the C++ wrapper >> http://www.codeproject.com/KB/database/CppSQLite.aspx > > Apparently I did come up with an original idea. > Because none of the wrappers from the archives are using variable argument > lists. > All these wrappers are basically doing the same thing, a very thin layer on top of SQlite. Maybe original to C++ wrappers. Using something like the Python DBAPI (which is more or less standard across all databases) you'd do something like this: bar_param = 42 zot_param = "Frobozz%" sql = "select * from foo where bar = ? and zot like ?" cursor.execute(sql, (bar_param, zot_param)) result = cursor.fetchall() # result is a list of tuples i.e. one tuple per row returned by the query You don't need to tell it what types the parameters are when you're using an object-oriented language ;-) And just in case you were about to say that that's inefficient because it's preparing the SQL each time: implementations are meant to save prepared statements in a cache and IIUC most do. > > My goal for a wrapper was to allow, using only a single function call, all of > the parameter binds and column values to get assigned. Having a separate > function call to retrieve each column or bind each parameter isn't much > better than straight SQLite (not that I'm complaining about SQLite, it > rocks!). > > Hasn't anyone else used variable argument lists for binding parameters and > what not? Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
> From: Neville Franks > Subject: Re: [sqlite] How do you guys use sqlite from C++? > I use a modified version of the C++ wrapper > http://www.codeproject.com/KB/database/CppSQLite.aspx Apparently I did come up with an original idea. Because none of the wrappers from the archives are using variable argument lists. All these wrappers are basically doing the same thing, a very thin layer on top of SQlite. My goal for a wrapper was to allow, using only a single function call, all of the parameter binds and column values to get assigned. Having a separate function call to retrieve each column or bind each parameter isn't much better than straight SQLite (not that I'm complaining about SQLite, it rocks!). Hasn't anyone else used variable argument lists for binding parameters and what not? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
I use a modified version of the C++ wrapper http://www.codeproject.com/KB/database/CppSQLite.aspx Monday, April 27, 2009, 8:35:43 PM, you wrote: V> I've made my own wrapper class around sqlite for executing V> database commands. Its completely generic and supports the use of V> binds and parameter substitution through the use of variable V> arguments () as well as a printf-style format string that V> clues the routine into the types of the arguments. For example: Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
On 27/04/2009 8:35 PM, Vinnie wrote: > I've made my own wrapper class around sqlite for executing database commands. > Its completely generic and supports the use of binds and parameter > substitution through the use of variable arguments () as well as a > printf-style format string that clues the routine into the types of the > arguments. For example: > > I'm pretty sure this is not an original idea You're not wrong. > so what I would like to know is, has anyone done anything similar? Or come up > with different solutions to the problem of putting a suitable wrapper around > SQlite? Or are there any third party libraries that have done something like > this? > > I would love to see other people's approach, Reading through this lot should keep you out of trouble for a while: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers > I'm hoping to pick up some ideas that will make this system better. Because > while it is a nice improvement over straight sqlite it is not without its > problems. The format string is prone to making mistakes especially when > modifying statements and adding more columns or parameters. And sometimes it > is silly seeing "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" in an > INSERT statement. Once in a while I forget to add a ? and it becomes a hard > to track bug. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do you guys use sqlite from C++?
I've made my own wrapper class around sqlite for executing database commands. Its completely generic and supports the use of binds and parameter substitution through the use of variable arguments () as well as a printf-style format string that clues the routine into the types of the arguments. For example: bRow=m_db.Select( err, &stmt, "SELECT " " NAME, " " FULLPATH, " " PARENTID " "FROM DIR " " WHERE DIRID=?;", "D,SSD", dirRid, &strName, &dirStrPath, &parentRid ); The string "D,SSD" tells the Select function about the types of the variable arguments, and binds parameters or column values as appropriate. In this example there is one 64-bit integer parameter (a row ID) and three bound columns; Two of type utf-16 string and one of type 64-bit int. I'm pretty sure this is not an original idea so what I would like to know is, has anyone done anything similar? Or come up with different solutions to the problem of putting a suitable wrapper around SQlite? Or are there any third party libraries that have done something like this? I would love to see other people's approach, I'm hoping to pick up some ideas that will make this system better. Because while it is a nice improvement over straight sqlite it is not without its problems. The format string is prone to making mistakes especially when modifying statements and adding more columns or parameters. And sometimes it is silly seeing "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" in an INSERT statement. Once in a while I forget to add a ? and it becomes a hard to track bug. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
