Re: [sqlite] Help with SQLite and VB2010 Express

[Hotmail (terreaultguy)]

Hi, Simon
I am a SQLite user. And before I can get to ask questions about SQLite 
Database, I must be able to use it with Visual Studio 2010.
The tools from SQLite are not up to par.

So I think all questions about SQLite and the environment it can be used are 
SQLite User Question.
I will always answer them, if I can help. And if I know of another Forum 
that answers the better will provide it.

So do not be so sanctimonious.

Guy
-Original Message- 
From: Simon Slavin
Sent: Monday, January 10, 2011 5:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Help with SQLite and VB2010 Express


On 10 Jan 2011, at 9:34pm, Bob Keeland wrote:

> Obviously I don't know what I'm doing with SQLite so I'll ask the question 
> that shows my ignorance.
> What is the difference between what can or should be asked on this list 
> and what should be directed to the System.Data.SQLite forum?

The easiest way is to pretend that the people on this list (the sqlite-users 
one) have never used your operating system or programming language, and the 
people on the System.Data.SQLite forum have never used the advanced features 
of SQLite.  The question you asked refers in detail to .NET, particular 
setup procedures, and OleDB.  None of those things are part of SQLite, and 
none of them can be downloaded from the SQLite home site.  From our point of 
view, they're not really part of the subject matter of this list.

It's possible that you'll find someone on this list who can answer your 
question.  But it's more likely that you'll get better help from the other 
list because those are the people who understand all the technical terms you 
used.  Had your question been about the syntax of a SQLite command, this 
list would be better.

>  They seem to have duplicate purposes - helping people who are having 
> problems.

I read quite a few technical fora like that but they don't have much 
overlap.  People who read one of the lists won't help me with the 
subject-matter of another list because they just don't know about it, and 
the question would be off-charter anyway.

Simon.
___
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] Propose minor incompatible API change

[Tod Wulff]

On Mon, Jan 10, 2011 at 9:00 PM, Igor Tandetnik  wrote:
...  They are stuck calling sqlite3_step - incorrectly - so the only
remaining move is to modify the behavior of sqlite3_step itself, to allow
them to muddle through.  ...

Understood and concurred.  It makes sense to capitulate and have the current
implementation of sqlite3_step() support how the market has decided to use
it.  *Right, wrong, or otherwise, it is what it is.*

However for new dev efforts, does it not make sense to consider deploying
sqlite3_step_v2() which functions as the devs intend - i.e. leave
sqlite3_step() in place for the legacy code and made sqlite3_step_v2() the
new defacto standard for current/future dev efforts.?.

-t

On Mon, Jan 10, 2011 at 9:00 PM, Igor Tandetnik  wrote:

> On 1/10/2011 8:52 PM, Chris Peachment wrote:
> > On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote:
> >
> > 
> >
> >> This is, technically, a compatibility break.  On the other hand, there
> >> appear to be vast numbers of smartphone applications that currently
> depend
> >> on undefined behavior and will suddenly stop working if we don't make
> this
> >> change.
> >>
> >
> > What's wrong with using a new function: sqlite3_step_v2()
>
> Presumably, it would be just as difficult to get those broken apps to
> switch to sqlite3_step_v2 as it would be to get them fixed in the first
> place. They are stuck calling sqlite3_step - incorrectly - so the only
> remaining move is to modify the behavior of sqlite3_step itself, to
> allow them to muddle through.
> --
> Igor Tandetnik
>
> ___
> 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] Propose minor incompatible API change

[Igor Tandetnik]

On 1/10/2011 8:52 PM, Chris Peachment wrote:
> On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote:
>
> 
>
>> This is, technically, a compatibility break.  On the other hand, there
>> appear to be vast numbers of smartphone applications that currently depend
>> on undefined behavior and will suddenly stop working if we don't make this
>> change.
>>
>
> What's wrong with using a new function: sqlite3_step_v2()

Presumably, it would be just as difficult to get those broken apps to 
switch to sqlite3_step_v2 as it would be to get them fixed in the first 
place. They are stuck calling sqlite3_step - incorrectly - so the only 
remaining move is to modify the behavior of sqlite3_step itself, to 
allow them to muddle through.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Richard Hipp]

On Mon, Jan 10, 2011 at 8:52 PM, Chris Peachment  wrote:

> On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote:
>
> 
>
> > This is, technically, a compatibility break.  On the other hand, there
> > appear to be vast numbers of smartphone applications that currently
> depend
> > on undefined behavior and will suddenly stop working if we don't make
> this
> > change.
> >
>
> What's wrong with using a new function: sqlite3_step_v2()
>

The legacy applications we are trying to fix all link against
sqlite3_step().  If we were to make them changes to sqlite3_step_v2(), we
might as well just ask them to fix their code.  But, as noted in the
original post, that is impractical due to the large number of broken legacy
applications.


>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Chris Peachment]

On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote:



> This is, technically, a compatibility break.  On the other hand, there
> appear to be vast numbers of smartphone applications that currently depend
> on undefined behavior and will suddenly stop working if we don't make this
> change.
> 

What's wrong with using a new function: sqlite3_step_v2()


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Propose minor incompatible API change

[Richard Hipp]

