Re: [sqlite] How do you guys use sqlite from C++?

[Vinnie]

> > 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++?

[Vinnie]

> 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++)

[Vinnie]

> 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

[Vinnie]

> 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?

[Vinnie]

> 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??

[Vinnie]

> 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?

[Vinnie]

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

[Vinnie]

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?

[Vinnie]

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

[Vinnie]

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?

[Vinnie]

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?

[Vinnie]

> 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?

[Vinnie]

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?

[Vinnie]

> 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?

[Vinnie]

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

[Vinnie]

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

[Vinnie]

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