Re: [sqlite] Empty all rows from table
On Wed, 13 Feb 2008 17:45:20 +1100 BareFeet <[EMAIL PROTECTED]> dijo: > > DELETE FROM worktable > > That is correct. You need a trailing semicolon though, on any command, > ie: > > delete from workTable; > > > But that just throws error messages. I tried adding the trailing semicolon: DELETE FROM worktable; But when I click on the green checkmark (in Kexi) I get: *The query is incorrect Syntax Error near "DELETE" Curiously, this works: SELECT * FROM worktable And without a trailing semicolon, too. I only went to SQL statements because the query builder GUI in Kexi did not give any way to delete rows. Now I'm beginning to wonder if the Kexi folks forgot to include a DELETE statement in their syntax. Thanks for the suggestions. Further ideas welcome. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting Japanese records
On Feb 12, 2008 9:17 PM, Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > Hi, > I am working in 3.3.6 > > My database looks as follows: > "Create table MUSIC (id integer primary key,Track text);" > > I have inserted some Japanese records inside and I tried to list all the > tracks sorted by Tracks. > > "select Tracks from MUSIC order by Track;" [ All records are Japanese Full > Size Katakana ] > If you aren't getting the expected results you might try creating a custom collation. But afaik hiragana and katakana should both sort fine, as both are in lexicographical order (ie, あかさたなはまやらわ) which is what sqlite uses by default. -- Cory Nelson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Empty all rows from table
Hi John, > DELETE FROM worktable That is correct. You need a trailing semicolon though, on any command, ie: delete from workTable; > But that just throws error messages. What error messages? Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Empty all rows from table
The table I want to empty is called worktable. Can't figure how to do it with the GUI. Went to: http://www.sqlite.org/lang.html Which said the correct syntax should be DELETE FROM worktable But that just throws error messages. Surely there is a way to delete all rows from a table. What is the secret? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sorting Japanese records
Hi, I am working in 3.3.6 My database looks as follows: "Create table MUSIC (id integer primary key,Track text);" I have inserted some Japanese records inside and I tried to list all the tracks sorted by Tracks. "select Tracks from MUSIC order by Track;" [ All records are Japanese Full Size Katakana ] But I am not getting the results in sorting order. I have created the table in UTF-16 encoding format.this I did by just enabling the "PRAGMA encoding = UTF16;" before creating the table. Then while inserting I will use as follows. if ( sqlite3_prepare(pst_CallbackInstance->db,"INSERT OR IGNORE INTO MUSIC ( Track ) VALUES ( ? );",-1,&pst_ PrepareStmt,0)!= SQLITE_OK) { return SQLITE_DB_ERROR; } else { sqlite3_bind_text16 (pst_PrepareStmt,1, TrackName,-1,SQLITE_STATIC); sqlite3_step (pst_ PrepareStmt); i_Return = sqlite3_finalize (pst_ PrepareStmt); if( i_Return || ps8_SqliteErrMsg ) { if( ps8_SqliteErrMsg!=0 ) { #ifdef PRINTF_ENABLED printf("SQL error: %s\n", ps8_SqliteErrMsg); #endif sqlite3_free(ps8_SqliteErrMsg); ps8_SqliteErrMsg = 0; } } else { e_ReturnStatus = SQLITE_DB_SUCCESS; } } Will the above function insert the track name in UTF16 format inside Database or I am wrong some where ? I want the Tracks to be in Sorted order [ All track are inserted as UTF16 - Japanese letters ] Can I use UTF8 itself for doing this. Can Anyone help to solve this. Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The PM is the same for the same ProjID because the split is a one to > one > assignment, so that should not happen. So, if there is a case like > this, > the PM has been set wrong. What I would like to have is a total of > the > ProjFund, ProjFund total - invoice total where Split = 'y', invoice > total > where Split = 'y', the first (or last or any of the) PM that Split = > 'y'. SELECT Class, sum(ProjFund), sum(ProjFund) - sum(case split when 'y' then invoice else 0 end), sum(case split when 'y' then invoice else 0 end), max(case split when 'y' then PM else '' end) from ClassTable group by Class, ProjID; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"Igor Tandetnik" wrote... > "jose isaias cabrera" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] >> imagine almost the same data, >> >> Class|ProjID|ProjFund|Invoice|PM|Split >> Finishers|1045|73||JIC| >> Finishers|1045|75|30|LED| >> Finishers|1045|75|30|SAN| >> Finishers|1045|75|30|JIC| >> Finishers|1045|75||ELI| >> Finishers|1045|75|75|ELI|y >> Finishers|1045|75|25|| >> Finishers|1045|73||JIC| >> Finishers|1045|73||LED| >> Finishers|1045|73||KAP| >> Finishers|1045|73|58.4|ELI|y >> Finishers|1045|73||| >> >> I would like to also get the PM value when split = 'y'. > > In the previous problem, you got one row for each distinct Class/ProjId > pair. Do you now want to group by Class/ProjId/PM tuple? If not, and you > still want to group by Class/ProjId, what should happen if, within this > group, there are two different PMs both marked with Split='y'? For > example, what resultset do you expect for input like this: > > Class|ProjID|ProjFund|Invoice|PM|Split > Finishers|1045|75|75|JIC|y > Finishers|1045|75|30|LED|y The PM is the same for the same ProjID because the split is a one to one assignment, so that should not happen. So, if there is a case like this, the PM has been set wrong. What I would like to have is a total of the ProjFund, ProjFund total - invoice total where Split = 'y', invoice total where Split = 'y', the first (or last or any of the) PM that Split = 'y'. thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
Hi Igor, Thank you very much, it working :-) Cheers, 2008/2/13, Igor Tandetnik <[EMAIL PROTECTED]>: > > "li yuqian" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > now my sql working, but result different mysql result. > > > > sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state > > FROM globals t LEFT JOIN globals d ON > > (substr(t.variable,5,length(t.variable)-4) = > > substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE > > 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') > > ORDER BY t.variable; > > My bad. The condition on t must be out in the WHERE clause: > > SELECT t.variable, t.value, ifnull(d.value, 'off')) state > FROM globals t LEFT JOIN globals d ON ( > substr(t.variable,5, length(t.variable)-4) = > substr(d.variable,12, length(d.variable)-11) and > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\' > ) > WHERE t.variable LIKE 'OUT\_%' ESCAPE '\' > ORDER BY t.variable; > > Otherwise the result will always include all rows from t. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
"li yuqian" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > now my sql working, but result different mysql result. > > sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state > FROM globals t LEFT JOIN globals d ON > (substr(t.variable,5,length(t.variable)-4) = > substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE > 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') > ORDER BY t.variable; My bad. The condition on t must be out in the WHERE clause: SELECT t.variable, t.value, ifnull(d.value, 'off')) state FROM globals t LEFT JOIN globals d ON ( substr(t.variable,5, length(t.variable)-4) = substr(d.variable,12, length(d.variable)-11) and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\' ) WHERE t.variable LIKE 'OUT\_%' ESCAPE '\' ORDER BY t.variable; Otherwise the result will always include all rows from t. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
Hi Igor, Thanks for your kind of help :-) now my sql working, but result different mysql result. 1. mysql: --- mysql> SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') d ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable LIKE 'OUT\_%' UNION ALL SELECT v.variable, v.value, concat(substring(v.value,1,0),'off') state FROM `globals` v WHERE v.variableLIKE 'OUT\_%' AND concat('OUTDISABLE_',substring( v.variable,5)) NOT IN ( SELECT variable from globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable; +--++---+ | variable | value | state | +--++---+ | OUT_1| ZAP/g0 | off | +--++---+ -- 2. sqlite : sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5,length(t.variable)-4) = substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; AFTER_INCOMING||off ALLOW_SIP_ANON|yes|off CALLFILENAME|\"\"|off DIALOUTIDS|1|off DIAL_OPTIONS|tr|off DIAL_OUT|9|off DIRECTORY|last|off DIRECTORY_OPTS||off FAX||off FAX_RX|system|off FAX_RX_EMAIL|[EMAIL PROTECTED]|off FAX_RX_FROM|[EMAIL PROTECTED]|off INCOMING|group-all|off IN_OVERRIDE|forcereghours|off NULL|\"\"|off OPERATOR||off OPERATOR_XTN||off OUT_1|ZAP/g0|off PARKNOTIFY|SIP/200|off RECORDEXTEN|\"\"|off REGDAYS|mon-fri|off REGTIME|7:55-17:05|off RINGTIMER|15|off TIMEFORMAT|kM|off TONEZONE|us|off TRANSFER_CONTEXT|from-internal-xfer|off TRUNK_OPTIONS||off VMX_CONTEXT|from-internal|off VMX_LOOPDEST_CONTEXT||off VMX_LOOPDEST_EXT|dovm|off VMX_LOOPDEST_PRI|1|off VMX_LOOPS|1|off VMX_OPTS_DOVM||off VMX_OPTS_LOOP||off VMX_OPTS_TIMEOUT||off VMX_PRI|1|off VMX_REPEAT|1|off VMX_TIMEDEST_CONTEXT||off VMX_TIMEDEST_EXT|dovm|off VMX_TIMEDEST_PRI|1|off VMX_TIMEOUT|2|off VM_DDTYPE|b|off VM_GAIN||off VM_OPTS||off VM_PREFIX|*|off - thanks, 2008/2/13, Igor Tandetnik <[EMAIL PROTECTED]>: > > li yuqian <[EMAIL PROTECTED]> wrote: > > now i try follow sql > > SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM > > globals t LEFT JOIN globals d ON (substr(t.variable,5) = > > substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and > > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; > > > > and get a SQL error: wrong number of arguments to function substr(), > > still not get working :-( > > This statement works for me without any errors. Try upgrading to a more > recent version of SQLite. If for some reason you can't, try replacing > > substr(t.variable,5) > > with > > substr(t.variable,5, length(t.variable) - 4) > > and similarly for the other occurence of substr. > > ''||'off' is equivalent to simply 'off' (concatenating anything to an > empty string doesn't change that anything). > -- > With best wishes, > Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE clauses with prepared statements
On Feb 12, 2008, at 3:51 PM, Igor Tandetnik wrote: > Diego Souza <[EMAIL PROTECTED]> > wrote: >> I wasn't able to figure this out. I'm trying to execute a query like >> this: >> >> SELECT columns FROM table WHERE column LIKE ? >> >> However, I'd like to use % or _ placeholders. For instance in the >> following >> code: >> >> sqlite3_prepare_v2(db, "SELECT columns FROM table WHERE column LIKE >> ?", -1, stmt, 0); sqlite3_bind_text16(stmt, 1, "myutf16txt", bytes, >> SQLITE_STATIC); >> >> How do I insert the % stuff ? > > You can make them part of the parameter value: > > sqlite3_bind_text16(stmt, 1, "%myutf16txt%", bytes, SQLITE_STATIC); > > Or, you can change the statement to something like this: > > SELECT columns FROM table WHERE column LIKE '%' || ? || '%'; > > Igor Tandetnik Also, don't forget to carefully read the notes about the optimizer. In particular: The right-hand side of the GLOB or LIKE operator must be a literal string value that does not begin with a wildcard. If the right-hand side is a parameter that is bound to a string, then no optimization is attempted. So if you bind the parameter then optimization is disabled, which may be important depending on what you are doing. In my case I needed fast lookups from a large db so I had to make the LIKE/GLOB parameter part of the SQL statement I was preparing. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE clauses with prepared statements
Diego Souza <[EMAIL PROTECTED]> wrote: > I wasn't able to figure this out. I'm trying to execute a query like > this: > > SELECT columns FROM table WHERE column LIKE ? > > However, I'd like to use % or _ placeholders. For instance in the > following > code: > > sqlite3_prepare_v2(db, "SELECT columns FROM table WHERE column LIKE > ?", -1, stmt, 0); sqlite3_bind_text16(stmt, 1, "myutf16txt", bytes, > SQLITE_STATIC); > > How do I insert the % stuff ? You can make them part of the parameter value: sqlite3_bind_text16(stmt, 1, "%myutf16txt%", bytes, SQLITE_STATIC); Or, you can change the statement to something like this: SELECT columns FROM table WHERE column LIKE '%' || ? || '%'; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE clauses with prepared statements
Hi there, I wasn't able to figure this out. I'm trying to execute a query like this: SELECT columns FROM table WHERE column LIKE ? However, I'd like to use % or _ placeholders. For instance in the following code: sqlite3_prepare_v2(db, "SELECT columns FROM table WHERE column LIKE ?", -1, stmt, 0); sqlite3_bind_text16(stmt, 1, "myutf16txt", bytes, SQLITE_STATIC); How do I insert the % stuff ? Thanks in advance, -- ~dsouza yahoo!im: paravinicius gpg key fingerprint: 71B8 CE21 3A6E F894 5B1B 9ECE F88E 067F E891 651E ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Well-Known Users of SQLite
Hi All, I was just invited to use LiveStation (http://www.livestation.com/), the LiveStation player installs sqlite3.dll. This might be why Microsoft developers are seen in the sqlite mailing list. http://www.livestation.com/news/18-livestation_showcased_at_microsoft_innova tion_day Kind regards, Lodewijk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tclsqlite3
dick128 wrote: > cannot load dll - 2nd attempt, different, tcl (protcl) > 1st attempt was with a different commerically compiled tcl and it just > exited on load of dll - no error msgs. > > tclsqlite 3.5.6 loading dll blows away tcl > (tclsqlite3.dll) > > can load other dll's and use them,such as tbcload161.dll that comes from > protcl with many different versions of tcl, > without any problems. using windows xp pro. > > any ideas anyone ? > > error signature is > > AppName: protclsh83.exe AppVer: 8.3.2.2 > ModName: tclsqlite3.dll ModVer: 0.0.0.0 Offset: 000553fd > > Exception info > Code: 0xc005 flags: 0x > record 0x > Address:0x0683653fd A simple google search for "Code 0xc005" returns lots of results. This is a memory access violation error. There are many possible causes. Since others are able to run this library, it is most likely something specific to your machine. You might want to run a memory test such as http://www.memtest.org/ to rule out hardware issues. After that start going through some of those links to see if anything similar to your situation is mentioned. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] tclsqlite3
cannot load dll - 2nd attempt, different, tcl (protcl) 1st attempt was with a different commerically compiled tcl and it just exited on load of dll - no error msgs. tclsqlite 3.5.6 loading dll blows away tcl (tclsqlite3.dll) can load other dll's and use them,such as tbcload161.dll that comes from protcl with many different versions of tcl, without any problems. using windows xp pro. any ideas anyone ? error signature is AppName: protclsh83.exe AppVer: 8.3.2.2 ModName: tclsqlite3.dll ModVer: 0.0.0.0 Offset: 000553fd Exception info Code: 0xc005 flags: 0x record 0x Address:0x0683653fd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts2 data in a different table
Hello All, (my first post here) I have various tables which all relate to the same central object, all of them contain various pieces of information about this object. And I want to full-text-search for these objects. I.e. in my case sounds (which have tags, comments, metadata, categories, ratings, etc etc). I wanted to use FTS2 to search through sounds, in the end I settled for a trigger approach: adding a new tag for example will trigger an update of the search-table. create virtual table search using fts2(sound_id, content); create trigger if not exists soundtag_insert after insert on tag for each row begin update search set content=() where search.sound_id=new.sound_id; end; etcetera for all my tables related to the sound. All well, but then I tried: select sound.* from sound left join search on sound.id=search.sound_id where search.content match "bass drum" and sound.samplerate=44100; and got: SQL error: unable to use function MATCH in the requested context The solution I use now is a subselect: select * from sound where samplerate=44100 and id in (select sound_id from search where content match "bass drum"); but this will get me into trouble for large datasets - I suppose. Is there a better solution for this problem or not? I also know FTS2 uses only text-data, so I suppose my join will be slower (or am I assuming too much) than a join between two tables with integer keys? thanks a lot, - bram ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Igor's Emails come as NewsGroups Posts
jose isaias cabrera <[EMAIL PROTECTED]> wrote: > Aaah, my friend, I wish it was that easy. .-) You are young and have > not being touched by the "security" of the enterprise/corporate > world, yet. .-) You see, your message comes to sqlite-users as a news > post, as you can see in the first two lines of this email. When I > try to reply, Outlook Express (yes, I know--thanks) thinks that I am > replying to a news post and gives me a news reply screen, which I can > not change to email nor I can send as an email note. Well, hit Reply instead of Reply Group then (this creates an email rather than a newsgroup post), and put sqlite-users@sqlite.org in To: field. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Igor's Emails come as NewsGroups Posts
"Igor Tandetnik" wrote in message news:[EMAIL PROTECTED] > "jose isaias cabrera" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] >> why is it that your sqlite replies come as NewsGroups posts? > > I'm posting via GMane interface (www.gmane.org) > >> I can >> not reply to them because my company does not allow replies to >> newsgroups. > > I don't understand this statement. The message comes to you as an email, > right? Just reply to it as you would to any email. Gmane will do the > right thing: send it as an email to the list, and also reflect is as a > newsgroup post in GMane newsgroup. There's nothing special you need to > do. Aaah, my friend, I wish it was that easy. .-) You are young and have not being touched by the "security" of the enterprise/corporate world, yet. .-) You see, your message comes to sqlite-users as a news post, as you can see in the first two lines of this email. When I try to reply, Outlook Express (yes, I know--thanks) thinks that I am replying to a news post and gives me a news reply screen, which I can not change to email nor I can send as an email note. X does not have newsgroups servers anymore and and so, I can not push it back. So I have to take your email and create a new email to the list and this is a news... > If you want to send a private email, and are confused by an email > address like [EMAIL PROTECTED], then don't be. I am not, but like I said, I can not reply as an email... > This is GMane's spam protection measure. GMane keeps a mapping from this > one-off address to my real address, and will forward mail accordingly. I am trying to have security open google groups, which I can access some of the "selected" groups. I believe that there is one that pushes these emails back and forth. jic ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updatable views
I agree very much with your suggestion. Although all those vendor specific "extensions" generally make the designers and coders lives easier, the wheels tend to come off in onerous ways when a heavily extended project migration is attempted. Even when using a given vendor's product I have, based on past experiences, made every effort to avoid the "gray" areas (IBM manuals highlight extensions with gray background) of a particular vendor's product. In the long run it will serve a project well no matter the project's size or level of complexity. I have single user PC based projects that I have, over their life, migrated to more than one vendors database. (Do I know how to pick a loser?) And I have participated in projects where a migration from the likes of Oracle to SQL Server (no matter how ill advised) have been made. Therefore I strongly adhere to the SQL Standard whenever possible. This tends to cause less future work in the long run. Although that approach does not eliminate all conversion efforts, because most DB vendors can't even get the SQL Standard right :-( Fred > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Dennis Cote > Sent: Tuesday, February 12, 2008 9:41 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Updatable views > > > John Stanton wrote: > > That ia a nice idea. To have a pragma which specied the > dialect. There > > could be "strict" or "ansi" and "mysql", "oracle", > "sqlserver" etc etc. > > It would give tighter control over hard to track annoying > minor syntax > > errors. > > > > I don't think we need anything that involved. Just a simple > standard SQL > mode and a the current SQLite mode with all its extensions in > place. The > second is needed as the default case for backward compatibility. The > first would let those who care move to standard SQL syntax. > > I would further suggest that SQLite could change its behavior > when the > next major version release happens, and make the standard mode the > default, since it is allowed to break backwards compatibility > at that time. > > Users migrating from other databases generally have to make some > modifications to their schema and SQL code since none of > these systems > are fully standard compliant, and most have some extensions > that are not > supported elsewhere. It would be relatively easy to make the changes > needed to use standard SQL quoting when migrating to SQLite > at that point. > > All these database products are continually moving to better > support for > the SQL standard. This would simply be another step along that road. > Once the database is converted to use standard quoting in SQLite it > would be portable to any of the other databases since they > all support > standard quoting. Similarly, databases created in SQLite > using standard > quoting would be more easily portable to any of these other databases > if the need arises. > > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
li yuqian <[EMAIL PROTECTED]> wrote: > now i try follow sql > SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM > globals t LEFT JOIN globals d ON (substr(t.variable,5) = > substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; > > and get a SQL error: wrong number of arguments to function substr(), > still not get working :-( This statement works for me without any errors. Try upgrading to a more recent version of SQLite. If for some reason you can't, try replacing substr(t.variable,5) with substr(t.variable,5, length(t.variable) - 4) and similarly for the other occurence of substr. ''||'off' is equivalent to simply 'off' (concatenating anything to an empty string doesn't change that anything). -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View update performance (was: Updatable views)
Stephen Oberholtzer wrote: > > If only you'd been around when I'd posted my message! Nobody said > *anything*, so I figured nobody else cared about it. > I have been around for quite some time, but I don't recall your message. However, I do occasionally take a vacation, or get busy and just skim the postings. :-) I'm glad you didn't give up on your idea. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updatable views
John Stanton wrote: > That ia a nice idea. To have a pragma which specied the dialect. There > could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. > It would give tighter control over hard to track annoying minor syntax > errors. > I don't think we need anything that involved. Just a simple standard SQL mode and a the current SQLite mode with all its extensions in place. The second is needed as the default case for backward compatibility. The first would let those who care move to standard SQL syntax. I would further suggest that SQLite could change its behavior when the next major version release happens, and make the standard mode the default, since it is allowed to break backwards compatibility at that time. Users migrating from other databases generally have to make some modifications to their schema and SQL code since none of these systems are fully standard compliant, and most have some extensions that are not supported elsewhere. It would be relatively easy to make the changes needed to use standard SQL quoting when migrating to SQLite at that point. All these database products are continually moving to better support for the SQL standard. This would simply be another step along that road. Once the database is converted to use standard quoting in SQLite it would be portable to any of the other databases since they all support standard quoting. Similarly, databases created in SQLite using standard quoting would be more easily portable to any of these other databases if the need arises. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updatable views
BareFeet wrote: > > I thought I saw syntax of some other SQL engines that > permit if/then or case/when type branching within a trigger, but I may > be mistaken. > A case/when expression is just that an expression. It can appear any where an expression can be used. It can contain multiple sub-select statements, but it can't be used to select between insert, update, and delete statements. Some dialects of SQL support SQL procedures which could be used to do such things, but SQLite does not. > > Yes, although now I'm keen to see fixed the apparent huge overhead of > the inefficiency that Steve raised about using updatable views ;-) > Likewise, I think this is a very good optimization idea that should be implemented as soon as possible. I see he has created a ticket to get it on the radar at SQLite HQ. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Innovative examples / user stories
Hi, (Sorry, I lost original mail to reply) Mohd Radzi Ibrahim yazmış: > [snipped usage example] > > - Original Message - > From: "Lars Aronsson" <[EMAIL PROTECTED]> > To: > Sent: Tuesday, February 12, 2008 1:51 AM > Subject: [sqlite] Innovative examples / user stories > > >> Is there any documentation of how people use SQLite in odd ways in >> their everyday activities? >> [snip] >> I'm using sqlite3 as a configuration file and snippet code(Lua) storage in CairoPad[1] application. My idea was users of CairoPad could share their code with others just transferring one sqlite db file (sharing is not implemented yet). [1] http://www.dynaset.org/dogusanh/download.html#cairopad >> >> -- >> Lars Aronsson ([EMAIL PROTECTED]) >> Aronsson Datateknik - http://aronsson.se -- Regards, Hakki Dogusan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
Hi José, > I have one more ask for help, imagine almost the same data, > > Class|ProjID|ProjFund|Invoice|PM|Split > Finishers|1045|73||JIC| > Finishers|1045|75|30|LED| > Finishers|1045|75|30|SAN| > Finishers|1045|75|30|JIC| > Finishers|1045|75||ELI| > Finishers|1045|75|75|ELI|y > Finishers|1045|75|25|| > Finishers|1045|73||JIC| > Finishers|1045|73||LED| > Finishers|1045|73||KAP| > Finishers|1045|73|58.4|ELI|y > Finishers|1045|73||| Building on the normalized version of the database that I posted earlier, you would add a table: create table FundPM ( FundID integer--> Fund.FundID , PM text collate nocase ) ; insert into FundPM ( FundID, PM ) values (1, 'JIC' ); insert into FundPM ( FundID, PM ) values (2, 'LED' ); insert into FundPM ( FundID, PM ) values (3, 'SAN' ); insert into FundPM ( FundID, PM ) values (4, 'JIC' ); insert into FundPM ( FundID, PM ) values (5, 'ELI' ); insert into FundPM ( FundID, PM ) values (6, 'ELI' ); insert into FundPM ( FundID, PM ) values (8, 'JIC' ); insert into FundPM ( FundID, PM ) values (9, 'LED' ); insert into FundPM ( FundID, PM ) values (10, 'KAP' ); insert into FundPM ( FundID, PM ) values (11, 'ELI' ); > I would like to also get the PM value when split = 'y'. Again, you just join the Split table to the columns that you want: select PM from Split left join Invoice on Split.InvoiceID = Invoice.InvoiceID left join FundPM on Invoice.FundID = FundPM.FundID ; I think this has reached the point where it's probably useful for you to explain the bigger picture of your data and what you're trying to do with it. Tom BareFeet -- ADSL2+ at the cheapest price in Australia: http://www.tandb.com.au/broadband/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
Hi Igor, we want porting the FreePBX(freepbx.org) to our project www.astfin.org, Actually i don't know this substr(t.value,1,0) means :-( now i try follow sql SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) = substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; and get a SQL error: wrong number of arguments to function substr(), still not get working :-( Thanks, 2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>: > > "li yuqian" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > i follow the documents for sqlite function and change the sql to: > > --- > > SELECT t.variable, t.value, ifnull(d.value, > > group_concat(substr(t.value,1,0),'off')) > > state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) = > > substr( d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and > > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; > > - > > always get SQL error: no such function: group_concat, but the sqlite > > documents have this function! > > group_concat is an aggregate function (like max and such), so it's not > what you need anyway. It's probably new in some version of SQLite later > than the one you have. > > SQLite doesn't have concat() function, but has concatenation operator || > (two pipe characters). So simply replace concat(a, b) with a || b > > I'm still pretty sure that substr(t.value,1,0) always returns an empty > string, so you could just as well write '' (two single quotes). I > suspect you meant to do something different, but it's not clear what. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
"li yuqian" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > i follow the documents for sqlite function and change the sql to: > --- > SELECT t.variable, t.value, ifnull(d.value, > group_concat(substr(t.value,1,0),'off')) > state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) = > substr( d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; > - > always get SQL error: no such function: group_concat, but the sqlite > documents have this function! group_concat is an aggregate function (like max and such), so it's not what you need anyway. It's probably new in some version of SQLite later than the one you have. SQLite doesn't have concat() function, but has concatenation operator || (two pipe characters). So simply replace concat(a, b) with a || b I'm still pretty sure that substr(t.value,1,0) always returns an empty string, so you could just as well write '' (two single quotes). I suspect you meant to do something different, but it's not clear what. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
Hi Igor, thank you very much. i follow the documents for sqlite function and change the sql to: --- SELECT t.variable, t.value, ifnull(d.value, group_concat(substr(t.value,1,0),'off')) state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) = substr( d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable; - always get SQL error: no such function: group_concat, but the sqlite documents have this function! thanks 2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>: > > "li yuqian" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > > > i tried your sql, but get a error: > > - > > SQL error: no such function: substring > > > > In SQLite, it's named substr. Check the documentation to make sure it > expects the same parameters as substring in MySQL (with which I'm not > familiar), adjust as necessary. > > http://sqlite.org/lang_expr.html > > I'm particularly suspicious of substring(v.value,1,0) - it appears to > always produce an empty string. What is it supposed to do? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious problem: lower/upper malfunction
It's OK, found a README... :] -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] php connections strings
Give this website a try. I think that it is one of the best examples when starting out. http://www.litewebsite.com/?c=49#sqlite3 > Despite over 12 years in software development, I have been able to > avoid databases for a great part of that time. The real bane in my > side when it comes to SQL databases is connection strings. I would > like to use SQLite 3 in my PHP project, but I need some help on how to > construct the connection string for the PHP Data Objects (PDO) > extension. What would it normally look like for SQLite? > > Sam > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
"li yuqian" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > i tried your sql, but get a error: > - > SQL error: no such function: substring > In SQLite, it's named substr. Check the documentation to make sure it expects the same parameters as substring in MySQL (with which I'm not familiar), adjust as necessary. http://sqlite.org/lang_expr.html I'm particularly suspicious of substring(v.value,1,0) - it appears to always produce an empty string. What is it supposed to do? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious problem: lower/upper malfunction
On Mon, Feb 11, 2008 at 02:23:54PM +0100, Z.B. wrote: > > There is an icu extension to make it work with UTF-8/UTF-16 as > > far as I know. > > Is it a special extension for SQLite? Perhaps someone could tell me, how one is supposed to use an extension http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c Yes, took that file - how to integrate that file with SQLite 3.5.4, to make lower/upper/LIKE properly working? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > imagine almost the same data, > > Class|ProjID|ProjFund|Invoice|PM|Split > Finishers|1045|73||JIC| > Finishers|1045|75|30|LED| > Finishers|1045|75|30|SAN| > Finishers|1045|75|30|JIC| > Finishers|1045|75||ELI| > Finishers|1045|75|75|ELI|y > Finishers|1045|75|25|| > Finishers|1045|73||JIC| > Finishers|1045|73||LED| > Finishers|1045|73||KAP| > Finishers|1045|73|58.4|ELI|y > Finishers|1045|73||| > > I would like to also get the PM value when split = 'y'. In the previous problem, you got one row for each distinct Class/ProjId pair. Do you now want to group by Class/ProjId/PM tuple? If not, and you still want to group by Class/ProjId, what should happen if, within this group, there are two different PMs both marked with Split='y'? For example, what resultset do you expect for input like this: Class|ProjID|ProjFund|Invoice|PM|Split Finishers|1045|75|75|JIC|y Finishers|1045|75|30|LED|y Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
Hi Igor, Thanks for your reply. i tried your sql, but get a error: - SQL error: no such function: substring is something wrong? thanks 2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>: > > "li yuqian" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to > > our > > project www.astfin.org, the freepbx can support sqlite3, but not very > > well, > > now i got a problem about sqlite3 > > - > > SELECT t.variable, t.value, d.value state FROM `globals` t JOIN > > (SELECT > > x.variable, x.value FROM globals x WHERE x.variable LIKE > > 'OUTDISABLE\_%') d > > ON substring(t.variable,5) = substring(d.variable,12) WHERE > > t.variable LIKE 'OUT\_%' UNION ALL SELECT v.variable, v.value, > > concat(substring(v.value,1,0),'off') > > state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND > > concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT > > variable from > > globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable > > You make it a little too complicated. Try this: > > SELECT t.variable, t.value, > ifnull(d.value, concat(substring(t.value,1,0),'off')) state > FROM globals t LEFT JOIN globals d ON ( > substring(t.variable,5) = substring(d.variable,12) and > t.variable LIKE 'OUT\_%' ESCAPE '\' and > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\' > ) > ORDER BY t.variable; > > Note that in SQLite, a backslash has no special meaning in LIKE > operator, unless assigned such meaning via ESCAPE clause (any character > can be used as an escape character, not just backslash). > -- > With best wishes, > Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Igor's Emails come as NewsGroups Posts
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > why is it that your sqlite replies come as NewsGroups posts? I'm posting via GMane interface (www.gmane.org) > I can > not reply to them because my company does not allow replies to > newsgroups. I don't understand this statement. The message comes to you as an email, right? Just reply to it as you would to any email. Gmane will do the right thing: send it as an email to the list, and also reflect is as a newsgroup post in GMane newsgroup. There's nothing special you need to do. If you want to send a private email, and are confused by an email address like [EMAIL PROTECTED], then don't be. This is GMane's spam protection measure. GMane keeps a mapping from this one-off address to my real address, and will forward mail accordingly. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
"li yuqian" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to > our > project www.astfin.org, the freepbx can support sqlite3, but not very > well, > now i got a problem about sqlite3 > - > SELECT t.variable, t.value, d.value state FROM `globals` t JOIN > (SELECT > x.variable, x.value FROM globals x WHERE x.variable LIKE > 'OUTDISABLE\_%') d > ON substring(t.variable,5) = substring(d.variable,12) WHERE > t.variable LIKE 'OUT\_%' UNION ALL SELECT v.variable, v.value, > concat(substring(v.value,1,0),'off') > state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND > concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT > variable from > globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable You make it a little too complicated. Try this: SELECT t.variable, t.value, ifnull(d.value, concat(substring(t.value,1,0),'off')) state FROM globals t LEFT JOIN globals d ON ( substring(t.variable,5) = substring(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\' ) ORDER BY t.variable; Note that in SQLite, a backslash has no special meaning in LIKE operator, unless assigned such meaning via ESCAPE clause (any character can be used as an escape character, not just backslash). -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
Hi José, >> The benefits of normalizing increase with the amount and/or >> complexity of your data. Notice there are no case statements to get >> what you want. You just start with the table you want (Split, in >> this case) and join any needed related data, so SQLite only scans >> the relevant data, rather than testing every row. > > Wouldn't this take longer to process then a case statement? I wouldn't think so, no. This method (ie normalizing first) means that all the rows that have a "Split" value are already in one small table. So there's no searching through all the rows in the one huge table that's filled mainly with nulls. The joining with the Fund and Invoice data is done via the indexed primary key fields, a procedure for which SQL database engines are optimized. In short, if you read the first chapter of any book on SQL programming, you'll see that normalizing your database is the fundamental first step in achieving the nest results with minimum wastage. Tom BareFeet -- 5000 computer accessories delivered anywhere in Australia: http://www.tandb.com.au/forsale/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] php connections strings
Despite over 12 years in software development, I have been able to avoid databases for a great part of that time. The real bane in my side when it comes to SQL databases is connection strings. I would like to use SQLite 3 in my PHP project, but I need some help on how to construct the connection string for the PHP Data Objects (PDO) extension. What would it normally look like for SQLite? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Blob incremental i/o via Python
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Norman Young wrote: > Can this same interface be accessed in Python? That API has to be wrapped. > Can this be done from Python via the sqlite3 module? Can you point me to > examples? I have implemented it in APSW in the development code (ie not released yet). You can find the source on the python sqlite site http://www.initd.org/tracker/pysqlite/wiki/APSW#Developmentversion The api makes it look like a regular python style file object (ie with read/write/close/seek/tell methods). To open one there is a blobopen method on connections which essentially takes the same parameters as the C function http://sqlite.org/c3ref/blob_open.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHsWEUmOOfHg372QQRAgqkAJ4z1cQpbWVfDpQhx0fVmv/eZizD2ACfbBcY P6ggdol0itPywxc7tciPbiU= =6QUU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for sqlite syntax
I tried this, but sqlite3 show: --- SQL error: no such column: d.variable --- any idea? thanks 2008/2/12, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>: > > Could you just use ORDER BY 1 ? > > best regards > -- radzi -- > - Original Message - > From: "li yuqian" <[EMAIL PROTECTED]> > To: > Cc: "Dimitar Penev" <[EMAIL PROTECTED]>; "Mark" <[EMAIL PROTECTED]> > Sent: Tuesday, February 12, 2008 1:56 PM > Subject: [sqlite] Help for sqlite syntax > > > > Hi guys, > > > > i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to > our > > project www.astfin.org, the freepbx can support sqlite3, but not very > > well, > > now i got a problem about sqlite3 > > - > > SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT > > x.variable, x.value FROM globals x WHERE x.variable LIKE > 'OUTDISABLE\_%') > > d > > ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable > > LIKE > > 'OUT\_%' UNION ALL SELECT v.variable, v.value, > > concat(substring(v.value,1,0),'off') > > state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND > > concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT variable > > from > > globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable > > --- > > > > above sql working very well at mysql, but at sqlite3 will show: > > --- > > SQL error: ORDER BY term number 1 does not match any result column > > --- > > > > the globals table is > > -- > > CREATE TABLE `globals` ( > > `variable` char(20) NOT NULL default '', > > `value` char(50) NOT NULL default '', > > PRIMARY KEY (`variable`) > > ) ; > > - > > and insert some contents to this table > > -- > > INSERT INTO `globals` VALUES ('CALLFILENAME','\"\"'); > > INSERT INTO `globals` VALUES ('DIAL_OPTIONS','tr'); > > INSERT INTO `globals` VALUES ('TRUNK_OPTIONS',''); > > INSERT INTO `globals` VALUES ('DIAL_OUT','9'); > > INSERT INTO `globals` VALUES ('FAX',''); > > INSERT INTO `globals` VALUES ('FAX_RX','system'); > > INSERT INTO `globals` VALUES ('FAX_RX_EMAIL','[EMAIL PROTECTED]'); > > INSERT INTO `globals` VALUES ('FAX_RX_FROM','[EMAIL PROTECTED]'); > > INSERT INTO `globals` VALUES ('INCOMING','group-all'); > > INSERT INTO `globals` VALUES ('NULL','\"\"'); > > INSERT INTO `globals` VALUES ('OPERATOR',''); > > INSERT INTO `globals` VALUES ('OPERATOR_XTN',''); > > INSERT INTO `globals` VALUES ('PARKNOTIFY','SIP/200'); > > INSERT INTO `globals` VALUES ('RECORDEXTEN','\"\"'); > > INSERT INTO `globals` VALUES ('RINGTIMER','15'); > > INSERT INTO `globals` VALUES ('DIRECTORY','last'); > > INSERT INTO `globals` VALUES ('AFTER_INCOMING',''); > > INSERT INTO `globals` VALUES ('IN_OVERRIDE','forcereghours'); > > INSERT INTO `globals` VALUES ('REGTIME','7:55-17:05'); > > INSERT INTO `globals` VALUES ('REGDAYS','mon-fri'); > > INSERT INTO `globals` VALUES ('DIRECTORY_OPTS',''); > > INSERT INTO `globals` VALUES ('DIALOUTIDS','1'); > > INSERT INTO `globals` VALUES ('OUT_1','ZAP/g0'); > > INSERT INTO `globals` VALUES ('VM_PREFIX','*'); > > INSERT INTO `globals` VALUES ('VM_OPTS',''); > > INSERT INTO `globals` VALUES ('VM_GAIN',''); > > INSERT INTO `globals` VALUES ('VM_DDTYPE','u'); > > INSERT INTO `globals` VALUES ('TIMEFORMAT','kM'); > > INSERT INTO `globals` VALUES ('TONEZONE','us'); > > INSERT INTO `globals` VALUES ('ALLOW_SIP_ANON','no'); > > INSERT INTO `globals` VALUES ('VMX_CONTEXT','from-internal'); > > INSERT INTO `globals` VALUES ('VMX_PRI','1'); > > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_CONTEXT',''); > > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_EXT','dovm'); > > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_PRI','1'); > > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_CONTEXT',''); > > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_EXT','dovm'); > > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_PRI','1'); > > INSERT INTO `globals` VALUES ('VMX_OPTS_TIMEOUT',''); > > INSERT INTO `globals` VALUES ('VMX_OPTS_LOOP',''); > > INSERT INTO `globals` VALUES ('VMX_OPTS_DOVM',''); > > INSERT INTO `globals` VALUES ('VMX_TIMEOUT','2'); > > INSERT INTO `globals` VALUES ('VMX_REPEAT','1'); > > INSERT INTO `globals` VALUES ('VMX_LOOPS','1'); > > INSERT INTO `globals` VALUES ('TRANSFER_CONTEXT','from-internal-xfer'); > > - > > > > how i can change the sql for sqlite3, any idea, thanks > > > > -- > > Li YuQian > > Your Astfin team > > ___ > > uClinux/Asterisk distribution for Blackfin CPU > > http://www.ucpbx.com > > http://astfin.org > > http://sourceforge.net/projects/astfin/ > > ___ > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian