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, , "SELECT " " NAME, " " FULLPATH, " " PARENTID " "FROM DIR " " WHERE DIRID=?;", "D,SSD", dirRid, , , ); 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++?
> 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] Newbie question about using SQLite with Windows Forms application (VS 2005 C++)
> From: wiktor> Subject: [sqlite] Newbie question about using SQLite with > Windows Forms application (VS 2005 C++) > I'm trying to build a win form application that uses > sqlite. I have problems with making it work. I would like > to have the sqlite source included in my project (as .h file > or dll) - sth similar to (but done by a function) > http://www.sqlite.org/quickstart.html. As I have read on > internet sources it shall be possible. I have never used Windows Forms but from what I understand it is a user interface toolkit for .NET. So you will need to access SQLite from .NET. There are a few ways of going about this. Here is one .NET wrapper for SQLite: http://www.phpguru.org/static/SQLite.NET.html The SQLite website has some instructions for building SQLite with Visual Studio .NET: http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Hope this helps! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] converting sqlite db into xml
> From: candd> I want to have a sample C source for a program that convert > an sqlite > data base file to xml file. > could you help me please! Thats a pretty broad and open ended question. What exactly are you trying to accomplish here? Do you want to produce enough XML data so that you can re-constitute the database at a later date? Or did you want something like mapping the table schemas into XML templates, and then producing document instances that represent existing rows using that schema? Or something else entirely? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit to database and/or blob size on Mac/Windows?
> From: John Machin> Irrespective of what people tell you and how authoritative > they seem, I > would recommend that you do some simple tests: Excellent advice! Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite database to xml converter??
> From: candd> > Dear All! > > I am new user of sqlite3 > I want to have a sample C source for a program that convert > an sqlite > data base file to xml file. > could you help me please! Hi and welcome to the group. Your problem is very easy to solve, just rename your database file to have the extension ".xml" and you should be good to go. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Limit to database and/or blob size on Mac/Windows?
Dear Group: I've done some calculations and its a fairly likely scenario that my users will end up with sqlite databases that are over 1 gigabyte in size, in some cases 4 gigabytes. An upper limit on the number of rows in a table could be as high as 100,000 (yeah that not very high). There are rows containing blobs that average around 50 kilobytes in size. Is there a limit to the database size on Windows or Macintosh? I did a search and the only thing I came up with was that large file support was enabled for Unix in one of the releases. I'm looking at sqlite.c from the amalgamation and it says that >2GB file support is enabled on POSIX if the underlying OS supports it. And "Similar is true for Mac OS X". But there is no mention of Windows. Anyone? Thanks! Sincerely, Vinnie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Whoops! Huge misunderstanding of multi-threaded database
So I'm getting SQLITE_BUSY now. I have one thread inserting rows while another thread tries to read a row from the same table. I had a bad implementation where I was keeping the transaction open far longer than necessary so I think I went over some 5 second rule? Does SQLite wait up some length of time before returning a "busy" error? How can I tell SQLite to wait forever? Is this something desirable? Its not convenient for me to check for a busy result in every line of code that makes a database call. In all cases I would want to re-execute the statement over and over again until it goes through. I was under the impression that SQLite would simply block until the other operations completed. How do you get this behavior? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA doesn't support parameter binds?
Sorry for only posting when I have a problem...but... I'm doing PRAGMA user_version=?; And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). Of course I can't step into the sqlite3.c code because the Visual Studio 2008 debugger gets hopelessly confused when confronted with a file whose line number representations exceed the capacity of an unsigned 16 bit integer. So I check the syntax diagram and a pragma-value only has { signed-number, name, string-literal } as choices. Whereas an "expression" in the syntax diagram (used in a SELECT statement for example) has { ..., bind-parameter, ... }. I would prefer to use parameter binds to keep the number of functions in my wrapper down (and eliminate the need for a printf-style API to sqlite3) so can anyone confirm or deny that parameter binds do in fact not work for PRAGMA statements? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections from different threads
Sorry for asking such a basic question, and it seems I know the answer but I would like a confirmation. If I am executing the same SQL statement from multiple database handles to the same database file, I still need to prepare a distinct sqlite3_stmt for each connection, even though the SQL statement is the same and the database is the same. It seems that the database handle is "bound" to the statement, and there is no way to specify which database you want to use after the statement has been prepared. Right? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UTF-16 API a second class citizen?
Wow man that is the page I've been looking for my whole life but didn't know it...finally an explanation for this mess. > From: "Igor Tandetnik" >> "The Absolute Minimum Every Software Developer > Absolutely, Positively > Must Know About Unicode and Character Sets (No > Excuses!)" > http://www.joelonsoftware.com/articles/Unicode.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF-16 API a second class citizen?
> Note that both UTF-8 and UTF-16 are capable of representing > the full range of Unicode characters. Conversion between the two is > lossless. You seem to be under impression that UTF-8 is somehow > deficient, only suitable for "legacy" encoding. This is not the > case. Yeah thats what they say...but if thats the case then why use UTF-16 at all? What is the benefit for supporting UNICODE? Why is there UTF-16 support in SQLite? To be honest, thinking about character encodings gives me a large headache even though I've been programming for decades. I figured that supporting wide characters will be somehow beneficial for international users...I hope I was not mistaken but it was not a small amount of extra work. Although I have (hopefully) written everything to work with narrow characters by flipping a switch. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF-16 API a second class citizen?
PRAGMA statements, I see what you mean now. This is exactly what I needed, thanks a lot. To clarify what I am doing, my SQL statements are in UTF-8 and they are all prepared, with parameter bindings. So table names, column names, etc.. are all UTF-8. However, I have table fields which will be UTF-16. For example, filenames that have to support international character sets. Or metadata fields that use different character sets (UNICODE). For these I am using sqlite3_bind_text16() and passing an appropriate wchar_t buffer. On the other hand there is some legacy data that I want to store using UTF-8. For these fields I will use sqlite3_bind_text(). It is possible that in a single INSERT statement there are both UTF-16 and UTF-8 (wchar_t and char) fields present. At no point am I ever constructing SQL statements using a printf() style conversion on field data to create the statement. Am I vulnerable to a performance penalty because of conversions in this scenario? Thanks > Igor Tandetnik wrote: > > You can mix and match encodings in your application. > The database > > encoding determines how strings are actually stored in > the file (and > > it's database-wide, not per table). SQLite API > converts back and forth > > as necessary. > > > Very inneficiently, but yes, it does. I suggest to the OP > to use > parameterised queries if you need to use string values, > otherwise, > you'll see significant overhead from conversions back > and forth between > utf8 and utf16 inside the sqlite code. > > Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF-16 API a second class citizen?
> From: "Igor Tandetnik"> You could convert your file name from UTF-16 to UTF-8, then > call sqlite3_open_v2. Converting the file name is no problem. But I thought that depending on how you opened the database (open16 versus open_v2), SQL treats your strings differently. I don't care about the encoding used to pass the filename, I care about the strings in my table rows. Or does the encoding for the file name used to open the database not matter to subsequent SQLite SQL statements? Can I mix and match UTF-8 and UTF-16 in a table or across multiple tables? > See PRAGMA user_version > (http://sqlite.org/pragma.html#version) - it's > designed specifically to do this sort of thing. Yes I see, thank you very much. This is exactly what I am already trying to with my VERSION table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UTF-16 API a second class citizen?
Dear Group: When my application launches I want to open the associated database, and if that fails because the file does not exist then I would create a new database. sqlite3_open_v2() is ideal for this purpose because you can leave out SQLITE_OPEN_CREATE flag, and specify SQLITE_OPEN_READWRITE. Unfortunately, this is all academic because I am using sqlite3_open16()! Where is the UTF-16 version that accepts the flags as a parameter? How can I achieve the same functionality? Let me add that I am not too keen on modifying sqlite.c so thats not an option (too much hassle when new versions come out). How did this oversight happen? And what is the workaround? How can I tell, after a call to sqlite3_open16() if the database was created? The first thing I do when it opens is SELECT * FROM VERSION to see if I have to upgrade the database to a new version of my application data. I guess that call could fail and that would be my clue to create all the tables. But what if the SELECT fails for a different reason? How do I distinguish it? How do I make this robust? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating unique data takes many hours, help
Michael: While my answer isn't strictly limited to sqlite, the performance nut in me can't help myself. You have a a table with only one column, a string. I could be wrong here but it seems like you just want to keep a list of values that you have already tried. After you insert a bunch of strings into the table you want to be able to quickly look up a string to see if it exists, so that you can tell if you already probed that sequence (taking a guess here). If my guess is right, and the only thing you are doing is looking up sorted single-column elements, you probably can get away with your own quick disk-based binary tree implementation and avoid sqlite for this particular circumstance altogether. The result would be several orders of magnitude faster, even after you have followed the suggestions others have given. > Hi, > > I am new with sqlite, and I create a program that reads > several mllion > records and puts them into a sqlite db using. > > The table has one column ONLY indexed and unique, but it > takes many hours. > > Is it necessary to pre-allocate the space, or is the > anything that I can > do to reduce the time it takes. > > this is how I create the db, table and index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Source code position out of sync debugging in VS 2008
I have added sqlite.c to my Visual Studio 2008 project and everything seems to be working in terms of database calls. However, when I step into an actual sqlite routine using the debugger, the source code position is out of sync with the actual location. For example, I step into sqlite3_open_v2() and it takes me to a completely unrelated source code line. I've already tried rebuilding everything, checked the settings, etc... but nothing seems to help. I get the feeling this is a problem with the file being so large and containing so many symbols. Has anyone else experienced this problem? Thanks My amalgamation was created on 2009-02-17 21:53:46 UTC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users