As you probably know, the sqlite3_step() interface is used to step through
the results of an SQLite query.  The sqlite3_step() function returns
SQLITE_ROW for every row that is returned, then SQLITE_DONE when there are
no more result rows.  After SQLITE_DONE (or an error) is returned, one can
use sqlite3_reset() to rewind the statement back to the beginning then start
calling sqlite3_step() again.

Prior to 3.6.23.1, if you called sqlite3_step() after it returned
SQLITE_DONE and without first calling sqlite3_reset(), you would get an
SQLITE_MISUSE error.  But, we were told, this was causing problems for some
poorly coded applications that were getting back SQLITE_BUSY or
SQLITE_LOCKED and were immediately retrying sqlite3_step() without the
intervening sqlite3_reset().  Rather than force a huge cloud of (mostly
smartphone) apps to be fixed, we decided to change the way sqlite3_step()
worked so that if you called sqlite3_step() again after an error or an
SQLITE_DONE it would reset automatically instead of returning SQLITE_MISUSE.

But now it seems, that this 3.6.23.1 change is causing problems with a
different set of poorly (read: incorrectly) coded (smartphone)
applications.  We are told that there are many apps that terminate their
loop upon receiving SQLITE_MISUSE rather than SQLITE_DONE.  When those
applications are linked against newer versions of SQLite, their loops spin
forever since the sqlite3_step() now resets automatically and never returns
SQLITE_MISUSE.

The proposed solution is to modify the behavior of sqlite3_step() yet again
so that it only automatically resets after returning either the SQLITE_BUSY
or SQLITE_LOCKED error.  Any call to sqlite3_step() after returning
SQLITE_DONE or some error other than BUSY or LOCKED will revert generating
an SQLITE_MISUSE, just as it did for all versions of SQLite prior to
3.6.23.1.

This is, technically, a compatibility break.  On the other hand, there
appear to be vast numbers of smartphone applications that currently depend
on undefined behavior and will suddenly stop working if we don't make this
change.

So the question to you, gentle reader, is should we make this change, and
break backwards compatibility, albeit in a very obscure way, or should we be
hard-nosed and force hundreds or perhaps thousands of smartphone application
developers fix their programs?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fill blank fields

[Igor Tandetnik]

On 1/10/2011 5:04 PM, CDN Mark wrote:
> using this exampleUPDATE Aircraft SET CN = '*' where (CN = '' or CN is null) 
> and MyPrimaryKey='xyz';can you substitute another column name iso MyPrimaryKey

You can substitute any logical combination on any set of columns in 
Aircraft table.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with SQLite and VB2010 Express

[Hotmail (terreaultguy)]

Hi, Simon
I am a SQLite user. And before I can get to ask questions about SQLite 
Database, I must be able to use it with Visual Studio 2010.
The tools from SQLite are not up to par.

So I think all questions about SQLite and the environment it can be used are 
SQLite User Question.
I will always answer them, if I can help. And if I know of another Forum 
that answers the better will provide it.

So do not be so sanctimonious.

Guy
-Original Message- 
From: Simon Slavin
Sent: Monday, January 10, 2011 5:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Help with SQLite and VB2010 Express


On 10 Jan 2011, at 9:34pm, Bob Keeland wrote:

> Obviously I don't know what I'm doing with SQLite so I'll ask the question 
> that shows my ignorance.
> What is the difference between what can or should be asked on this list 
> and what should be directed to the System.Data.SQLite forum?

The easiest way is to pretend that the people on this list (the sqlite-users 
one) have never used your operating system or programming language, and the 
people on the System.Data.SQLite forum have never used the advanced features 
of SQLite.  The question you asked refers in detail to .NET, particular 
setup procedures, and OleDB.  None of those things are part of SQLite, and 
none of them can be downloaded from the SQLite home site.  From our point of 
view, they're not really part of the subject matter of this list.

It's possible that you'll find someone on this list who can answer your 
question.  But it's more likely that you'll get better help from the other 
list because those are the people who understand all the technical terms you 
used.  Had your question been about the syntax of a SQLite command, this 
list would be better.

>  They seem to have duplicate purposes - helping people who are having 
> problems.

I read quite a few technical fora like that but they don't have much 
overlap.  People who read one of the lists won't help me with the 
subject-matter of another list because they just don't know about it, and 
the question would be off-charter anyway.

Simon.
___
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 with SQLite and VB2010 Express

[Bob Keeland]

Thank you for the reply, it makes more sense now. I'll go to the forum on 
System.Data.SQLite.
BobK

--- On Mon, 1/10/11, Simon Slavin  wrote:


From: Simon Slavin 
Subject: Re: [sqlite] Help with SQLite and VB2010 Express
To: "General Discussion of SQLite Database" 
Date: Monday, January 10, 2011, 4:49 PM



On 10 Jan 2011, at 9:34pm, Bob Keeland wrote:

> Obviously I don't know what I'm doing with SQLite so I'll ask the question 
> that shows my ignorance.
> What is the difference between what can or should be asked on this list and 
> what should be directed to the System.Data.SQLite forum?

The easiest way is to pretend that the people on this list (the sqlite-users 
one) have never used your operating system or programming language, and the 
people on the System.Data.SQLite forum have never used the advanced features of 
SQLite.  The question you asked refers in detail to .NET, particular setup 
procedures, and OleDB.  None of those things are part of SQLite, and none of 
them can be downloaded from the SQLite home site.  From our point of view, 
they're not really part of the subject matter of this list.

