Re: [sqlite] Empty all rows from table

[John Jason Jordan]

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

[Cory Nelson]

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

[BareFeet]

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

[John Jason Jordan]

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

[Mahalakshmi.m]

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,_ 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

[Igor Tandetnik]

"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

[jose isaias cabrera]

"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

[li yuqian]

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]

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

[Peter A. Friend]

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

[Igor Tandetnik]

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

[Diego Souza]

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

[Lodewijk Duymaer van Twist]

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

[Dennis Cote]

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

[dick128]

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

[Bram - Smartelectronix]

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

[Igor Tandetnik]

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

[jose isaias cabrera]

"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

[Fred Williams]

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

[Igor Tandetnik]

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)

[Dennis Cote]

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

[Dennis Cote]

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

[Dennis Cote]

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

[Hakki Dogusan]

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

[BareFeet]

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

[li yuqian]

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

[Igor Tandetnik]

"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

[li yuqian]

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

[Zbigniew Baniewski]

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] Help for sqlite syntax

[Igor Tandetnik]

"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

[Zbigniew Baniewski]

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

[Igor Tandetnik]

"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

[li yuqian]

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

[Igor Tandetnik]

"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

[Igor Tandetnik]

"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

[BareFeet]

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

[Sam Carleton]

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

[Roger Binns]

-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

[li yuqian]

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
>