Re: [sqlite] Unicode Again... Sti ll Stuck... A Challenge... Store and retrieve the word résumé with out using a unicode string literal
Well, I have a solution to my own problem, and I wanted to post it for two
reasons: First, it might help someone; second, I'm wondering if someone can
explain it to me...
Here's the scoop...
I'm on a Windows machine. It turns out that the default code page on
Windows is cp437. So, in my Python code, if I type:
s = 'résumé' (with the French e s), it is stored as: 'r\x82sum\x82' because
hex 82 (decimal 130) is the code for French e in code page 437 (used by
Windows)...
OK. So, now that I now my data comes to me from the HTML form (or in a flat
file) in code page 437 on a Windows machine, I can do the following when I
send the data to the database:
f = cgi.FieldStorage()
cur.execute("insert into test values (?,?)",
(f['txtName'].value.decode('cp437')))
The decode method after the incoming form data will force a translation from
code page 437 to unicode (from 1 byte per character according to extended
ascii set code page 437 to 2 bytes per character -- unicode). That's all
fine.
Now, when I get the data with:
cur.execute("select * from test")
mylist = cur.fetchall()
I would expect that I would need to encode the unicode data coming from
Sqlite to get back to my original code page 437 (of course, I could also
just use the data as unicode). So, I would expect to do this:
(say that row one, column one has the value of résumé)
In that case, the following should return me exactly to the original
'r\x82sum\x82'
mylist[0][0].encode('cp437')
But it doesn't!!! (Wacky)!
Rather, it gives me this: 'r\xe9sum\xe9'
Interestingly, that's almost the same as what I get with a unicode literal.
In other words, if I write this Python code:
x = u'résumé'
and then type x in the shell to see what it is, I get this:
u'r\xe9sum\xe9'
The only difference is that the latter is unicode and the former
('r\xe9sum\xe9') is not.
So, to get back where I started, I do the fetchall and then this wacky
thing:
eval("u'" + mylist[0][0].encode('cp437') + "'").encode('cp437')
In other words, I say: OK, you're almost there. Now, convert to unicode by
evaluating the string as a unicode literal and then encode the unicode back
to the code page 437.
What a kludge. It seems like an awefully lot of work to get back to the
original data that was stored to the database. And why? Does anyone know
what's going on here???
Thanks.
wcmadness wrote:
>
> Surely there is an answer to this question...
>
> I'm using Python and PySqlite. I'm trying to store the word résumé to a
> text field. I'm really doing this as a test to see how to handle
> diacritical letters, such as umlaut characters (from German) or accented
> characters (from French). I can produce French é on my keyboard with
> Alt-130...
>
> If I were coding a string literal, I would send through the data as
> unicode, as in: u'résumé'. But, I'm not that lucky. The data is coming
> from an HTML form or from a flat file. It will take on the default codec
> used on my machine (latin-1). If I just send it through as is, it has
> problems either when I fetchall or when I try to print what I've fetched.
> So, for example:
>
> Insert Into tblTest (word) values ('résumé')
>
> will cause problems.
>
> I know that Sqlite stores text data as utf-8. I know that in Python (on
> my machine, at least) strings are stored as latin-1. So, for example, in
> Python code:
>
> v = 'résumé'
>
> v would be of type str, using latin-1 encoding.
>
> So, I have tried sending through my data as follows:
>
> cur.execute("Insert Into tblTest (word) values (?)",
> ("résumé".decode("latin-1").encode("utf-8"),))
>
> That stores the data just fine, but when I fetchall, I still have
> problems. Say, I select * from tblTest and then do:
>
> l = cur.fetchall()
>
> Doing print l[0][1] (to print the word résumé) will give a nasty message
> about ascii codec can't convert character \x082 (or some variation of that
> message).
>
> I've tried doing:
>
> print l[0][1].decode('utf-8').encode('latin-1')
>
> But to no avail.
>
> The simple question is this:
>
> How do I store the word résumé to a Sqlite DB without using a unicode
> literal (e.g. u'résumé'), such that printing the results retrieved from
> fetchall will not crash?
>
> Surely someone is doing this... Say you get data from an HTML page that
> contains diacritical characters. You need to store it to Sqlite and
> retrieve it back out for display. What do you do???
>
> I'm stuck!
>
> Doug
>
--
View this message in context:
http://www.nabble.com/Unicode-Again...-Still-Stuck...-A-Challenge...-Store-and-retrieve-the-word-r%C3%A9sum%C3%A9-without-using-a-unicode-string-literal-tf4190926.html#a11918870
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] strategy adding indexes
On 7/30/07, Bharath Booshan L <[EMAIL PROTECTED]> wrote: > Hi Tom, > > I have one more query regarding usage of indexes. > > > > > 2. From left to right in the same order as your index. So if you > > create index MyIndex on MyTable ( Column1, Column2, Column3 ), then > > you must test them in the same order, eg: where Column1 = Value1 and > > Column2 = Value2 or Column3 = Value3. If you miss a column in the > > sequence or place one out of order, the index won't be used from that > > point in the test onwards. > > > If I use only one of the Column i.e. Column1, Column2 or Column3 in a query > Would it still use the index MyIndex or that we need to have the 3 Columns > (in sequence) in the test in order to use the MyIndex? IIRC, the index that matches the most leftmost columns will be used. ie the index (a,b,c,d) will be used to help with queries testing either (a), (a,b), (a,b,c), or (a,b,c,d). > Regards, > > Bharath Booshan L. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to set up sqlite3 with Dev C++
Hi; I'm pretty much a newbie. I'm just about finishing a PC a game with Dev C++. I decided to use sqlite3 to load content from XML repository (for user write access) into the database and then sort and pull out the appropriate information as needed. However, I am trying to figure out SET UP sqlite3 with Dev C++ - so that I can start coding. Currently, I am trying to run the quick start program which includes The compiler doesn't find sqlite3.h I don't want to include the source code. I was hoping there was some way that I could somehow simply linke the sqlite3.dll and sqlite3.def so that I could code for my specific needs. Or do I have to include the source to get it working. Thanks in advance Stev - Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
[sqlite] Unicode Again... Still Stuck... A Challenge... Store and ret rieve the word résumé without using a unicode string literal
Surely there is an answer to this question...
I'm using Python and PySqlite. I'm trying to store the word résumé to a
text field. I'm really doing this as a test to see how to handle
diacritical letters, such as umlaut characters (from German) or accented
characters (from French). I can produce French é on my keyboard with
Alt-130...
If I were coding a string literal, I would send through the data as unicode,
as in: u'résumé'. But, I'm not that lucky. The data is coming from an HTML
form or from a flat file. It will take on the default codec used on my
machine (latin-1). If I just send it through as is, it has problems either
when I fetchall or when I try to print what I've fetched. So, for example:
Insert Into tblTest (word) values ('résumé')
will cause problems.
I know that Sqlite stores text data as utf-8. I know that in Python (on my
machine, at least) strings are stored as latin-1. So, for example, in
Python code:
v = 'résumé'
v would be of type str, using latin-1 encoding.
So, I have tried sending through my data as follows:
cur.execute("Insert Into tblTest (word) values (?)",
("résumé".decode("latin-1").encode("utf-8"),))
That stores the data just fine, but when I fetchall, I still have problems.
Say, I select * from tblTest and then do:
l = cur.fetchall()
Doing print l[0][1] (to print the word résumé) will give a nasty message
about ascii codec can't convert character \x082 (or some variation of that
message).
I've tried doing:
print l[0][1].decode('utf-8').encode('latin-1')
But to no avail.
The simple question is this:
How do I store the word résumé to a Sqlite DB without using a unicode
literal (e.g. u'résumé'), such that printing the results retrieved from
fetchall will not crash?
Surely someone is doing this... Say you get data from an HTML page that
contains diacritical characters. You need to store it to Sqlite and
retrieve it back out for display. What do you do???
I'm stuck!
Doug
--
View this message in context:
http://www.nabble.com/Unicode-Again...-Still-Stuck...-A-Challenge...-Store-and-retrieve-the-word-r%C3%A9sum%C3%A9-without-using-a-unicode-string-literal-tf4190926.html#a11918145
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: [3.3.13] UPDATE OR ROLLBACK?
At 23:20 30/07/2007 -0400, Igor Tandetnik wrote: http://sqlite.org/capi3ref.html#sqlite3_changes Makes sense. Thanks! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] strategy adding indexes
Hi Tom, I have one more query regarding usage of indexes. > 2. From left to right in the same order as your index. So if you > create index MyIndex on MyTable ( Column1, Column2, Column3 ), then > you must test them in the same order, eg: where Column1 = Value1 and > Column2 = Value2 or Column3 = Value3. If you miss a column in the > sequence or place one out of order, the index won't be used from that > point in the test onwards. If I use only one of the Column i.e. Column1, Column2 or Column3 in a query Would it still use the index MyIndex or that we need to have the 3 Columns (in sequence) in the test in order to use the MyIndex? Regards, Bharath Booshan L. --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] strategy adding indexes
Hi Tom, Thanks for that; useful to know. Didn't know about point 1 and 2 and that will complicate matters a bit further. RBS -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: 31 July 2007 00:39 To: sqlite-users@sqlite.org Subject: Re: [sqlite] strategy adding indexes Hi RBS, > - indexes that include all possible combinations of fields that may > appear > in a WHERE clause. As an aside, note that, AFAIK, indexes are only used: 1. To get the first match of a query. If you ask for more than one matching record, the second, third etc matches are found by searching, not through the index. 2. From left to right in the same order as your index. So if you create index MyIndex on MyTable ( Column1, Column2, Column3 ), then you must test them in the same order, eg: where Column1 = Value1 and Column2 = Value2 or Column3 = Value3. If you miss a column in the sequence or place one out of order, the index won't be used from that point in the test onwards. 3. In equality tests, eg "=" (equals) and "in". If you use "like" for comparison, the index isn't used. The last test (only) may be one or two inequality tests, such as ">" or "<". And that last test must be in sequence (ie rule 2). I hope this helps a bit. Some more learned SQLiters out there may care to correct or clarify. Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: [3.3.13] UPDATE OR ROLLBACK?
Gilles Ganault
wrote:
I'd like to use a timestamp column in each table to keep track of
when a column was last updated, so that a user can be notified of a
problem
when trying to updated a record that has already been updated by
another
user while the first user was still working on the original data.
In www.sqlite.org/lang_conflict.html, I read about the "UPDATE OR
ROLLBACK" instructions, so tried the following using Todd Tanner's
VBified
SQLite DLL... but am not notified of anything special: SQLite doesn't
return
any error, and just reports that no row matches:
'row already updated by someone else, so timestamp=2 -> no row
actually matches constraint
mQuery = "begin;"
mQuery = mQuery & "update OR ROLLBACK mytable set name='bart',
timestamp=2 where id=1 and timestamp=1;"
The conflict resolution clause ("OR ROLLBACK" in your case) kicks in,
unsurprisingly, when there is a conflict - e.g. when an update would
violate a uniqueness or CHECK constraint. It is a normal case, and not a
conflict, when the condition in the WHERE clause simply selects zero
rows.
See sqlite3_changes:
http://sqlite.org/capi3ref.html#sqlite3_changes
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] [3.3.13] UPDATE OR ROLLBACK?
Hello I'd like to use a timestamp column in each table to keep track of when a column was last updated, so that a user can be notified of a problem when trying to updated a record that has already been updated by another user while the first user was still working on the original data. In www.sqlite.org/lang_conflict.html, I read about the "UPDATE OR ROLLBACK" instructions, so tried the following using Todd Tanner's VBified SQLite DLL... but am not notified of anything special: SQLite doesn't return any error, and just reports that no row matches: === CODE === Private Declare Sub sqlite3_open Lib "SQLite3VB.dll" (ByVal FileName As String, ByRef handle As Long) Private Declare Function sqlite_get_table Lib "SQLite3VB.dll" (ByVal DB_Handle As Long, ByVal SQLString As String, ByRef ErrStr As String) As Variant() Private Declare Function number_of_rows_from_last_call Lib "SQLite3VB.dll" () As Long 'row already updated by someone else, so timestamp=2 -> no row actually matches constraint mQuery = "begin;" mQuery = mQuery & "update OR ROLLBACK mytable set name='bart', timestamp=2 where id=1 and timestamp=1;" mQuery = mQuery & "commit" sqlite3_open DBFile, DB If DB > 0 Then mVar = sqlite_get_table(DB, QueryStr, mErrStr) If mErrStr <> "" Then ErrStr = mErrStr sqlite3_close DB Exit Function Else 'No error + empty array! How to be notified of conflict? === CODE === Thank you G. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to cout the nandflash expire
Tank you very much, I know that all of the sqlite's operates will be parsed to vdbe code, but i don't know how can i get the map of vdbe code to file operate. Ben Combee wrote: > > On 7/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >> hello, I port the sqlite3 to linux(file system is jffs2).now , I must >> cout >> the nandflash expire in sqlite3 running. > > SQLite works on top of the file system, so it has no knowledge of what > JFFS2 and MTD are doing to manage your NAND flash. You'll have to > talk directly to the JFFS2 layer on your device and find out what's > happening to the file that stored the SQLite database. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/how-to-cout-the-nandflash-expire-tf4168923.html#a11891733 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Bitwise 'AND' issue with bound variables
Hello, I'd been looking into a bug in my application which worked down to an issue with Bitwise AND and bound variables in prepared statements it seems. The query... SELECT * FROM example WHERE (intColumn & 4294901760) = ? Where 'intColumn' is an integer column and the parameter is bound using sqlite3_bind_int() always returned zero rows. Even when that exact query returned multiple rows from management tools. I realized that AND'ing the parameter with any integer value fixed this. Eg... SELECT * FROM example WHERE (intColumn & 4294901760) = (? & 4294967295) Note that 4294967295 is equal to 0xFF and the parameter's actual value is always the same width so (? & 4294967295) should not change the parameter's value. The second query produces the results I expected but I'd like to know why the first query did not work. Does anyone have any ideas? Best regards, Kervin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE INDEX that is case insensitive?
Lower case and upper case are different, with lower case having the higher vlaue. To get case insensitive sorts do this: CREATE TABLE mytab (a TEXT COLLATE NOCASE); then SELECT a FROM mytab ODRER BY a; will give a case insensitive sorted list. Chase wrote: ok. here's a SELECT that works... SELECT foo FROM bar WHERE foo LIKE 'D%' ORDER BY upper(foo); but, how could that upper(foo) part be used with the CREATE INDEX syntax? neither of the following attemps worked (syntax errors): CREATE INDEX barfooindex ON bar upper(foo); or CREATE INDEX barfooindex ON bar(foo) ORDER BY upper(foo); at this point, i guess i'm just wanting to avoid the extra run-time overhead of running the UPPER() function on every foo returned from a select. i may just add an extra column to the table that's the upper-case version of title and index that. same net result, but it would add, of course, to the size of the db. like: foo UPPERFOO TestTEST any ideas? - chase On July 30, 2007, Chase wrote: Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here either. It searches the text case-insensitively, but it still outputs it in the "wrong" order. Keep in mind that I'm aware that the former is NUMERICALLY/TECHNICALLY in order, but I need it in "human-understood alphabetical order" (we'll call it). We ultimately will be creating an index for this column anyway, so let's just jump ahead and talk about creating an INDEX which would spit out: Da DC De Do If, however, it has nothing to do with the index and instead we should deal with this in the SELECT, that's fine. In that case, tell me what that SELECT statement would look like. Thanks. - Chase - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] strategy adding indexes
Hi RBS, - indexes that include all possible combinations of fields that may appear in a WHERE clause. As an aside, note that, AFAIK, indexes are only used: 1. To get the first match of a query. If you ask for more than one matching record, the second, third etc matches are found by searching, not through the index. 2. From left to right in the same order as your index. So if you create index MyIndex on MyTable ( Column1, Column2, Column3 ), then you must test them in the same order, eg: where Column1 = Value1 and Column2 = Value2 or Column3 = Value3. If you miss a column in the sequence or place one out of order, the index won't be used from that point in the test onwards. 3. In equality tests, eg "=" (equals) and "in". If you use "like" for comparison, the index isn't used. The last test (only) may be one or two inequality tests, such as ">" or "<". And that last test must be in sequence (ie rule 2). I hope this helps a bit. Some more learned SQLiters out there may care to correct or clarify. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How to know which rows are affected by UPDATE/DELETE?
John Stanton <[EMAIL PROTECTED]> wrote: You should take a closer look at the structure of Sqlite, in particular how it uses pages. It is not amenable to your row locking strategy. Optimistic locking doesn't lock anything per se. It's a download-modify-upload cycle where the "upload" part checks that a record being modified has not changed since being downloaded. If it has, the operation fails, and a human user is usually notified and asked to synch to the new version and try her edit again (if it still makes sense). Optimistic locking doesn't need any special support from database engine. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE INDEX that is case insensitive?
On 7/30/07, Chase <[EMAIL PROTECTED]> wrote: > Right now, when i do a select in sqlite that is supposed to be in > alphabetical order, i get: > > DC > Da > De > Do > We ultimately will be creating an index for this column anyway, so > let's just jump ahead and talk about creating an INDEX which would spit > out: > > Da > DC > De > Do You can use the COLLATE clause when creating the index (or the table column, or with ORDER BY in a query). See http://sqlite.org/datatype3.html#collation for available collations. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Changing Database Encoding
Wow, it looks like I really did a number on these! I found iconv for Windows and have integrated it into the conversion process... NOw to tackled the Unicode handling (or non-handling) of the SQLite ODBC driver.. This will sure teach me to go around changing things! On 7/30/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: > On 7/30/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > I've read a few places that it is not possible to change the encoding > > of a database once it's created. Is it possible to do it in some > > automated way with any of the command line utilities? > > Read about the "pragma encoding" [1] SQL command (you just need to use > it before inserting any data). Note that the "encoding" is always > UNICODE, so you don't gain much with this pragma if you're from the > "western" part of the world > > > I converted a database from SQLite 2.X to 3.X using sqlite.exe's .dump > > function and apparently didn't set the encoding correctly as any > > non-English text isn't accessible. I think I need to set the encoding > > to UTF8 now, though it "just worked" before, so I'm not sure what I > > accidentally did right the first time :-) > > I don't think that is your problem. You were probably using a 2.x > database in "8-bit" mode, not UTF-8. Things can get ugly if you used > the UTF-8 library version on non-UTF-8 strings. > > The only solution I can see is to use something like "iconv" to > translate the dump to UTF-8 before inserting the data into a 3.x > database. That can be more difficult than you think in case of > mismatched use of library versions. > > Regards, > ~Nuno Lucas > > > > > Any help is appreciated! Thanks! > > > > -- > > - Mitchell Vincent > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE INDEX that is case insensitive?
ok. here's a SELECT that works... SELECT foo FROM bar WHERE foo LIKE 'D%' ORDER BY upper(foo); but, how could that upper(foo) part be used with the CREATE INDEX syntax? neither of the following attemps worked (syntax errors): CREATE INDEX barfooindex ON bar upper(foo); or CREATE INDEX barfooindex ON bar(foo) ORDER BY upper(foo); at this point, i guess i'm just wanting to avoid the extra run-time overhead of running the UPPER() function on every foo returned from a select. i may just add an extra column to the table that's the upper-case version of title and index that. same net result, but it would add, of course, to the size of the db. like: foo UPPERFOO TestTEST any ideas? - chase On July 30, 2007, Chase wrote: Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here either. It searches the text case-insensitively, but it still outputs it in the "wrong" order. Keep in mind that I'm aware that the former is NUMERICALLY/TECHNICALLY in order, but I need it in "human-understood alphabetical order" (we'll call it). We ultimately will be creating an index for this column anyway, so let's just jump ahead and talk about creating an INDEX which would spit out: Da DC De Do If, however, it has nothing to do with the index and instead we should deal with this in the SELECT, that's fine. In that case, tell me what that SELECT statement would look like. Thanks. - Chase - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE INDEX that is case insensitive?
It is in correct order. You might try COLLATE NOCASE to force an uper case only sort. Chase wrote: Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here either. It searches the text case-insensitively, but it still outputs it in the "wrong" order. Keep in mind that I'm aware that the former is NUMERICALLY/TECHNICALLY in order, but I need it in "human-understood alphabetical order" (we'll call it). We ultimately will be creating an index for this column anyway, so let's just jump ahead and talk about creating an INDEX which would spit out: Da DC De Do If, however, it has nothing to do with the index and instead we should deal with this in the SELECT, that's fine. In that case, tell me what that SELECT statement would look like. Thanks. - Chase - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to know which rows are affected by UPDATE/DELETE?
You should take a closer look at the structure of Sqlite, in particular how it uses pages. It is not amenable to your row locking strategy. Gilles Ganault wrote: Hello To write a front-end server to SQLite. To avoid locking the whole database, I'd like to implement optimistic locking, but for this to work, I need to use a timestamp and know which tables + records are affected when a user sends a query that changes the database (UPDATE, DELETE). I guess the timestamp mechanisme can be achieved through a three-column database: table_name, row_id, timestamp, so that, before making any change, the server can check if any row has already been changed by another user while the current user was still working. Is there a way to know which rows will be changed by a query like this? UPDATE suppliers SET name = 'HP' WHERE name = 'IBM'; Thank you G. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extremely new to SQLite
Thanks James, But everything is still foggy to me. Could you show me some example syntax that accomplishes the following. Thanks again, Rahul. James Dennett wrote: -Original Message- From: Rahul Banerjee [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 1:34 PM To: sqlite-users@sqlite.org Subject: [sqlite] Extremely new to SQLite Hi, I'm trying to integrate SQLite into a library management system coded in C++. I'm extremely new to SQLite and the documentation at http://www.sqlite.org didn't do it for me. Can anyone give me some help/tips. All I need to do is: 1. Access db See the docs on sqlite3_open() for getting a handle to a database. 2. Retrieve data from a particular row and column (and store it into a var) I'd recommend using sqlite3_prepare_v2 to prepare the statement, the various sqlite3_bind_*() functions to bind variables, and then sqlite3_step() to execute it. You'll prepare something lke "select col1 from table2 where col2=:1" (or one of various placeholder syntaxes supported by SQLite3). Obviously you'll need some understanding of SQL in general, which is largely not specific to SQLite. 3. Write/Modify data into db Pretty much the same as selecting data, but with different SQL. 4. Save and exit db You don't "save"; you "commit;" your transaction, if you started one. If you didn't start one explicitly, there's nothing to do; the library will commit after each statement. That's the joy of Durability in ACID. -- James - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CREATE INDEX that is case insensitive?
Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here either. It searches the text case-insensitively, but it still outputs it in the "wrong" order. Keep in mind that I'm aware that the former is NUMERICALLY/TECHNICALLY in order, but I need it in "human-understood alphabetical order" (we'll call it). We ultimately will be creating an index for this column anyway, so let's just jump ahead and talk about creating an INDEX which would spit out: Da DC De Do If, however, it has nothing to do with the index and instead we should deal with this in the SELECT, that's fine. In that case, tell me what that SELECT statement would look like. Thanks. - Chase - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] strategy adding indexes
What would be a good strategy in adding indexes to the various tables? I know SQLite can only use one index in simple (not intersect etc.) queries, so is it usually best to make: - indexes that include all possible combinations of fields that may appear in a WHERE clause. - make one very large index combining all fields that may appear in a WHERE clause. Or would it be better to make single field indexes and go with intersect etc? I understand that in the end it will come down to a lot of experimenting, but maybe there are some useful guidelines/rules that speed up this large task. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Changing Database Encoding
On 7/30/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I've read a few places that it is not possible to change the encoding > of a database once it's created. Is it possible to do it in some > automated way with any of the command line utilities? Read about the "pragma encoding" [1] SQL command (you just need to use it before inserting any data). Note that the "encoding" is always UNICODE, so you don't gain much with this pragma if you're from the "western" part of the world > I converted a database from SQLite 2.X to 3.X using sqlite.exe's .dump > function and apparently didn't set the encoding correctly as any > non-English text isn't accessible. I think I need to set the encoding > to UTF8 now, though it "just worked" before, so I'm not sure what I > accidentally did right the first time :-) I don't think that is your problem. You were probably using a 2.x database in "8-bit" mode, not UTF-8. Things can get ugly if you used the UTF-8 library version on non-UTF-8 strings. The only solution I can see is to use something like "iconv" to translate the dump to UTF-8 before inserting the data into a 3.x database. That can be more difficult than you think in case of mismatched use of library versions. Regards, ~Nuno Lucas > > Any help is appreciated! Thanks! > > -- > - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Changing Database Encoding
I've read a few places that it is not possible to change the encoding of a database once it's created. Is it possible to do it in some automated way with any of the command line utilities? I converted a database from SQLite 2.X to 3.X using sqlite.exe's .dump function and apparently didn't set the encoding correctly as any non-English text isn't accessible. I think I need to set the encoding to UTF8 now, though it "just worked" before, so I'm not sure what I accidentally did right the first time :-) Any help is appreciated! Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to cout the nandflash expire
On 7/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > hello, I port the sqlite3 to linux(file system is jffs2).now , I must cout > the nandflash expire in sqlite3 running. SQLite works on top of the file system, so it has no knowledge of what JFFS2 and MTD are doing to manage your NAND flash. You'll have to talk directly to the JFFS2 layer on your device and find out what's happening to the file that stored the SQLite database. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unicode
On 7/30/07, wcmadness <[EMAIL PROTECTED]> wrote: > I'm stuck on this. I'm writing a data layer that potentially needs to handle > diacritical (sp?) characters, such a French accented é characters or German > umlauted characters (sp?). It should be rare that I would run into > something like this, but the data layer should handle it nevertheless. For > example, it would certainly be expected to handle something as simple as the > word résumé or the name Réggé. > > I've tried quite a few things now, and I just can't get to a solid solution. > The data gets stored to Sqlite, but when I try to select it, I have > problems. Here's a sample of the error I get from the Python shell trying > to select data with accented characters: This is probably not related to the SQLite library itself. You should talk to the author(s) of the Python bindings and/or look at your own code. I do a lot of coding in Perl (accessing SQLite via DBD::SQLite) and have no problems with Latin-1 or Unicode. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to cout the nandflash expire
hello, I port the sqlite3 to linux(file system is jffs2).now , I must cout the nandflash expire in sqlite3 running. -- View this message in context: http://www.nabble.com/how-to-cout-the-nandflash-expire-tf4168923.html#a11860604 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