It's possible that you'll find someone on this list who can answer your 
question.  But it's more likely that you'll get better help from the other list 
because those are the people who understand all the technical terms you used.  
Had your question been about the syntax of a SQLite command, this list would be 
better.

>  They seem to have duplicate purposes - helping people who are having 
>problems.

I read quite a few technical fora like that but they don't have much overlap.  
People who read one of the lists won't help me with the subject-matter of 
another list because they just don't know about it, and the question would be 
off-charter anyway.

Simon.
___
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 with SQLite and VB2010 Express

[Simon Slavin]

On 10 Jan 2011, at 9:34pm, Bob Keeland wrote:

> Obviously I don't know what I'm doing with SQLite so I'll ask the question 
> that shows my ignorance.
> What is the difference between what can or should be asked on this list and 
> what should be directed to the System.Data.SQLite forum?

The easiest way is to pretend that the people on this list (the sqlite-users 
one) have never used your operating system or programming language, and the 
people on the System.Data.SQLite forum have never used the advanced features of 
SQLite.  The question you asked refers in detail to .NET, particular setup 
procedures, and OleDB.  None of those things are part of SQLite, and none of 
them can be downloaded from the SQLite home site.  From our point of view, 
they're not really part of the subject matter of this list.

It's possible that you'll find someone on this list who can answer your 
question.  But it's more likely that you'll get better help from the other list 
because those are the people who understand all the technical terms you used.  
Had your question been about the syntax of a SQLite command, this list would be 
better.

>  They seem to have duplicate purposes - helping people who are having 
> problems.

I read quite a few technical fora like that but they don't have much overlap.  
People who read one of the lists won't help me with the subject-matter of 
another list because they just don't know about it, and the question would be 
off-charter anyway.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fill blank fields

[CDN Mark]

using this exampleUPDATE Aircraft SET CN = '*' where (CN = '' or CN is null) 
and MyPrimaryKey='xyz';can you substitute another column name iso MyPrimaryKey 
if the column you wantedto reference to wasn't the primary key, for example the 
field in a column named AT,as in:UPDATE Aircraft SET CN = '*' where (CN = '' or 
CN is null) and AT='xyz';mtia Mark
CDN Mark  wrote:
> what I need to do is replace blank fields in a specific row, sort of
> a double where where statement as in:
> 
> UPDATE Aircraft SET CN = '*' where CN = '' or CN is null where (primary key)
> is xyz

UPDATE Aircraft SET CN = '*' where (CN = '' or CN is null) and 
MyPrimaryKey='xyz';
-- 
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with SQLite and VB2010 Express

[Hotmail (terreaultguy)]

Hi, Bob

I had problems to when i installed SQLite-1.0.66.0-setup specially with apps 
that I had created with
SQLite-1.0.65.0-setup

and also because of the framework 4.0, for that there is a work around to 
modify in the config file of the app.

I will try to find the details and report back to you

cause now every thing works fine now

Be patient,
Guy

-Original Message- 
From: Bob Keeland
Sent: Monday, January 10, 2011 4:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Help with SQLite and VB2010 Express

Obviously I don't know what I'm doing with SQLite so I'll ask the question 
that shows my ignorance.
What is the difference between what can or should be asked on this list and 
what should be directed to the System.Data.SQLite forum? They seem to have 
duplicate purposes - helping people who are having problems.

BobK


> You really don't need to run the msi. You can download the binary
> package and added the dlls as references and start using the
> dataprovider. In the future, this sort of question should be asked in
> the System.Data.SQLite forum.

On Mon, Jan 10, 2011 at 12:10 PM, Bob Keeland  wrote:
> I've been struggling with how to use SQLite with Visual Basic2010 Express. 
> VB2010 Express does not work with Microsoft Access (which I've used in the 
> past) and so I have to use SQL. I don't feel that I need the capabilities 
> of Microsoft SQL Server and so I've been looking at SQLite and MySQL. I've 
> got System.Data.SQLite (VB.NET - ADO.NET provider on how to connect to 
> SQLite within Visual Basic) from the forum and I've got 
> "SQLite-1.0.66.0-setup" but when I run it I get a message that says that 
> it will switch the OleDB connection in VB2010 to run SQLite instead of 
> Access. Then, it gives me an error message that it cannot read;
>   "c:\ . . . . \AppData\Local\Temp\tmpB3B6.tmp.msi"
>
> Does anyone know why I get this message? Do I need to buy Visual Studio 
> 2010 Professional in order to do database work? Is the VB2010 Express just 
> too limited???
>
> BobK
>
>
>
>
> ___
> 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




___
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 with SQLite and VB2010 Express

[Bob Keeland]

Obviously I don't know what I'm doing with SQLite so I'll ask the question that 
shows my ignorance.
What is the difference between what can or should be asked on this list and 
what should be directed to the System.Data.SQLite forum? They seem to have 
duplicate purposes - helping people who are having problems.
 
BobK


> You really don't need to run the msi. You can download the binary
> package and added the dlls as references and start using the
> dataprovider. In the future, this sort of question should be asked in
> the System.Data.SQLite forum.

