Re: [sqlite] Feasability of a Range function

2010-03-09 Thread Jean-Christophe Deschamps
Hi Alexey, 1. See internal sqlite instarray interface: http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test Note: http://sqlite.mobigroup.ru include official

[sqlite] Feasability of a Range function

2010-03-07 Thread Jean-Christophe Deschamps
I'm trying to determine if a Range(from, to) function can be made as an extension function. Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 4, 5) for use in constructs similar to select some_scalar_function(i) where i in range(1, 5); without having to build a

Re: [sqlite] Feasability of a Range function

2010-03-07 Thread Jean-Christophe Deschamps
Why not just select some_scalar_function(i) where i between 1 and 5; That's because we then get No such column: i. That was not very important. I would have the use for such possibility but I can live without. My question was just curiosity about whether something along the line could

Re: [sqlite] Feasability of a Range function

2010-03-07 Thread Jean-Christophe Deschamps
Ah. You want table-valued functions, like this: http://msdn.microsoft.com/en-us/library/ms191165.aspx Thanks Igor, that's what I had in mind. In any case, SQLite doesn't support table-valued functions. The closest thing to it is a virtual table: OK, got it, but this is a bit of heavy engine

Re: [sqlite] how to execute an ATTACH DATABASE?

2010-03-07 Thread Jean-Christophe Deschamps
ATTACH DATABASE ?1 as sysDB AFAIK you can't use parameter binding for anything else than litteral values. It makes sense since it would be impossible for the parser and optimizer to evaluate and produce run-time code for a statement without knowing beforehand which database or column the

Re: [sqlite] Feasability of a Range function

2010-03-07 Thread Jean-Christophe Deschamps
Wouldn't it make more sense for i in 1..5 to expand to i = 1 and i = 5? Then it would also work for ordered types that aren't ordinal, such as rationals and strings and blobs and dates etc, and it would work for very large ranges, since there's no conceptual need to generate all the

Re: [sqlite] why is underscore like dash?

2010-03-02 Thread Jean-Christophe Deschamps
sqlite select * from test where text like '_'; Underscore '_' is LIKE wildcard for any single character, percent '%' matches any substring. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
SELECT count(*) WHERE NOT text IS NULL requires that the complete text column is loaded. With a stored LOB this results in crazy performance. How did you find that? What do you mean by requires loading of the whole text column? It pretty much can require even loading of text columns that

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
I totally disagree with you. Let's say you have 1,000,000 rows and 100 of them contain NULL. In this situation selecting NOT NULL will select almost all rows which means that using index in this case doesn't give any performance boost. So here using full scan for NOT NULL condition is better and

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
maybe NOT is implemented the same way as any other function and so it cannot be optimized using index. That's possible, but other logical operators don't exhibit the same bahavior and will not prevent the use of indexes. That NOT is not being handled at the same _logical_ level than AND and

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
So indexes are not used for NOT conditions, as NOT conditions generally require a full scan, regardless. Yes, it is a simple reverse of a binary test, but the reverse of a specific indexed lookup of a known value is a table scan to gather all the unknown values. Jay, I

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT (col = 12345). You're right of course! (and I was even saying about nulls treated apart) But, in your view, that the set can be non-contiguous for negative/negated conditions would it explain that current code can't make

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-03-01 Thread Jean-Christophe Deschamps
I haven't been able to think of how it would preclude using the index, but I suspect it's more a matter of needing a similar-but-different codepath to optimize for the NOT case, rather than a simple invert this codepath relying on the existing case. Which is really just another way of stating

Re: [sqlite] Is there an optimization for IS NULL operator in a SELECT query ?

2010-02-27 Thread Jean-Christophe Deschamps
It is driving me crazy. I'm working on a web spider where a table holds the downloaded webpage. It seems that a select SELECT count(*) WHERE NOT text IS NULL requires that the complete text column is loaded. With a stored LOB this results in crazy performance. Is this optimized in later

[sqlite] Diagrams and ROLLBACK TO

2010-02-26 Thread Jean-Christophe Deschamps
The diagrams in the following pages of the documentation don't allow for the ROLLBACK TO construct: lang_savepoint.html and lang_transaction.html lang_savepoint.html also contains a 'tranaction' typo. ___ sqlite-users mailing list

Re: [sqlite] Diagrams and ROLLBACK TO

2010-02-26 Thread Jean-Christophe Deschamps
Forget the previous post as it's probably wrong. I was misinterpreting the ROLLBACK TO discussion. In fact I was led to believe there were two forms: rollback to and rollback to savepoint. This is not the case. But then, I still have hard time understanding this part: Instead of cancelling

Re: [sqlite] Nesting Read/Write within Transaction?

2010-02-13 Thread Jean-Christophe Deschamps
It doesn't matter if you are in a transaction or not, changing rows while inside a fetch loop on the same table may lead to problems. Sorry I was talking of another process doing asynchronous writes. I didn't understand the warning was towards a single rogue process. Of course letting the

Re: [sqlite] Nesting Read/Write within Transaction?

2010-02-12 Thread Jean-Christophe Deschamps
The only thing that can bite you is if you are in process of fetching rows from some select statement and you update row that was just fetched or update/insert rows that would have been fetched later by the select statement. As I understand it, simply wrapping every batch operation (Read, Write

Re: [sqlite] Multi-process read_uncommited equivalent?

2010-02-05 Thread Jean-Christophe Deschamps
Be aware that the backup process will need to restart from zero after each write! Now, if you can setup some kind of IPC between your two processes, then you could have the update process update the disk base and send identical data to the reader process, so the latter can update a memory

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

2010-02-03 Thread Jean-Christophe Deschamps
I will try to do something to that extent using timer and character counter. I hoped that I could update the text stored in the database character by character as fast as they come from the keyboard driver. Unfortunately updates noticeably slow down the display of typed characters. You can

Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite

2010-02-01 Thread Jean-Christophe Deschamps
To to get columns that begin with a number range or any number, I would use in SQL server: SELECT * FROM mytable WHERE myfield LIKE '[0-9]%' This obviously doesn't work in sqlite and I have been searching for an equivalence. Try this: SELECT * FROM mytable WHERE myfield glob '[0-9]*'

Re: [sqlite] Getting an error table insert failed for eventLog any idea what is the reason

2010-01-30 Thread Jean-Christophe Deschamps
when Database tries to create tables, getting the below error: table insert failed for eventLog any idea what is the reason, also I have these errors when I run manually, I gets these: Uh! To help you in an efficient manner, it's obvious we absolutely need an octal core dump of your 163

Re: [sqlite] SQlite query performs 10 times slower than MS Access query

2010-01-28 Thread Jean-Christophe Deschamps
The execution time of the first time I run my query varies wildly, I dunno why. Probably due to an empty or dirty cache. That's fairly common with cache inclined applications, subsystems or OSes. The second time, most of what's needed is found in cache with much less variability.

Re: [sqlite] contribution: fts3 porter stemmer enhancements to handle common european accents

2010-01-27 Thread Jean-Christophe Deschamps
The porter stemmer, by its very nature, is not intended to work for non-english text You should read the Snowball site about this: http://snowball.tartarus.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] what are the limitations for IN() lists?

2010-01-26 Thread Jean-Christophe Deschamps
Hi Tim, I am also somewhat in the dark about concurrency issues (if any) in a webservice scenario: -- Do TEMP tables have database-connection-scope so that there is no need to name the TEMP table uniquely? Does the table get deleted automatically when the connection is closed if the

Re: [sqlite] what are the limitations for IN() lists?

2010-01-26 Thread Jean-Christophe Deschamps
Hello Simon, This has the advantage of removing the chance of a name-space collision. That's true as well: it is an added free bonus. But honestly I would say that for such transient usage a random generated name is fairly unlikely to cause real-world problem. select hex(randomblob(16));

Re: [sqlite] Newbie problem using special column name

2010-01-25 Thread Jean-Christophe Deschamps
Welcome to the SQLite list. Can I add a column name containing a dash - and if yes, how would I do that? Make this create table test([column-1] varchar(255)); or create table test(column-1 varchar(255)); Both work ___ sqlite-users mailing

Re: [sqlite] update on Ticket 3437

2010-01-24 Thread Jean-Christophe Deschamps
No, it fails because 20 != '20' And also possibly because Date LIKE 2009% should be Date LIKE '2009%' everywhere it appears. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Writes during sleep of backup_step

2010-01-13 Thread Jean-Christophe Deschamps
Hi Dan, I've finally implemented the backup API and it works like a charm except on an important point. The example given on the site clearly says: If another thread writes to database connection pDb while this function is sleeping, then the backup database (database connection

Re: [sqlite] Sort Alphanumeric, Numeric data in a VARCHAR

2010-01-13 Thread Jean-Christophe Deschamps
I'm having trouble sorting the following data: point_number - VARCHAR(10) I've developped an SQLite extension including a very similar collation: it sorts the (integral) prefix first and, in case of a draw, orders based on the Unicode suffix. It currently doesn't cope with floating-point

[sqlite] Writes during sleep of backup_step

2010-01-12 Thread Jean-Christophe Deschamps
I've finally implemented the backup API and it works like a charm except on an important point. The example given on the site clearly says: If another thread writes to database connection pDb while this function is sleeping, then the backup database (database connection pFile) is

Re: [sqlite] graphs and sql

2010-01-09 Thread Jean-Christophe Deschamps
Hi Robert, For example, if I could store a graph in a sqlite database, I'd like to query the database to know if the graph contains a Eulerian path[1]. I may be driven by a misleading uneducated impression, but given the nature of most graph-related algorithms --I see the search for Eulerian

Re: [sqlite] Foreign key support in Sqlite

2010-01-03 Thread Jean-Christophe Deschamps
Hi, I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and downloaded the compiled file. I'm surprised in verifying that the zip only had the executable. I hoped to find also the DLL. I bet you downloaded this: http://www.sqlite.org/sqlite-3_6_21.zip This is the CLI, he

Re: [sqlite] Foreign key support in Sqlite

2010-01-03 Thread Jean-Christophe Deschamps
create table cities ( id integer primary key not null, name text not null ); create table people ( id integer primary key not null, name text not null, cities_id integer not null, foreign key(cities_id) references cities(id) ); insert into cities(name) values('Campos'); insert into cities(name)

Re: [sqlite] Between Query failed for DATETIME in sqlite

2009-12-23 Thread Jean-Christophe Deschamps
I am inserting data into asset table as m/d/ HH:MM:SS t format Ex: INSERT INTO ASSET (WARRANTSTDATE) VALUES ('12/22/2009 12:01:00 AM') Ex: INSERT INTO ASSET (WARRANTSTDATE) VALUES ('9/22/2009 12:01:00 AM') but my select query failed : No record found Not surprising: you made it very

Re: [sqlite] floor help (plus bug found)

2009-12-12 Thread Jean-Christophe Deschamps
Hi, At 00:11 13/12/2009, you wrote: Sir any ida how can value rounddown floor have done if not possible i have make small code i requard make function please say how can add i send you my rounddown funtion please Cose Exmaple : value=10.666 decimal=1 Create roundd{value,decimal){ if

Re: [sqlite] floor help (plus bug found)

2009-12-12 Thread Jean-Christophe Deschamps
[ I apologize if this appears twice on the list ] Hi, At 00:11 13/12/2009, you wrote: Sir any ida how can value rounddown floor have done if not possible i have make small code i requard make function please say how can add i send you my rounddown funtion please Cose Exmaple :

Re: [sqlite] floor help (NO bug, sorry)

2009-12-12 Thread Jean-Christophe Deschamps
I believe there is a bug here. Yes, in I my own head! We simply need to take care of integral values. The correct rounding down at 3rd decimal places using SQLite can be done so: case when cast(myValue as text) round(myValue) then round(myValue - 0.0005, 3) else

Re: [sqlite] .importing file into a BLOB

2009-12-10 Thread Jean-Christophe Deschamps
Something like (one line, wrapped by email): echo INSERT INTO t1 (id,pic) VALUES (1,X'$(od -A n -t x1 picture.gif | tr -d '\r\n\t\ ')') | sqlite3 mydb (The od utility may be smarter than that, optimization left to the OP) Looks good, you know better than I do. Just checked: od and tr are

Re: [sqlite] .importing file into a BLOB

2009-12-09 Thread Jean-Christophe Deschamps
Ted, Is it the command-line program misinterpreting the CR/LFs? Unfortunately, any command-line tool will interpret control characters at their face value when they are fed directly. With no ad hoc program doing it for you, the solution, as I've discussed at length before and even if it is

Re: [sqlite] char,ascii function in sqlite

2009-12-07 Thread Jean-Christophe Deschamps
hi , im using sqlite3 in debain i want to check the first character should not be an special character. [by before insert trigger] how to do it? i thought of using ascii function but i didnt find in sqlite. No such function is part of the SQLite core. But I wrote an extension offering that

Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps
What is programatically How, in any meaningful way, is this different than running a shell command (program of an extremely brief size)? I took the OP's phrasing to mean that he needed a way to do it with e.g. command-line available programs, but in any case without having to use a

Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps
Hi Puneet, Yes, that seems like a reasonable interpretation of the OP's question, one I also understood. One thing I don't understand though, Jean-Christophe, even though one can enter base64 encoded images into the db via the sqlite shell, how does one create the base64 encoded images? One would

Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps
Could you please give me a step by step on how to do that? Not via a small program written in a programming language, but only via the sqlite3 shell, as both you and I agree that was the implied intent of the OP's question. Sorry, I'm no vxWorks, Unix, Linux, MacOS, Windows, AS400, Symbian,

Re: [sqlite] Not matching numbers in where clause

2009-12-06 Thread Jean-Christophe Deschamps
I'm certainly not a SQlite guru, but sqlite select * from sqlite_master where name = 'dly04'; table|dly04|dly04|1042384|CREATE TABLE dly04(i_stnid integer, national_identifier varchar2(7), local_year integer, local_month integer, local_day integer, etc, etc I then imported data from work

Re: [sqlite] Putting images into SQLite.

2009-12-06 Thread Jean-Christophe Deschamps
From what I read, it is necessary to have a programmatic interface to put images into a database. True? yes, but I am not sure what other kind of interface there could be? You can't imagine putting an image into a sqlite db through the command line shell application. Of course, you can

Re: [sqlite] Putting images into SQLite.

2009-12-06 Thread Jean-Christophe Deschamps
You are correct. You proved that it is possible. part 2: What are the usual and convenient ways of inserting images into a sqlite db? answer: Programmatically. I certainly don't dispute this, and I added to this effect: It may not be the prefered way in most application, but it's

Re: [sqlite] The next release of SQLite....

2009-12-04 Thread Jean-Christophe Deschamps
Gidday Tim, I will look further into this approach: select sqlite3_load_extension('mylibrary', 'entrypoint'); to see if Adobe's security permits it. However, the Adobe FlashBuilder database application developer must confront this uncertainty: Adobe has been unresponsive to questions about

Re: [sqlite] Pragma not allowed in SQL error

2009-12-03 Thread Jean-Christophe Deschamps
PRAGMA set case_sensitive_like =1 This should be: PRAGMA case_sensitive_like = 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

2009-12-03 Thread Jean-Christophe Deschamps
Last minute comments on the pending release of SQLite 3.6.21 are welcomed. Thank you for your continued efforts. Can you consider making sqlite3_auto_extension and sqlite3_reset_auto_extension available into the API structure so that they can both be invoked from within an extension without

Re: [sqlite] The next release of SQLite....

2009-12-03 Thread Jean-Christophe Deschamps
Hi Tim, ... where myTextColumnUsingDefaultBinaryCollation like 'foo%' Did you try ... where myTextColumnUsingDefaultBinaryCollation glob 'foo*' GLOB is hardcoded as case-sensitive and more likely a candidate to using index. Just check it. 2. In Adobe, one is not able to load a

Re: [sqlite] WHERE CLAUSE in UNICODE

2009-11-29 Thread Jean-Christophe Deschamps
Now I want to make query on employee table which gives the result having names start between 'D' and 'M', so probable I'll do something SELECT * FROM employee WHERE name BETWEEN 'd%' AND 'n%' . But this is very specific if I know the characters. I want to avoid using the character and use

Re: [sqlite] another Feature Request: char from codepoint?

2009-11-18 Thread Jean-Christophe Deschamps
Hi Igor, The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I believe. That means the user enters the hex UTF-8 (or 16 depending on base encoding) representation of the character. E.g.: select cast(x'c389' as text); É Something like: select chrw(x'c9');

[sqlite] sqlite3_value_type question

2009-11-18 Thread Jean-Christophe Deschamps
When the argument to sqlite3_value_type() is either a litteral or a result of some scalar function, can we rely on testing for the 5 datatypes reliably? I understand that when the argument comes from a column, then the type returned by sqlite3_value_type() is the column type. But litterals

Re: [sqlite] feature request: built-in FLIP(string) function

2009-11-17 Thread Jean-Christophe Deschamps
So, for example, if one wanted to find all rows where myNormalColumn ENDS WITH 'fi c d', one could search myFlippedColumn like this: select * from LEXICON where myFlippedColumn LIKE 'd c if%' -- allows index use Make this select * from LEXICON where myFlippedColumn LIKE flip('fi c d')

Re: [sqlite] Unicode support

2009-11-17 Thread Jean-Christophe Deschamps
Tim, For those who are insisting on Unicode graphemic codepoint-combination intelligence: why can't we have a function that simply reverses the order of the codepoints, and is blissfully ignorant about what those individual codepoints or codepoint-combinations might signify as graphemes in a

Re: [sqlite] sqlite3_column_name

2009-11-13 Thread Jean-Christophe Deschamps
Unfortunately I cannot modify the query... it is supplied by an user. Well, what about upgrading the user? Sorry coul'd resist ... I'm already out! ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Jean-Christophe Deschamps
Now that I have a working tool to convert from Access .mdb to sqlitedb files, I need one for dBASE .dbf files. Or, a conversion to .csv will work, too. Needs to run on linux, of course. My Google searches turned up a bunch of tools for the Windows platforms, supposedly free

Re: [sqlite] sqlite for threads

2009-11-10 Thread Jean-Christophe Deschamps
Hi, Maybe many others have asked this question, so I will say sorry if that's true. I have a program which uses threads, when writing to (sometime even reading from) SQLite, I always got the error of database is locked. I think since SQLite is a file db, so it get locked easily by

Re: [sqlite] Column as a substring

2009-11-08 Thread Jean-Christophe Deschamps
Hello! Please, help me if you have a time for this. I have an sqlite database table: CREATE TABLE lemma ( id INTEGER PRIMARY KEY, base TEXT, preflex_id INTEGER, type_ancode TEXT, prefix_id INTEGER ) In the base column I store a string which I need to compare with another string,

Re: [sqlite] Column as a substring

2009-11-08 Thread Jean-Christophe Deschamps
Third, my oriiginal inquiry: SELECT * FROM fm WHERE name LIKE '%Juiian%' OR info LIKE '%Julian%' ORDER by name; returned the (correct) 6 rows. BTW, if you or anyone else need a fuzzy compare function I have one that can help. It works with internally Unicode-unaccented versions

Re: [sqlite] Column as a substring

2009-11-08 Thread Jean-Christophe Deschamps
I think that my problem is in using LIKE expression for non-ascii strings. Database encode is UTF-8. When table data in the base column (see my first message for structure) consists of english symbols (ascii) LIKE works correct, but when I'm trying to execute it on strings consists of UTF8

Re: [sqlite] Local data structures vs sqlite

2009-11-06 Thread Jean-Christophe Deschamps
I just don't want anyone saying but this one dude told me to replace all my structs with SQLite memory tables. Indeed that wouldn't be very clever SQLite promotion! I think the best plan in this case would be to get a real language But, surprisingly this thing is a real language, with some

Re: [sqlite] Local data structures vs sqlite

2009-11-05 Thread Jean-Christophe Deschamps
Hi John, I wouldn't use SQLite for most in memory data that never needs to be stored on disk Even this depends entirely on your context. Of course if only a simple lookup in a table is more or less all you ever need, there is little point in SQLite. But if or when your requirements get more

Re: [sqlite] Local data structures vs sqlite

2009-11-05 Thread Jean-Christophe Deschamps
You read 'worse' instead of 'worst', of course! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Local data structures vs sqlite

2009-11-05 Thread Jean-Christophe Deschamps
I think worth should be more accurate. Yes, rotfl! I realize this while hitting Send and didn't dare one more post. You know, I'm a victim of rogue cosmic rays and badly need one of those stock ECC brains. Sorry for my French accent btw! ___

Re: [sqlite] Local data structures vs sqlite

2009-11-05 Thread Jean-Christophe Deschamps
Nice Freudian slip! Again rotfl, when you know that slip is French word for men underwear... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find non-numeric character in text field

2009-11-04 Thread Jean-Christophe Deschamps
I would like to do a where on a text field and check if the values have non-numeric characters, which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space character. Is this possible without using a UDF or a very long OR construction? select * from mytable where mycol glob

Re: [sqlite] User-defined infix functions

2009-11-03 Thread Jean-Christophe Deschamps
´¯¯¯ In your specific example you could simply define a custom LIKE function, and LIKE could become Unicode aware without any goofy new operators. `--- Yes of course, but I'm doing so to keep the possibility to use the native operator as well. ___

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
[1] Supposing some textual data already inserted as UTF-8 (default mode) in a dBase, and a connection opened with sqlite3_open(): Does a sqlite3_column_text16 retrieves a correct UTF-16 content? Is to say, do SQLite the convertion internally? [2] Assuming the previous -or a UTF-16 content

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
My main point is that you can't take the UTF-16 string and safely supply it to APIs which want UCS-2 encoded text, such as Win32 APIs (including things like SetWindowText()). Odds are that the only library you are using which supports UTF-16 is SQLite. You should always be converting the text

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
Hi, Please, follow Igor advices, he is right. [1] Read the actual textual data with sqlite3_column_blob() Which you can directly convert to TEXT if, as you say, you entered only 7-bit ASCII or UTF-8 compliant data. [2] Assuming the system code page matches the one used when the data was

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
Hi, ´¯¯¯ Despite of that, I'm aware that I have some more that pure US-ASCII in the blob objects, in fact I'm near your situation because used the Spanish languaje and have 8-bit extended ASCII with some special characters -accented characters and so-. So the question is Yes, I have upper-ANSI

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
Hi John, Microsoft never seems to clearly identify whether the wide APIs should be given UTF-16 or UCS-2. Their guide on internationalization would seem to suggest that UCS-2 must be used, however, there is some reason to believe that perhaps UTF-16 is handled correctly as well. Couldn't find

Re: [sqlite] Some clarification needed about Unicode

2009-10-29 Thread Jean-Christophe Deschamps
Thanks for the link. That clarifies things a lot. So, for the OP, if you are targeting Win2k, it would be a good idea to use UCS-2, not UTF-16, with any wide API calls. XP and above should (according to Kaplan and Chen) support UTF-16 for API calls. W2k is clearly something of the past. But

Re: [sqlite] Degree character not displayed correctly.

2009-10-26 Thread Jean-Christophe Deschamps
Ted, I didn't insert it. I 'inherited' it from a (mercifully nameless) predecessor. I want to put this data into a database to make it easily accessible I'm no SQLite guru (really NO), but here is my 2 cent advice. First decide or determine what is (or shall be) your database encoding. Even

Re: [sqlite] Degree character not displayed correctly.

2009-10-26 Thread Jean-Christophe Deschamps
Nico, Igor, You're both right to point out that using SQLite would result in non-UTF-* compliant data producing unexpected results. There is still the possibility to store such data as blobs. Sorry for confusion. ___ sqlite-users mailing list

[sqlite] Can FK be created behind the scene?

2009-10-24 Thread Jean-Christophe Deschamps
Dear SQLiters, I'm almost sure the answer is No but I'd rather ask the question anyway. Is there _any_ situation where the v3.6.19 stock SQLite3 would create _by itself_ temporary foreign keys not explicitely created by the user, either or both on the referencing or on the referenced table?

Re: [sqlite] Can FK be created behind the scene?

2009-10-24 Thread Jean-Christophe Deschamps
Hi Igor, Temporary foreign keys? I can't fathom what this term could possibly mean. Your question makes no sense to me, sorry. What problem are you really trying to solve? Sorry, problem solved (outside SQLite). I noticed that after trying a DBM application I got errors saying that a

Re: [sqlite] manipulating arguments (in C)

2009-10-22 Thread Jean-Christophe Deschamps
Hi Rob, Perhaps this might lead you in the right direction Jean-Christophe ... #include stdarg.h Thank you for your answer. The va_* construct isn't portable, AFAIK. Various compilers (headers, libraries) may (and did) implement it in different and potentially incompatible ways. Since in

[sqlite] manipulating arguments (in C)

2009-10-21 Thread Jean-Christophe Deschamps
I feel the need to wrap an SQLite printf-like function into a scalar function. I wish to use it as in: select printf(format_string, list of arguments); My question is slightly off topic but there are experienced users here who have probably done it before. In the scalar function

Re: [sqlite] manipulating arguments (in C)

2009-10-21 Thread Jean-Christophe Deschamps
Roger, Thank you for your answer. I knew from old days that va_* things are very fragile (not only from the portability point of view). In the duct tape programming situation where I currently am, the best I can came up with is by fixing the max # of arguments to 32 and using a _ugly_ kludge

[sqlite] Questions about sqlite3_result_text*

2009-10-20 Thread Jean-Christophe Deschamps
Hi, The following details about text/blobs returns from scalar functions are unclear to me and I prefer doing things right. a) is it allowable for a scalar function to modify (in place) an input argument and return a pointer to modified argument with sqlite3_result_text? If yes, what

Re: [sqlite] Questions about sqlite3_result_text*

2009-10-20 Thread Jean-Christophe Deschamps
You are trying really hard to overthink things :-) I simply found version c-1) in a widely spread extension and was surprised by this way of doing the return, unduly complicated and inefficient in my poor understanding, hence the question. ___

Re: [sqlite] Is it possible to combine a collation and a search

2009-10-14 Thread Jean-Christophe Deschamps
Andy, Hwever, what I want to do is seach for all of these varients too, i.e. so that if I search for e that I get all of the e and accented e' etc, is this possble using something like the collation, or do I need to specify all of them individually? I'm about to release the beta of an SQLite

Re: [sqlite] Encoding specs functions overloading

2009-10-13 Thread Jean-Christophe Deschamps
Hi Pavel, I believe you need to show us your sql query. Maybe something in it forces SQLite to use UTF-16 version of the function. Ummm, I don't kno where the problem is, but _any_ simple select will do (for me), e.g.: An UTF-8 base... CREATE TABLE PaysISO ( Nom_Iso CHAR(43), Code_Iso

Re: [sqlite] Encoding specs functions overloading

2009-10-13 Thread Jean-Christophe Deschamps
Update: the problem is in the function registration. I tried to comment out the UTF-16 registration and the really weird thing is that using the following code, only GLOB with 3 arguments gets actually registered (along with all 1-arg string functions and the two collations). There must be

Re: [sqlite] Encoding specs functions overloading

2009-10-13 Thread Jean-Christophe Deschamps
If I set a breakpoint on this: rc = sqlite3_create_function(db, p-zName, p-nArg, p-enc, p-pContext, p-xFunc, 0, 0); I see that sqlite3.dll is returning 5 == SQLITE_BUSY for the following functions: upper UTF-8 lower UTF-8 like 2-arg UTF-8 like 3-arg UTF-8 glob

Re: [sqlite] Encoding specs functions overloading

2009-10-13 Thread Jean-Christophe Deschamps
The 3.6.18 sqlite3.exe CLI produces the same problem: the internal functions below can't be overloaded and trying to do so returns 5. System is XP Pro x86 SP3. What can I try next ? I see that sqlite3.dll is returning 5 == SQLITE_BUSY for the following functions: upper UTF-8 lower

Re: [sqlite] readers and writer

2009-10-13 Thread Jean-Christophe Deschamps
´¯¯¯ So if a SELECT is in progress, other SELECT commands can be allowed to proceed without problems. But no INSERT or UPDATE can be allowed until the SELECT is finished. Hence you will sometimes get a lock on the write. How you deal with this, I don't know. Random wait-and-try-again ? `---

Re: [sqlite] Igor's Equation

2009-10-12 Thread Jean-Christophe Deschamps
´¯¯¯ So what am I missing? `--- The word 'Euclidean'. Stop dividing just before the result gets fractional and you're home. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encoding specs functions overloading

2009-10-12 Thread Jean-Christophe Deschamps
I'm surprised that if I register overloading functions for LIKE or GLOB in both UTF-8 and UTF-16, only the UTF-16 version is called despite the fact that the database is UTF-8. I don't see the same behavior with the other scalar functions (lower, upper), which call the UTF-8 version as

Re: [sqlite] Encoding specs functions overloading

2009-10-12 Thread Jean-Christophe Deschamps
Thank you for your fast answer. I'm surprised by this too. In fact, I cannot reproduce it. I'm using the 3.6.18 Windows dll downloaded direct from the site. I just re-checked that. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Clarification needed for a syntax

2009-10-06 Thread Jean-Christophe Deschamps
Hi all, I've been surprised that the following syntax doesn't work and returns 3 values for 4 columns diagnose message. I'm just asking by curiosity. INSERT INTO t (a, b, c, d) VALUES ('aa', 'bb', (SELECT c, d FROM t WHERE cond)); with cond guaranteed to select exactly one row. I

Re: [sqlite] Clarification needed for a syntax

2009-10-06 Thread Jean-Christophe Deschamps
INSERT INTO t (a, b, c, d) VALUES ('aa', 'bb', (SELECT c, d FROM t WHERE cond)); with cond guaranteed to select exactly one row. I don't know of any DBMS where this would be valid. It seems so. INSERT INTO t(a, b, c, d) SELECT 'aa', 'bb', c, d FROM t WHERE cond; My mistake: I had

Re: [sqlite] Clarification needed for a syntax

2009-10-06 Thread Jean-Christophe Deschamps
´¯¯¯ SQLite tolerates this violation by picking the first column from the first row as the value of such an expression. There is no n-tuple here. `--- That explains it very clearly now. Point taken. Thanks. ___ sqlite-users mailing list

Re: [sqlite] How do I get context in collation function?

2009-10-05 Thread Jean-Christophe Deschamps
Pavel, My 2 cents here is: sometimes bigger and more complicated code runs a whole lot faster than simple one if the speed is a real concern of course... Indeed you're right in the general case. In fact it wasn't at all what I really meant. My problem currently is to have applications run,

Re: [sqlite] How do I get context in collation function?

2009-10-03 Thread Jean-Christophe Deschamps
Igor, Can't you preallocate sufficient memory at the time the collation is created? Unfornately I can't do that: it would mean I place a maximum size on a work space allocated at creation for manipulating user strings, which is taboo in my view. This is for getting unaccented copies of input

Re: [sqlite] How do I get context in collation function?

2009-10-03 Thread Jean-Christophe Deschamps
Roger, ´¯¯¯ There is now a ticket for this issue: `--- Thanks, not high priority but useful someday. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get context in collation function?

2009-10-03 Thread Jean-Christophe Deschamps
Igor, ´¯¯¯ It seems fairly easy to me to implement the kind of collation you describe using only a fixed amount of extra memory. `--- I didn't say it was impossible. Just that in this case, the code gets slowed down in convoluted loops everywhere. Also I feel that clear, straightforward code

<    1   2   3   4   >