Re: [sqlite] UNICODE Support
On 8/4/06, Trevor Talbot <[EMAIL PROTECTED]> wrote: On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: > But, since you brought it up - I have no expectations of SQLite > integrating a full Unicode locale library, however it would be a great > improvement if it would respect the current locale and use wcs* > functions when available, or at least order by standard Unicode order > instead of completely mangling things on UTF-8 codes. What do you mean by "standard Unicode order" in this context? Convert UTF-8 to UTF-16 (or both to UCS-4 if you want to be entirely correct) while sorting, to at least make them follow the same pattern. -- Cory Nelson http://www.int64.org
Re: [sqlite] UNICODE Support
On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: But, since you brought it up - I have no expectations of SQLite integrating a full Unicode locale library, however it would be a great improvement if it would respect the current locale and use wcs* functions when available, or at least order by standard Unicode order instead of completely mangling things on UTF-8 codes. What do you mean by "standard Unicode order" in this context?
Re: [sqlite] UNICODE Support
On 8/5/06, Cory Nelson <[EMAIL PROTECTED]> wrote: On 8/4/06, Nuno Lucas <[EMAIL PROTECTED]> wrote: > On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: > > IE, using memcmp() to compare strings. I've been bitten by this > > before, with SQLite producing unexpected results when using UTF-8. > > Using UTF-16 has worked more reliably in my experience. > > SQLite only knows how to sort ASCII, so memcmp does that right (being > it UTF-8 or UTF-16). > > If you think about it, the only way sorting will work 100% is by > having some form of localization (because for each language different > sorting rules apply, _even_ for words composed only of ASCII > characters). > > Adding localization to SQLite is out of the question (it would > probably need a library as big as SQLite itself), so it's up to the > user to define it's own localization funtions and integrate them with > sqlite (there are all the necessary hooks ready for that). I was not talking about sorting in my post - I've had simple = index comparisons fail in UTF-8. You should have reported it. If it's true, it's a bug that needs to be corrected. But again I would say I never found a bug like that in sqlite. But, since you brought it up - I have no expectations of SQLite integrating a full Unicode locale library, however it would be a great improvement if it would respect the current locale and use wcs* functions when available, or at least order by standard Unicode order instead of completely mangling things on UTF-8 codes. For it to respect the current locale then the database would be invalid after moving/using it in another locale (the affected indexes would need to be rebuilt). Using the COLATE thing (which I never used exactly because of the problem above) you can define your own sort function that does what you want. On the second point, you may be right and can be considered a bug. A sorted table should have exactly the same order either if the database is using UTF-8 or UTF-16 internally (even if it doesn't follow the UNICODE order). At least it seems consistency on a query result should be assured on this. Maybe others have another point of view... Regards, ~Nuno Lucas
Re: [sqlite] UNICODE Support
On 8/4/06, Nuno Lucas <[EMAIL PROTECTED]> wrote: On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: > IE, using memcmp() to compare strings. I've been bitten by this > before, with SQLite producing unexpected results when using UTF-8. > Using UTF-16 has worked more reliably in my experience. SQLite only knows how to sort ASCII, so memcmp does that right (being it UTF-8 or UTF-16). If you think about it, the only way sorting will work 100% is by having some form of localization (because for each language different sorting rules apply, _even_ for words composed only of ASCII characters). Adding localization to SQLite is out of the question (it would probably need a library as big as SQLite itself), so it's up to the user to define it's own localization funtions and integrate them with sqlite (there are all the necessary hooks ready for that). I was not talking about sorting in my post - I've had simple = index comparisons fail in UTF-8. But, since you brought it up - I have no expectations of SQLite integrating a full Unicode locale library, however it would be a great improvement if it would respect the current locale and use wcs* functions when available, or at least order by standard Unicode order instead of completely mangling things on UTF-8 codes. Regards, ~Nuno Lucas -- Cory Nelson http://www.int64.org
Re: [sqlite] UNICODE Support
On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: IE, using memcmp() to compare strings. I've been bitten by this before, with SQLite producing unexpected results when using UTF-8. Using UTF-16 has worked more reliably in my experience. SQLite only knows how to sort ASCII, so memcmp does that right (being it UTF-8 or UTF-16). If you think about it, the only way sorting will work 100% is by having some form of localization (because for each language different sorting rules apply, _even_ for words composed only of ASCII characters). Adding localization to SQLite is out of the question (it would probably need a library as big as SQLite itself), so it's up to the user to define it's own localization funtions and integrate them with sqlite (there are all the necessary hooks ready for that). Regards, ~Nuno Lucas
Re: Re: [sqlite] date data types
On 8/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: SQLite never has to figure out if the value in a field is a date or a time. There is no way for it to determine this. Woops. I guess I thought it did. Sorry for the confusion.
Re: [sqlite] date data types
On Fri, 04 Aug 2006 14:42:01 +0200, you wrote: >[EMAIL PROTECTED] wrote: [...] >>And it would go against the >>basic philosophy of SQLite. >> >> >in which way? > >but as far as I am concerned, I'll be happy if I can get the original >type declaration for the column by querying the database. is there a >way to do so? I've looked into the doc (for example, >"information_schema") but I found nothing which was working for me... Will PRAGMA table_info(tablename); do? >thanks and regards, >MF -- ( Kees Nuyt ) c[_]
Re: [sqlite] date data types
"Will Leshner" <[EMAIL PROTECTED]> wrote: > On 8/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > Adding DATE and TIMEINTERVAL types to SQLite would require an > > incompatible file format change. And it would go against the > > basic philosophy of SQLite. > > I wonder if it might not be useful to be able to ask SQLite if a value > is a date or time. I'm assuming that at some point SQLite has to > figure out if a value in a field is a date or time, and perhaps that > logic could be exposed as an API call somehow. > SQLite never has to figure out if the value in a field is a date or a time. There is no way for it to determine this. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] UNICODE Support
On 8/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Cory Nelson" <[EMAIL PROTECTED]> wrote: > On 8/3/06, RohitPatel <[EMAIL PROTECTED]> wrote: > > I recommend using utf-16 in the database - sqlite doesn't fully > support utf-8, and some things may give unexpected results if you use > it. > Oh really? What exactly is missing from SQLite's UTF-8 support? Correct me if I'm wrong but from what I understand SQLite supports storing and converting between UTF-8 and UTF-16, but that is where the support stops. It is wrong (in my opinion) to claim UTF-8 support, at least without a clear upfront warning, when that's all it offers. IE, using memcmp() to compare strings. I've been bitten by this before, with SQLite producing unexpected results when using UTF-8. Using UTF-16 has worked more reliably in my experience. -- D. Richard Hipp <[EMAIL PROTECTED]> -- Cory Nelson http://www.int64.org
Re: [sqlite] date data types
On 8/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. And it would go against the basic philosophy of SQLite. I wonder if it might not be useful to be able to ask SQLite if a value is a date or time. I'm assuming that at some point SQLite has to figure out if a value in a field is a date or time, and perhaps that logic could be exposed as an API call somehow.
Re: [sqlite] RE: UNICODE Support
On 8/3/06, Cory Nelson <[EMAIL PROTECTED]> wrote: I recommend using utf-16 in the database - sqlite doesn't fully support utf-8, and some things may give unexpected results if you use it. As with others who have replied, I have not had a problem working with UTF8 in a SQLite database.
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)
Alexander Lamb <[EMAIL PROTECTED]> wrote: > Well, I am afraid it didn't work. > > Somehow, the legacy_file_format info is not "sticky". > The "legacy_file_format" pragma does not appear to be sticky, but it is. The value reported back by PRAGMA legacy_file_format is incorrect. But the legacy file format did get set. Mario Frasca <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > >Adding DATE and TIMEINTERVAL types to SQLite would require an > >incompatible file format change. > > > well, yes, that was already clear. but: where is the type of the data > being stored? aren't there a few spare bits to use for 'future > additions', that is, new data types? sure, a file containing date data > would not be understood by executables where this has not been defined, > but maybe it is possible to do it so that they see a 'text'... or maybe > not... > Mario: Look back over this thread, and others before it, and observe all the grief that gets caused by file format changes. I've learned my lesson: No more file format changes except too fix a serious bug. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull, still)
Alexander Lamb wrote: Well, I even tried to insert a row in the table. The table contains the data. The file on the disk is 2K length. The PRAGMA commands tells me I am in legacy file format mode (1) but when I quit then return, I am back to 0! I just tried the same thing and got the same result. It is possible that the only problem is that the pragma is not returning the expected result, but is in fact changing the file format when used as directed. Have you tried copying the file to your Mac to see whether it can be read? Gerry, admittedly grasping at straws
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull, still)
Alexander Lamb uttered: Well, sorry, but I did exactly that: 1) When I do the PRAGMA command the file is created (I can see it appearing in the explorer) 2) After the create table, if I do PRAGMA legacy_file_format; I indeed receive "1" as an answer. However, I ".quit" then go back into the database and ask PRAGMA legacy_file_format; it returns "0". Once I quit, it "reverts" to 0?? The problem is that the file created is zero length until actual data (or meta-data) is inserted. Thus, you need to at least create a table. Then, once some data has been created, the header will contain the correct legacy_file_format information. Well, I even tried to insert a row in the table. The table contains the data. The file on the disk is 2K length. The PRAGMA commands tells me I am in legacy file format mode (1) but when I quit then return, I am back to 0! Could it be a wrong version of SQLite? When I ask for the version I get 3.3.6 I simply installed the .exe and .dll from the Windows package I found on the web site. Alex
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull, still)
Alexander Lamb uttered: Well, sorry, but I did exactly that: 1) When I do the PRAGMA command the file is created (I can see it appearing in the explorer) 2) After the create table, if I do PRAGMA legacy_file_format; I indeed receive "1" as an answer. However, I ".quit" then go back into the database and ask PRAGMA legacy_file_format; it returns "0". Once I quit, it "reverts" to 0?? The problem is that the file created is zero length until actual data (or meta-data) is inserted. Thus, you need to at least create a table. Then, once some data has been created, the header will contain the correct legacy_file_format information. So, if I have to issue a PRAGMA legacy_file_format=ON; each time I enter sqlite, I have a problem when I access the database through ODBC. I have no way to issue that PRAGMA command! Am I correct in my reasonning? -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 4:31 PM, [EMAIL PROTECTED] wrote: Alexander Lamb <[EMAIL PROTECTED]> writes: 1. (*) text/plain Well, I am afraid it didn't work. Somehow, the legacy_file_format info is not "sticky". I did: sqlite3 then in command mode: PRAGMA legacy_file_format=ON; then ATTACH "d:\mydb.db" AS mydb; In order to have done an ATTACH, the database had to have already existed. That's too late. Instead, ensure that mydb.db *does not exist*, and then do: sqlite3 d:\mydb.db PRAGMA legacy_file_format=ON; CREATE TABLE TEST (id INTEGER); The next time you then access the file, it will have the proper format. Derrell -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull, still)
Well, sorry, but I did exactly that: 1) When I do the PRAGMA command the file is created (I can see it appearing in the explorer) 2) After the create table, if I do PRAGMA legacy_file_format; I indeed receive "1" as an answer. However, I ".quit" then go back into the database and ask PRAGMA legacy_file_format; it returns "0". Once I quit, it "reverts" to 0?? So, if I have to issue a PRAGMA legacy_file_format=ON; each time I enter sqlite, I have a problem when I access the database through ODBC. I have no way to issue that PRAGMA command! Am I correct in my reasonning? -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 4:31 PM, [EMAIL PROTECTED] wrote: Alexander Lamb <[EMAIL PROTECTED]> writes: 1. (*) text/plain Well, I am afraid it didn't work. Somehow, the legacy_file_format info is not "sticky". I did: sqlite3 then in command mode: PRAGMA legacy_file_format=ON; then ATTACH "d:\mydb.db" AS mydb; In order to have done an ATTACH, the database had to have already existed. That's too late. Instead, ensure that mydb.db *does not exist*, and then do: sqlite3 d:\mydb.db PRAGMA legacy_file_format=ON; CREATE TABLE TEST (id INTEGER); The next time you then access the file, it will have the proper format. Derrell
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)
Alexander Lamb <[EMAIL PROTECTED]> writes: > 1. (*) text/plain > > Well, I am afraid it didn't work. > > Somehow, the legacy_file_format info is not "sticky". > > I did: > > sqlite3 > > then in command mode: > > PRAGMA legacy_file_format=ON; > > then > > ATTACH "d:\mydb.db" AS mydb; In order to have done an ATTACH, the database had to have already existed. That's too late. Instead, ensure that mydb.db *does not exist*, and then do: sqlite3 d:\mydb.db PRAGMA legacy_file_format=ON; CREATE TABLE TEST (id INTEGER); The next time you then access the file, it will have the proper format. Derrell
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)
Well, I am afraid it didn't work. Somehow, the legacy_file_format info is not "sticky". I did: sqlite3 then in command mode: PRAGMA legacy_file_format=ON; then ATTACH "d:\mydb.db" AS mydb; then CREATE TABLE TEST (id int); if I then quit and return to sqlite3 and attach the database I just created and issue the following command: PRAGMA legacy_file_format; I get the answer "0" (when it was "1" during the previous session). This means that when I export my tables from Access to the ODBC datasource of SQLite3, it does not keep the fact it has to write "legacy format". This confirmed by the fact I can't read the database on my Mac. Did I do something wrong? Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 3:34 PM, Alexander Lamb wrote: Ahah, I understood "PRAGMA" as an compile option :-( That's why point 2 was not very clear (I am new to SQLite). I'll do this right away! Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 3:27 PM, Gerry Snyder wrote: Alexander Lamb wrote: So it is not possible to create a database with the legacy file format using the command line on Windows and SQLite 3.3? Yes it is. Reread Dr. Hipp's point (2) below. If I understand correctly, I need to grab the latest CVS version of SQLite to have the 3.3 format disabled by default. Yes, but you do not need to use the default. You just need to issue the pragma command before creating the first table in the new file. Reread Dr. Hipp's point (2) below. The problem I see with that is that I am not a Windows developer so I have no way of compiling the .exe and .dll No need. That was just one of the suggestions. On Aug 4, 2006, at 12:13 PM, [EMAIL PROTECTED] wrote: Either (1) use the latest version of SQLite from CVS or (2) run PRAGMA legacy_file_format=ON; prior to creating that first table in the SQLite database. Hope this helps. Sorry I was too lazy to correct the top-posting. The mish=mash may be hard to read. Gerry
Re: [sqlite] From Windows file format to MacOSX
Ahah, I understood "PRAGMA" as an compile option :-( That's why point 2 was not very clear (I am new to SQLite). I'll do this right away! Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 3:27 PM, Gerry Snyder wrote: Alexander Lamb wrote: So it is not possible to create a database with the legacy file format using the command line on Windows and SQLite 3.3? Yes it is. Reread Dr. Hipp's point (2) below. If I understand correctly, I need to grab the latest CVS version of SQLite to have the 3.3 format disabled by default. Yes, but you do not need to use the default. You just need to issue the pragma command before creating the first table in the new file. Reread Dr. Hipp's point (2) below. The problem I see with that is that I am not a Windows developer so I have no way of compiling the .exe and .dll No need. That was just one of the suggestions. On Aug 4, 2006, at 12:13 PM, [EMAIL PROTECTED] wrote: Either (1) use the latest version of SQLite from CVS or (2) run PRAGMA legacy_file_format=ON; prior to creating that first table in the SQLite database. Hope this helps. Sorry I was too lazy to correct the top-posting. The mish=mash may be hard to read. Gerry
Re: [sqlite] From Windows file format to MacOSX
Alexander Lamb wrote: So it is not possible to create a database with the legacy file format using the command line on Windows and SQLite 3.3? Yes it is. Reread Dr. Hipp's point (2) below. If I understand correctly, I need to grab the latest CVS version of SQLite to have the 3.3 format disabled by default. Yes, but you do not need to use the default. You just need to issue the pragma command before creating the first table in the new file. Reread Dr. Hipp's point (2) below. The problem I see with that is that I am not a Windows developer so I have no way of compiling the .exe and .dll No need. That was just one of the suggestions. On Aug 4, 2006, at 12:13 PM, [EMAIL PROTECTED] wrote: Either (1) use the latest version of SQLite from CVS or (2) run PRAGMA legacy_file_format=ON; prior to creating that first table in the SQLite database. Hope this helps. Sorry I was too lazy to correct the top-posting. The mish=mash may be hard to read. Gerry
Re: [sqlite] date data types
[EMAIL PROTECTED] wrote: Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. well, yes, that was already clear. but: where is the type of the data being stored? aren't there a few spare bits to use for 'future additions', that is, new data types? sure, a file containing date data would not be understood by executables where this has not been defined, but maybe it is possible to do it so that they see a 'text'... or maybe not... And it would go against the basic philosophy of SQLite. in which way? but as far as I am concerned, I'll be happy if I can get the original type declaration for the column by querying the database. is there a way to do so? I've looked into the doc (for example, "information_schema") but I found nothing which was working for me... thanks and regards, MF
Re: [sqlite] From Windows file format to MacOSX
So it is not possible to create a database with the legacy file format using the command line on Windows and SQLite 3.3? If I understand correctly, I need to grab the latest CVS version of SQLite to have the 3.3 format disabled by default. The problem I see with that is that I am not a Windows developer so I have no way of compiling the .exe and .dll -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 12:13 PM, [EMAIL PROTECTED] wrote: Alexander Lamb <[EMAIL PROTECTED]> wrote: Hello list (I am new but searched a bit before posting). I am trying to convert an Access database on WindowsXP to an SQLite database to be used in a CoreData application on MacOSX (BTW, this task seems so generic and would help so much in transfering legacy Access apps to MacOSX I am surprised to not have found anything related). Here is what I did: 1) I created an ODBC datasource for my Access database 2) In installed SQLite (the .exe, the dll and .ref all in a same directory) 3) I created an SQLite database from the command line and created a table (to force the creation of the file) Either (1) use the latest version of SQLite from CVS or (2) run PRAGMA legacy_file_format=ON; prior to creating that first table in the SQLite database. You are probably creating the database with 3.3.6 on windows then trying to read it with 3.1.3 on Mac. A database created by 3.1.3 can be read and written by 3.3.6 (backwards compatible) but a database created by 3.3.6 cannot be read or written by 3.1.3 (not forwards compatible). The revised file format introduced in 3.3.0 has caused so much grief that it will be turned off by default in the next release. The revised file format gives the capability of using DESC indices. In the future, if you want DESC indices, you have to run a pragma to turn the on prior to creating the database. 4) I created an ODBC datasource for my SQLite database 5) I selected one by one each Access table (in Access) and did an "export..." to my SQLite datasource From the command line, I see the SQLite database is correctly filled with my database. 6) I copy the file to my MacOSX machine 7) From the command line, I see I can't open the database I did the test with two versions of SQLite on Windows: with the 3.3 it doesn't work (which seems more or less normal according to the documentation). However, with version 2.8, the mac can't read the file. The error is: Error: file is encrypted or is not a database I looked and the history of versions on the documentation pages of SQLite and they mention needing a reload. But that would meand I have to install SQLite 2.8 on MacOSX. Is that correct and if it is is there an easy package to do so? Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED]
Re: [sqlite] From Windows file format to MacOSX
Simply using SMB (mount from my mac to the PC) then a finder copy. -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 10:31 AM, Brandon, Nicholas (UK) wrote: I did the test with two versions of SQLite on Windows: with the 3.3 it doesn't work (which seems more or less normal according to the documentation). However, with version 2.8, the mac can't read the file. The error is: Error: file is encrypted or is not a database How have you transferred the file from Windows to Mac? (FTP, Appletalk?) This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] RE: UNICODE Support
Cory Nelson wrote: I recommend using utf-16 in the database - sqlite doesn't fully support utf-8, and some things may give unexpected results if you use it. Could you expand a bit on this please? I haven't seen any bugs as such with sqlite as such but I did have a few problems storing "foreign" characters through the Python wrappers to sqlite, where the wrappers barfed converting the "foreign" character. In one case it was because the source (Windows app) lied about the encoding - it claimed the text was UTF-8 when it was windows-1252. In the other case the text came from a Unix box and was supposed to be 7-bit ASCII, but I suspect it was generated by a Windows app, as above. I think I've got this all sorted in my mind but if you say sqlite has issues handling UTF-8 then I need to look at it again. Martin
Re: [sqlite] From Windows file format to MacOSX
Alexander Lamb <[EMAIL PROTECTED]> wrote: > Hello list (I am new but searched a bit before posting). > > I am trying to convert an Access database on WindowsXP to an SQLite > database to be used in a CoreData application on MacOSX (BTW, this > task seems so generic and would help so much in transfering legacy > Access apps to MacOSX I am surprised to not have found anything > related). > > Here is what I did: > > 1) I created an ODBC datasource for my Access database > 2) In installed SQLite (the .exe, the dll and .ref all in a same > directory) > 3) I created an SQLite database from the command line and created a > table (to force the creation of the file) Either (1) use the latest version of SQLite from CVS or (2) run PRAGMA legacy_file_format=ON; prior to creating that first table in the SQLite database. You are probably creating the database with 3.3.6 on windows then trying to read it with 3.1.3 on Mac. A database created by 3.1.3 can be read and written by 3.3.6 (backwards compatible) but a database created by 3.3.6 cannot be read or written by 3.1.3 (not forwards compatible). The revised file format introduced in 3.3.0 has caused so much grief that it will be turned off by default in the next release. The revised file format gives the capability of using DESC indices. In the future, if you want DESC indices, you have to run a pragma to turn the on prior to creating the database. > 4) I created an ODBC datasource for my SQLite database > 5) I selected one by one each Access table (in Access) and did an > "export..." to my SQLite datasource > > From the command line, I see the SQLite database is correctly filled > with my database. > > 6) I copy the file to my MacOSX machine > 7) From the command line, I see I can't open the database > > I did the test with two versions of SQLite on Windows: with the 3.3 > it doesn't work (which seems more or less normal according to the > documentation). > However, with version 2.8, the mac can't read the file. The error is: > > Error: file is encrypted or is not a database > > I looked and the history of versions on the documentation pages of > SQLite and they mention needing a reload. But that would meand I have > to install SQLite 2.8 on MacOSX. Is that correct and if it is is > there an easy package to do so? > > Thanks, > > Alex > -- > Alexander Lamb > [EMAIL PROTECTED]
Re: [sqlite] date data types
Mario Frasca <[EMAIL PROTECTED]> wrote: > > would it be difficult, or simply impossible, to implement these types in > sqlite? > Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. And it would go against the basic philosophy of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] UNICODE Support
"Cory Nelson" <[EMAIL PROTECTED]> wrote: > On 8/3/06, RohitPatel <[EMAIL PROTECTED]> wrote: > > I recommend using utf-16 in the database - sqlite doesn't fully > support utf-8, and some things may give unexpected results if you use > it. > Oh really? What exactly is missing from SQLite's UTF-8 support? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] date data types
Nuno Lucas wrote: You need to get the column declared type and convert it to the type you want based on that. mmm... so I would look at it in a statically typed way. it sounds reasonable and is surely acceptable for me. but: how do I get the declared type of the column? currently the python interface does not retrieve this information, probably because of the dynamic typing of the engine... You can always add a ticket for it, as a new feature, and see how it goes ;-) you mean: reporting a bug? I don't manage to access the page from here, but I see that from home it does work. will definitely do so. it could be an optional feature, to be enabled at compile time... regards, MarioF
Re: [sqlite] date data types
On 8/4/06, Mario Frasca <[EMAIL PROTECTED]> wrote: nice. if I was just working inside of SQL and was only interested in printing the values, it would probably be fine. my problem is that I'm co-author of a python library (http://ibo.sourceforge.net) on top of various db-api2 interface libraries to three or four db engines (four, if I can include sqlite). one of the problems consists in writing and retrieving datetime data to the database. the dynamic typing of sqlite is not a problem, actually it fits quite good with the strong dynamic typing system of Python... but then, when I write to just any field a value which is a date, I would like to get back a value which is a date, not a string or a floating point number. the same goes for a datetime and a timedelta, which are each a separate type in python... You need to get the column declared type and convert it to the type you want based on that. would it be difficult, or simply impossible, to implement these types in sqlite? I think the question here is if the developpers want to do it. It wasn't pacific the inclusion of datetime support in sqlite, so adding one more type it's probably a litle more difficult (remember sqlite is an embeded SQL engine, which aims for a small size and low memory footprint). You can always add a ticket for it, as a new feature, and see how it goes ;-) Regards, ~Nuno Lucas
Re: [sqlite] RE: UNICODE Support
On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: I recommend using utf-16 in the database - sqlite doesn't fully support utf-8, and some things may give unexpected results if you use it. Could you give some example of unexpected result with UTF-8? In my experience the only unexpected results with UTF-8 were bugs in my program (like passing non-UTF-8 string). Any other unexpected result should be considered a bug in SQLite and reported as such. Regards, ~Nuno Lucas -- Cory Nelson http://www.int64.org
RE: [sqlite] From Windows file format to MacOSX
>I did the test with two versions of SQLite on Windows: with the 3.3 it doesn't work (which seems more or less >normal according to the documentation). >However, with version 2.8, the mac can't read the file. The error is: >Error: file is encrypted or is not a database How have you transferred the file from Windows to Mac? (FTP, Appletalk?) This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] From Windows file format to MacOSX
Hello list (I am new but searched a bit before posting). I am trying to convert an Access database on WindowsXP to an SQLite database to be used in a CoreData application on MacOSX (BTW, this task seems so generic and would help so much in transfering legacy Access apps to MacOSX I am surprised to not have found anything related). Here is what I did: 1) I created an ODBC datasource for my Access database 2) In installed SQLite (the .exe, the dll and .ref all in a same directory) 3) I created an SQLite database from the command line and created a table (to force the creation of the file) 4) I created an ODBC datasource for my SQLite database 5) I selected one by one each Access table (in Access) and did an "export..." to my SQLite datasource From the command line, I see the SQLite database is correctly filled with my database. 6) I copy the file to my MacOSX machine 7) From the command line, I see I can't open the database I did the test with two versions of SQLite on Windows: with the 3.3 it doesn't work (which seems more or less normal according to the documentation). However, with version 2.8, the mac can't read the file. The error is: Error: file is encrypted or is not a database I looked and the history of versions on the documentation pages of SQLite and they mention needing a reload. But that would meand I have to install SQLite 2.8 on MacOSX. Is that correct and if it is is there an easy package to do so? Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED]
Re: [sqlite] date data types
Dennis Cote wrote:
Mario Frasca wrote:
where I would expect:
sqlite> select datetime('now');
2006-08-03 11:36:32.211032
sqlite> select typeof(datetime('now'));
datetime
sqlite> select datetime('now') - date('2006-01-01');
214 11:36:51.291331
sqlite> select typeof(datetime('now') - date('2006-01-01'));
timedelta
Mario,
The DATE and DATETIME types you seem to be expecting are handled
in SQLite as Julian dates. These are floating point numbers that
[...]
The TIMEDELTA type is also represented as a floating point number
[...]
Try these queries instead: [...]
nice. if I was just working inside of SQL and was only interested in printing
the values, it would probably be fine. my problem is that I'm co-author of a
python library (http://ibo.sourceforge.net) on top of various db-api2 interface
libraries to three or four db engines (four, if I can include sqlite). one of
the problems consists in writing and retrieving datetime data to the database.
the dynamic typing of sqlite is not a problem, actually it fits quite good with
the strong dynamic typing system of Python... but then, when I write to just
any field a value which is a date, I would like to get back a value which is a
date, not a string or a floating point number. the same goes for a datetime
and a timedelta, which are each a separate type in python...
would it be difficult, or simply impossible, to implement these types in sqlite?
anyway thanks a lot for your answer and queries!
MF