On Mon, Jan 10, 2011 at 12:10 PM, Bob Keeland  wrote:
> I've been struggling with how to use SQLite with Visual Basic2010 Express. 
> VB2010 Express does not work with Microsoft Access (which I've used in the 
> past) and so I have to use SQL. I don't feel that I need the capabilities of 
> Microsoft SQL Server and so I've been looking at SQLite and MySQL. I've got 
> System.Data.SQLite (VB.NET - ADO.NET provider on how to connect to SQLite 
> within Visual Basic) from the forum and I've got "SQLite-1.0.66.0-setup" but 
> when I run it I get a message that says that it will switch the OleDB 
> connection in VB2010 to run SQLite instead of Access. Then, it gives me an 
> error message that it cannot read;
>   "c:\ . . . . \AppData\Local\Temp\tmpB3B6.tmp.msi"
>
> Does anyone know why I get this message? Do I need to buy Visual Studio 2010 
> Professional in order to do database work? Is the VB2010 Express just too 
> limited???
>
> BobK
>
>
>
>
> ___
> 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



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] joining fts table with normal table

[Rael Bauer]

Hi,

The documentation mentions the possibility of joining a fts table with a normal 
table via rowid.
(e.g.   select sender, subject from email join email_text on email.rowid = 
email_text.rowid where body match 'jam';)


Can this join only be done via the rowid field? I tried using an
"id" field (my own id INTEGER field) - i.e when I insert the fts
record, I set the rowid to id of the other table, and then tried join
with e.g. email.rowid = email_text.id, but get an error. It seems this is not 
possible, but I'm not sure why there should be a distinction.


Thanks
Rael


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with SQLite and VB2010 Express

[Roosevelt Anderson]

You really don't need to run the msi. You can download the binary
package and added the dlls as references and start using the
dataprovider. In the future, this sort of question should be asked in
the System.Data.SQLite forum.

On Mon, Jan 10, 2011 at 12:10 PM, Bob Keeland  wrote:
> I've been struggling with how to use SQLite with Visual Basic2010 Express. 
> VB2010 Express does not work with Microsoft Access (which I've used in the 
> past) and so I have to use SQL. I don't feel that I need the capabilities of 
> Microsoft SQL Server and so I've been looking at SQLite and MySQL. I've got 
> System.Data.SQLite (VB.NET - ADO.NET provider on how to connect to SQLite 
> within Visual Basic) from the forum and I've got "SQLite-1.0.66.0-setup" but 
> when I run it I get a message that says that it will switch the OleDB 
> connection in VB2010 to run SQLite instead of Access. Then, it gives me an 
> error message that it cannot read;
>   "c:\ . . . . \AppData\Local\Temp\tmpB3B6.tmp.msi"
>
> Does anyone know why I get this message? Do I need to buy Visual Studio 2010 
> Professional in order to do database work? Is the VB2010 Express just too 
> limited???
>
> BobK
>
>
>
>
> ___
> 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

[sqlite] Help with SQLite and VB2010 Express

[Bob Keeland]

I've been struggling with how to use SQLite with Visual Basic2010 Express. 
VB2010 Express does not work with Microsoft Access (which I've used in the 
past) and so I have to use SQL. I don't feel that I need the capabilities of 
Microsoft SQL Server and so I've been looking at SQLite and MySQL. I've got 
System.Data.SQLite (VB.NET - ADO.NET provider on how to connect to SQLite 
within Visual Basic) from the forum and I've got "SQLite-1.0.66.0-setup" but 
when I run it I get a message that says that it will switch the OleDB 
connection in VB2010 to run SQLite instead of Access. Then, it gives me an 
error message that it cannot read;
  "c:\ . . . . \AppData\Local\Temp\tmpB3B6.tmp.msi"
 
Does anyone know why I get this message? Do I need to buy Visual Studio 2010 
Professional in order to do database work? Is the VB2010 Express just too 
limited???
 
BobK
 


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/10/2011 06:41 AM, Adam DeVita wrote:
> The HASP envelope does prevent an executable from running with a
> debugger open.

Only in simple cases.  Since the executable and the key are in the hands
(and total control) of the adversary, they can do anything.  For example
they can emulate/virtualize the process.  It will stop someone with a copy
of Visual Studio but will not stop someone determined and skilled.

My preferred way of looking at this kind of thing is how much would an
adversary charge to break things.  This HASP thing changes it from a few
hundred dollars to a few thousand.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk0rNOQACgkQmOOfHg372QSb8QCgl68DWt2EBFon1z7GjUVtCfyR
1xYAoMi4Gd7I2yOu0Mx6J+x3z7L+dMMF
=ENvt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use distinct in group_concat, together with specified separator

[Simon Davies]

On 10 January 2011 15:18, smart sl  wrote:
> Thank you. It's an indirect way, and it works. Could it be the same
> efficient as "select group_concat(distinct data separator '-') from tst"
> like in mysql? I'm not quite understand the actual functionality inside, so
> this is significant to me.

I know nothing of the internals of MySql; but although the syntax is
different and the SQLite solution looks 'indirect', the processing
necessary to generate the result set is basically the same.

>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use distinct in group_concat, together with specified separator

[smart sl]

Thank you. It's an indirect way, and it works. Could it be the same
efficient as "select group_concat(distinct data separator '-') from tst"
like in mysql? I'm not quite understand the actual functionality inside, so
this is significant to me.

