[sqlite] "datatype mismatch" error
I'm trying to insert some UTF-16 into my database but I'm getting a "datatype mismatch" error when I call sqlite3_step() after i bind the string with sqlite3_bind_text16(). anyone know why this would happen?
Re: [sqlite] How are NULL values deleted?
Hello Vladimir and Ulrik, On Oct 28, 2004, at 01:01, Vladimir Vukicevic wrote: You need to use "IS NULL" as opposed to "= NULL". Thanks so much. Works like a charm :-) Regards, -- Tito
Re: [sqlite] How are NULL values deleted?
You need to use "IS NULL" as opposed to "= NULL". - Vlad On Thu, 28 Oct 2004 00:55:39 +0200, Tito Ciuro <[EMAIL PROTECTED]> wrote: > Hello, > > I'd like to remove all rows with a specific column equaling NULL. I've > tried this: > > SQLite version 3.0.8 > Enter ".help" for instructions > sqlite> select * from address; > Ciuró|1|Javi||España > Garaicoechevarria|2|Ana||España > Ciuró|3|Tito||España > Miti|4|Sam||Italy > Schmuck|5|Joe||Germany > sqlite> delete from address where ZIP = NULL; > > sqlite> select * from address; > Ciuró|1|Javi||España > Garaicoechevarria|2|Ana||España > Ciuró|3|Tito||España > Miti|4|Sam||Italy > Schmuck|5|Joe||Germany > > sqlite> delete from address where ZIP = 'NULL'; > sqlite> select * from address; > Ciuró|1|Javi||España > Garaicoechevarria|2|Ana||España > Ciuró|3|Tito||España > Miti|4|Sam||Italy > Schmuck|5|Joe||Germany > > Since all rows have NULL for ZIP, I would expect to remove all rows, > but I cannot figure it out... > > Any ideas? > > Thanks, > > -- Tito >
[sqlite] How are NULL values deleted?
Hello, I'd like to remove all rows with a specific column equaling NULL. I've tried this: SQLite version 3.0.8 Enter ".help" for instructions sqlite> select * from address; Ciuró|1|Javi||España Garaicoechevarria|2|Ana||España Ciuró|3|Tito||España Miti|4|Sam||Italy Schmuck|5|Joe||Germany sqlite> delete from address where ZIP = NULL; sqlite> select * from address; Ciuró|1|Javi||España Garaicoechevarria|2|Ana||España Ciuró|3|Tito||España Miti|4|Sam||Italy Schmuck|5|Joe||Germany sqlite> delete from address where ZIP = 'NULL'; sqlite> select * from address; Ciuró|1|Javi||España Garaicoechevarria|2|Ana||España Ciuró|3|Tito||España Miti|4|Sam||Italy Schmuck|5|Joe||Germany Since all rows have NULL for ZIP, I would expect to remove all rows, but I cannot figure it out... Any ideas? Thanks, -- Tito
Re: [sqlite] a bunch of questions about SQLite from new user
Vladimir, Thank you for your responses. One short followup: A. what happens to the zSql? Is it copied or it is assumed that this is a static text? Suppose I call sqlite3_prepare in one place and immediately release dynamically allocated text. sqlite3_prepare parses only the first statement. Then I call sqlite3_step as many times as needed. Will it crash when trying to parse next statements in next steps? zSql is parsed and turned into an internal VM bytecode for later execution; you're under no obligation to keep zSql around after prepare returns. This would suggest that the whole string is parsed even if it contains more than one statement. According to documentation on sqlite3_prepare(): "This routine only compiles the first statement in zSql, so *pzTail is left pointing to what remains uncompiled." B. Is pzTail of any use? I can see that inside the library code it is checked against NULL before assigning the output text pointer but the documentation does not state explicitly that this parameter is optional. I don't want to rely on internal implementation (which may change in future release) when the docs don't say it is optional. It would be nice to clarify this. It's useful if you're reading SQL from the user, and you get, say, two INSERT commands in one string. pzTail will let you call prepare/step/etc. multiple times to evaluate all the sql. It would be good to explicitly state that it may be NULL. I don't understand something. Suppose you have 2 statements in the SQL string. Do you have to call sqlite3_prepare() 2 times? I was under impression that sqlite3_step() will do it for me if I call it multiple times. I tried to find some sample code via google and I did not see a single example of calling sqlite3_prepare() multiple times for one SQL string. Thanks in advance, Tom Abracode http://www.abracode.com
[sqlite] SQLite & Palm???
Hi, I'm quite new to both Palm programming and SQLite, and I am planning to write a calendar application that collaborates with calendar application a college of mine has written: http://www.tnt.uni-hannover.de/~pahl/calendar/overview.html (sorry, web site only in German) The calendar data is stored in a mySQL database, and it's possibel to copy this database to a SQLite database on your laptop or PocketPC. Unfortunately, there is no synchronization for Palm abvailable. I have read, that there is a Palm port of SQLite available, and I downloaded SqlitePalm from http://jalil.org/sqlite/SqlitePalm.zip Unfortunately, I am not able to compile it. Is there some documentation on how to get SQLite running and on how to use it on a Palm OS device? Acutally, I have a Palm m500 with Palm OS 4.0.1 and I am using prc-tools on my linux box. Torsten
RE: [sqlite] Index Usage
Great Question! I am eager to hear the response! I use a ton of JOINs and INTERSECTs. Coverage on that topic would be great too! -Original Message- From: William Hachfeld [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 9:34 AM To: [EMAIL PROTECTED] Subject: [sqlite] Index Usage Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform the following query: SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; on a large number of rows (say around one million) for some group 'g' and an interval '[x, y)'. And, of course, with the assumption that (end > begin) for all rows. Will my query performance be substantially improved by creating an index such as: CREATE INDEX MultiColumnIndex ON Example (grp, begin, end) or will the operators "<" and ">=" prohibit SQLite from using the index? Also, I'm aware that SQLite supports multi-column indicies, but not the use of multiple indicies per query. Is it possible to get around the later restriction by expressing my above query using a sub-select: SELECT id FROM (SELECT * FROM Example WHERE grp=g) WHERE x < end AND y >= begin; and then creating the following indicies instead: CREATE INDEX GroupIndex ON Example (group) CREATE INDEX IntervalIndex ON Example (begin, end) And if so, can any generalizations be made regarding the performance of using the two indicies versus the first, single, index? How about disk usage? Thanks in advance for any information regarding the above! -- William Hachfeld
Re[2]: [sqlite] Index question
On Wed, 27 Oct 2004, Taka Muraoka wrote: >UP> Hi Taka, > >>> >>> These are stored in tables like this: >>> >>> CREATE TABLE object >>> ( >>> object_id INTEGER PRIMARY KEY >>> ) ; >>> >>> CREATE TABLE item >>> ( >>> object_id INTEGER , >>> seq_no INTEGER >>> ) ; >>> >>> We retrieve items for a given object using this: >>> >>> SELECT * FROM item >>> WHERE object_id = 12345 >>> ORDER BY seq_no ; >>> >>> To speed up this up, I want to create an index on the item table. Am I >>> better off creating it on the object_id column alone or both object_id >>> and seq_no? What's the difference if I create a primary key on the item >>> table using object_id/seq_no? > >UP> If there is more than one row with the same object_id, you cannot use >UP> object_id as a primary key for the item table. Primary keys need to be >UP> unique for the table in question, so in your case, if you wish to use a >UP> primary key, you must use both object_id AND seq_no for the item table. > >Yes, there may be more than one row in the item table with the same >object_id. I'm asking which of these is better (i.e. faster): > >- no primary key but an index on object_id Depends on how many items you'll have per object_id. If it's a small number, then you could just use object_id for index and sort the items result set. Would make inserts and updates quicker than the indexes below. >- no primary key but an index on object_id *and* seq_no >- a primary key of object_id and seq_no but no index. These two are probably similar, both in performance and implementation. One of these is probably your best bet, I'd say the first. >- a primary key of object_id and seq_no and an index on object_id. Not worth bothering. The index of (object_id,seq_no) can be used for index searches for just object_id, I believe. SQLite will also only use a single index per table when optimizing. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