2011/1/10 Simon Davies 

> On 10 January 2011 14:22, smart sl  wrote:
> > Is there any one can help?
>
> SQLite version 3.6.11
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tst( data integer );
> sqlite>
> sqlite> insert into tst values( 1 );
> sqlite> insert into tst values( 2 );
> sqlite> insert into tst values( 3 );
> sqlite> insert into tst values( 2 );
> sqlite>
> sqlite> select group_concat( d, '-' ) from ( select distinct data d from
> tst );
> 1-2-3
> sqlite>
>
> >
> > 2011/1/7 smart sl 
> >
> >> In "Aggregate Functions" of SQLite Docs, it's said that:
> >> In any aggregate function that takes a single argument, that argument
> can
> >> be preceded by the keyword DISTINCT.
> >>
> >> Then how about two arguments circumstance when I want to use distinct
> and
> >> together with specified separator. Is there a way to do that?
> >>
> >> I'm using SQLite 3.7.3 in tcl. Thanks advance.
> >>
>
> Regards,
> Simon
> ___
> 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] How to use distinct in group_concat, together with specified separator

[Simon Davies]

On 10 January 2011 14:22, smart sl  wrote:
> Is there any one can help?

SQLite version 3.6.11
Enter ".help" for instructions
sqlite>
sqlite> create table tst( data integer );
sqlite>
sqlite> insert into tst values( 1 );
sqlite> insert into tst values( 2 );
sqlite> insert into tst values( 3 );
sqlite> insert into tst values( 2 );
sqlite>
sqlite> select group_concat( d, '-' ) from ( select distinct data d from tst );
1-2-3
sqlite>

>
> 2011/1/7 smart sl 
>
>> In "Aggregate Functions" of SQLite Docs, it's said that:
>> In any aggregate function that takes a single argument, that argument can
>> be preceded by the keyword DISTINCT.
>>
>> Then how about two arguments circumstance when I want to use distinct and
>> together with specified separator. Is there a way to do that?
>>
>> I'm using SQLite 3.7.3 in tcl. Thanks advance.
>>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required

[Adam DeVita]

Just to add my $0.02

We use http://www.safenet-inc.com/ HASP HL Encryption.  (We use HASP keys
for end user products so it was 'free' to my internal product tracking
system db.) This key allows the exe to get encrypted and optionally a data
file as well.

The encryption of the program provides us with some security against a
password being saved within the exe in clear form.   When it comes to data
encryption though, the performance penalty we suffer is 2x to 4x.  Also,
HASP HL data encryption + Sqlite + Windows 7,  64 bit editions don't work
reliably.  The HASP envelope does prevent an executable from running with a
debugger open. It may be that newer versions of compiler or key will work,
but I can't say that they will (nor does safenet's technical "support"
actually provide answers).   Bitter experience so far says "Don't use HASP
for data encryption."

Adam




On Sun, Jan 9, 2011 at 5:40 PM, Simon Slavin  wrote:

>
> On 9 Jan 2011, at 5:29pm, Roger Binns wrote:
>
> > I think you misunderstand how the SQLite encryption extension works.  The
> on
> > disk storage format for SQLite is a series of fixed sized pages.  The
> > extension transparently encrypts each page on writing to disk and
> decrypts
> > on reading.  To use it you open/attach a database and then provide the
> > password either via a C API or a pragma.  You just make regular SQLite
> API
> > calls and everything just works.
> >
> >  http://www.hwaci.com/sw/sqlite/see.html
> >
> > The various other ones pointed out do something similar but since you go
> via
> > their API layers they intersperse code to do encryption.  I found it very
> > hard to work out what they did for encryption since things like the
> > algorithm used, IV (the usual weakness for home grown implementations)
> etc
> > do matter.  They also make other choices:
>
> As far as I can work out, the two solutions he pointed to encrypt at the
> field level.  So if you understand the file structure of an SQLite database
> you can, for example, work out which records have the same values in either
> within a table or as across tables.  It also gives you a handy-dandy
> plain/crypt pair since you will know that certain fields definitely start
> with 'CREATE TABLE ' and such things.
>
> On the other hand, these solutions are cheaper than the hwaci one.  As with
> most encryption it depends how much effort you think the enemy will devote
> to attacking your technique.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use distinct in group_concat, together with specified separator

[smart sl]

Is there any one can help?

2011/1/7 smart sl 

> In "Aggregate Functions" of SQLite Docs, it's said that:
> In any aggregate function that takes a single argument, that argument can
> be preceded by the keyword DISTINCT.
>
> Then how about two arguments circumstance when I want to use distinct and
> together with specified separator. Is there a way to do that?
>
> I'm using SQLite 3.7.3 in tcl. Thanks advance.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL

[Stephen Chrzanowski]

There are a few practices you can use depending on how you want things to
work.

The way I deal with things is that if there is any sort of user interaction,
checks and balances should be done at the UI end.  Which means that if you
require something to be filled in, don't let the user continue on.

Another two ways to do this would be to have the script/language/whatever
handle the exception that the database throws when trying to insert a NULL
value into a field not allowed to have a NULL field.

I use SQLite Expert myself, and there is no flaw with it in regards to
inserting NULL values in a field with a NOT NULL assignment.  It throws the
exception as it should.

On Fri, Jan 7, 2011 at 6:33 PM, tinauser  wrote:

> Thanks for the answer.
> I'm using the firefox add-on(sqlite manager).I wrongly guessed that
> when creating a table with a field defined as follows:
> "start_script" TEXT NOT NULL
> so without default, the default would be NULL, while apparently is an
> empty string. Indeed, if I try to insert NULL i got an exception.
> So, if I want to force the user to insert something, should I add as
> default NULL?Or is this a problem with SQLite manager?
> Thanks for helping!
>
> On Jan 6, 7:09 pm, Stephen Chrzanowski  wrote:
> > insert into YourTable (Field1, NotNullField) values ('Empty String','');
> > insert into YourTable (Field1, NotNullField) values ('Null Value',NULL);
> >
> > The first insert one will work, the second will not.
> >
> > An empty string is NOT considered a NULL value.  An empty string is
> actually
> > considered very valid data.  A value of NULL means that there is no data
> > associated to that particular field.
> >
> > On Wed, Jan 5, 2011 at 12:27 PM, tinauser  wrote:
> > > Hi there,
> > > I'm an inexpert user of SQLite.
> > > I create a table with a field NOT NULL
> > > However i succesfully insert a record with the NOT NULL field empty.
> > > Shouldn't this be "forbidden"?
> > > Thanks
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@sqlite.org
> > >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.orghttp://
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Bulk Insert in SQLite

[Simon Slavin]

On 10 Jan 2011, at 1:10pm, Sunil Bhardwaj wrote:

> We have large number of records in memory and we are using SQLite in-memory 
> using C++.  
> 
> How can we use Bulk Insert to speed up the record insertion process in SQLite 
> in one transaction.

CREATE TABLE ...
BEGIN TRANSACTION
INSERT ...
INSERT ...
INSERT ...
END TRANSACTION
CREATE INDEX ...

If your table already exists and you're doing a lot of transactions it may be 
faster to drop the indexes before you start the transaction and remake them 
when it's finished.  But this is not certain: try it both ways and see which is 
faster in your case.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using Bulk Insert in SQLite

[Sunil Bhardwaj]

Hi

We have large number of records in memory and we are using SQLite in-memory 
using C++.  

How can we use Bulk Insert to speed up the record insertion process in SQLite 
in one transaction.

Please suggest.

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl interface to the int sqlite3_open_v2 funtion's SQLITE_OPEN_READONLY flag?

[Simon Slavin]

On 10 Jan 2011, at 1:01pm, Fredrik Karlsson wrote:

> I notice that the int sqlite3_open_v2 C function has a
> SQLITE_OPEN_READONLY which allows opening a database in read only
> mode.
> Would it be possible to have this possibility in the Tcl interface
> too? Perhaps similar to the way the "open" command of Tcl specifies
> flags.

Actually I was just wanting something like

ATTACH DATABASE READONLY filename

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tcl interface to the int sqlite3_open_v2 funtion's SQLITE_OPEN_READONLY flag?

[Fredrik Karlsson]

Hi,

I notice that the int sqlite3_open_v2 C function has a
SQLITE_OPEN_READONLY which allows opening a database in read only
mode.
Would it be possible to have this possibility in the Tcl interface
too? Perhaps similar to the way the "open" command of Tcl specifies
flags.

This feature would be very useful as I have parts of my package that
should NEVER be able to modify the underlying database.
Right now I am jumping through lots and lots of Tcl loops in order to
avoid any possibility of writes in not trusted areas in the code, and
I cant help feeling that a read only database handle would be much
more sensible.

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max length of integer?

[luuk34]

On 10-01-11 13:09, Simon Slavin wrote:
> On 10 Jan 2011, at 10:05am, KimTaein wrote:
>
>> What is the largest number that I can store in integer data type in sqlite?
>> I was not able to find it on sqlite website.
> You're right.  It's not in the obvious place.  You can, however, find it here:
>
> 
>
> If I understand it correctly, integers can take up 64 bits, so the largest 
> number would be about
>
> 1.84467441e19
>
sqlite> select typeof((9223372036854775807)) ;
integer
sqlite> select typeof((9223372036854775808)) ;
real
sqlite>

so, i think, its 2^64-1


Luuk


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max length of integer?

[Jay A. Kreibich]

On Mon, Jan 10, 2011 at 12:09:02PM +, Simon Slavin scratched on the wall:
> 
> On 10 Jan 2011, at 10:05am, KimTaein wrote:
> 
> > What is the largest number that I can store in integer data type in sqlite?

  Integers are 64-bit signed values, so the range is -2^63 to (2^63 - 1).

> > I was not able to find it on sqlite website.

  It doesn't come right out an list a min and max value, but the
  data-types page indicates integers are 64-bit (8 byte) signed
  values:   http://www.sqlite.org/datatype3.html

> If I understand it correctly, integers can take up 64 bits, so the
> largest number would be about
> 
> 1.84467441e19

  Half that (+/-9.223e18).  Integers are always signed.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max length of integer?

[Simon Slavin]

On 10 Jan 2011, at 10:05am, KimTaein wrote:

> What is the largest number that I can store in integer data type in sqlite?
> I was not able to find it on sqlite website.

You're right.  It's not in the obvious place.  You can, however, find it here:



If I understand it correctly, integers can take up 64 bits, so the largest 
number would be about

1.84467441e19

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] max length of integer?

[KimTaein]

What is the largest number that I can store in integer data type in sqlite?
I was not able to find it on sqlite website.
 
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_DEFAULT_FILE_PERMISSIONS

[Jonathan H N Chin]

I have a linux application with daemons running under several uids.
I wish them all to be able to access and modify an SQLite database.
I am using the Perl DBI SQLite interface on a debian system.
However, regardless of how permissively I set the umask in my code,
the sqlite code will not create a file with permissions more lax
than rw-r--r--.

I see in the SQLite source:

> #ifndef SQLITE_DEFAULT_FILE_PERMISSIONS
> # define SQLITE_DEFAULT_FILE_PERMISSIONS 0644
> #endif

and associated code, which I guess is causing my problem.


I found a single mailing list post from 23 Feb 2007:

> Re: [sqlite] about default file permission of SQLite database file
> John Stanton
> Fri, 23 Feb 2007 07:33:01 -0800
> It would be better to make the create permission 0666. Then umask
> can restrict that permission and the user can get whatever permission
> required by the application.

but clearly this change has never been made.


Please would someone in the know explain to me why the SQLite
designers believe that SQLite should override umask settings.

Such an override is rather unexpected. Clearly, I could recompile
my own version of the sqlite packages, but this is extra work so
it would be helpful to know why this design decision was taken.


-jonathan

-- 
 Jonathan H N Chin   | deputy computer | Newton Institute, Cambridge, UK
 | systems manager | tel/fax: +44 1223 767091/330508
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Bulk Insert in SQLite

[Alok Singh]

*here are summary code :inserting 100k in 5-7 sec*
*
*
//Reading text file 1 in
yarray2= filetext.Split(Environment.NewLine)
//Reading text file 1 in
yarray1 = filetext1.Split(Environment.NewLine)

insert:
Dim tx = cons.BeginTransaction()
For y = j To x
Dim strval
strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''")
strval = Replace(strval, vbTab, "','")
myparam.Value = strval
cmd.CommandText = "INSERT into " & ticket & " VALUES('" & strval
& "')"
//' REMOVED cmd.Parameters.Add(myparam)
cmd.ExecuteNonQuery()

Next
tx.Commit()
   Array.Clear(yarray, 0, x) ' // FREE ARRAY 1
Array.Clear(yarray1, 0, x) ' // FREE ARRAY 1
tx.Dispose()
j = x + 1
x = x + 1
If x < Frow1 Then
GoTo insert
Else
tx = cons.BeginTransaction()
Dim m As Integer
m = x - 1 + 1
For y = m To Frow1

Dim strval
strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''")
strval = Replace(strval, vbTab, "','")
myparam.Value = strval
cmd.CommandText = "INSERT into " & ticket & " VALUES('" &
strval & "')"
'cmd.Parameters.Add(myparam)
cmd.ExecuteNonQuery()
Next
tx.Commit()
End If

Regards,
Alok



On 10 January 2011 15:55, Sunil Bhardwaj wrote:

> Hi
>
> Please suggest, how can we implement Bulk Insert in SQLite.
>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Thanks
Alok Kumar Singh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Bulk Insert in SQLite

[Tito Ciuro]

Sunil,

Google is your friend:

http://www.google.com/search?client=safari&rls=en&q=bulk+insert+sqlite&ie=UTF-8&oe=UTF-8

Regards,

-- Tito

On Jan 10, 2011, at 11:25 AM, Sunil Bhardwaj wrote:

> Hi
> 
> Please suggest, how can we implement Bulk Insert in SQLite.
> 
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910
> ___
> 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

[sqlite] Using Bulk Insert in SQLite

[Sunil Bhardwaj]

Hi

Please suggest, how can we implement Bulk Insert in SQLite.

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on breaking trigger recursion?

[Nicolas Williams]

On Fri, Jan 07, 2011 at 09:54:07PM -0600, Nicolas Williams wrote:
> On Sat, Jan 08, 2011 at 01:29:23AM +, Simon Slavin wrote:
> > On 8 Jan 2011, at 1:12am, Nicolas Williams wrote:
> > > I need to use recursive triggers.  In some cases I want to "normalize"
> > > values of some columns of NEW being INSERTed or UPDATEd, but there's no
> > > UPDATE syntax for changing NEW, thus I can't write something like:
> > 
> > 
> > CREATE TRIGGER fred INSTEAD OF INSERT ON myTable ...
> 
> Oh, for some reason I thought that INSTEAD OF triggers were for views
> only, but that's not the case.  [...]

Actually, I was unable to get SQLite3 to create INSTEAD OF triggers on
tables.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculate difference of two times and show the result as double value

[Bart Smissaert]

As you are using Acccess maybe you are using Olaf Schmidt's VB wrapper
dhRichclient3 and in that case you do things like this:

select
(cast(left$('16:30:00', 2) as integer) +
cast(mid$('16:30:00', 4, 2) as real) / 60) -
(cast(left$('08:00:00', 2) as integer) +
cast(mid$('08:00:00', 4, 2) as real) / 60)

Native SQLite with Julianday etc. will probably be quicker.

RBS


On Mon, Jan 10, 2011 at 7:42 AM, Sven  wrote:
> Dear SQLite users,
>
> I've created a Microsoft Access 2003 database with three complex queries
> written in pure SQL language. The function of the database is to
> document and calculate the daily work-time.
>
> Today I want to convert this database to SQLite but I have problems with
> my written queries: How can I calculate the difference of two times and
> show the result as double value?
>
> For more information I'll include one example: One table hold the two
> times (start_time, end_time) and the calculated result (shown in the
> table as 'difference'):
>
> [code]
> start_time | end_time | difference
> 08:00:00 | 16:30:00 | 8.5
> [/code]
>
> The result of the difference between the two times (start_time,
> end_time) shown in the table above as 'difference' will be calculated
> with the following SQL-query code within Microsoft Access 2003:
>
> [code]
> ( [end_time] - [start_time] ) * 24
> [/code]
>
> and returns the following data '8.5'. While using the following query
> code within SQLite:
>
> [code]
> select time(end_time) - time(start_time)
> [/code]
>
> I only get the following data returned: '8' (but not '8.5' as calculated
> with SQL).
>
> So currently I don't know what to do next to solve my problem and
> perhaps all the other users of this SQLite-users mailing list could help
> me out.
>
> best regards
> ___
> 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] Using local variables through sqlite

[venkat easwar]

Thanks Igor,

I coded in the second syntax previously, but the first syntax looks good for 
me. 
I will use that one for my project.

Thanks for helping me out guys.

Cheers
Venkat





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Sat, January 8, 2011 3:33:17 AM
Subject: Re: [sqlite] Using local variables through sqlite

On 1/7/2011 4:45 PM, Venkat Victorious wrote:
> On Fri, Jan 7, 2011 at 5:43 AM, BareFeetWare
>   wrote:
>> This is sometimes called "re-injection", where you're extracting the
>> results of one query, only to re-inject it into another query. In SQL, this
>> is a very inefficient way to do it. Most situations like this can be better
>> handled by combining the select and insert into one SQL command.
>>
> Combining select and insert will be useful if i am inserting from same
> table.

It works just as well between tables.

> Will this work even with inserting three values when one is a
> constant (something like 12), other one is variable from one table and third
> one is variable from some other table.

Yes.

> will
> the following thing work
>
> insert into  (a,b,c) select 1,b from  where
> , c from  where;

insert into TargetTable (a, b, c)
select 1, table1.b, table2.c
from table1, table2
where ;

-- or

select into TargetTable(a, b, c) values (1,
   (select b from table1 where ),
   (select c from table2 where ));

The first syntax allows inserting multiple records in a single 
statement, the second always inserts exactly one record.
-- 
Igor Tandetnik

___
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] calculate difference of two times and show the result as double value

[luuk34]

On 10-01-11 08:42, Sven wrote:
> Dear SQLite users,
>
> I've created a Microsoft Access 2003 database with three complex queries 
> written in pure SQL language. The function of the database is to 
> document and calculate the daily work-time.
>
> Today I want to convert this database to SQLite but I have problems with 
> my written queries: How can I calculate the difference of two times and 
> show the result as double value?
>
> For more information I'll include one example: One table hold the two 
> times (start_time, end_time) and the calculated result (shown in the 
> table as 'difference'):
>
> [code]
> start_time | end_time | difference
> 08:00:00 | 16:30:00 | 8.5
> [/code]
>
> The result of the difference between the two times (start_time, 
> end_time) shown in the table above as 'difference' will be calculated 
> with the following SQL-query code within Microsoft Access 2003:
>
> [code]
> ( [end_time] - [start_time] ) * 24
> [/code]
>
> and returns the following data '8.5'. While using the following query 
> code within SQLite:
>
> [code]
> select time(end_time) - time(start_time)
> [/code]
>
> I only get the following data returned: '8' (but not '8.5' as calculated 
> with SQL).
>
> So currently I don't know what to do next to solve my problem and 
> perhaps all the other users of this SQLite-users mailing list could help 
> me out.
>
> best regards
select (strftime('%s', time('now', '4 minutes')) - strftime('%s',
time('now')));
returns: 240(4*60)

This was found via Google at:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56060.html

More help on the strftime function:
http://sqlite.org/lang_datefunc.html
http://pubs.opengroup.org/onlinepubs/007908799/xsh/strftime.html

-- 
Luuk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculate difference of two times and show the result as double value

[Simon Davies]

On 10 January 2011 07:42, Sven  wrote:
> Dear SQLite users,
>
> I've created a Microsoft Access 2003 database with three complex queries
> written in pure SQL language. The function of the database is to
> document and calculate the daily work-time.
.
.
.
> and returns the following data '8.5'. While using the following query
> code within SQLite:
>
> [code]
> select time(end_time) - time(start_time)
> [/code]
>
> I only get the following data returned: '8' (but not '8.5' as calculated
> with SQL).
>
> So currently I don't know what to do next to solve my problem and
> perhaps all the other users of this SQLite-users mailing list could help
> me out.

http://www.sqlite.org/lang_datefunc.html

select ( julianday( '16:30:00' ) * 24.0 ) - ( julianday( '08:00:00' ) * 24.0 );

>
> best regards

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users