Re: [sqlite] calculate difference of two times and show the result as double value
On 10 January 2011 07:42, Sven tabaluga...@yahoo.de 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
Re: [sqlite] calculate difference of two times and show the result as double value
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] Using local variables through sqlite
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 itandet...@mvps.org 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, BareFeetWarelist@barefeetware.com 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 intotarget table (a,b,c) select 1,b fromtable1 where condition, c fromtable2 wherecondition; insert into TargetTable (a, b, c) select 1, table1.b, table2.c from table1, table2 where condition; -- or select into TargetTable(a, b, c) values (1, (select b from table1 where condition1), (select c from table2 where condition2)); 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
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 tabaluga...@yahoo.de 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] Advice on breaking trigger recursion?
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
[sqlite] Using Bulk Insert in SQLite
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] Using Bulk Insert in SQLite
Sunil, Google is your friend: http://www.google.com/search?client=safarirls=enq=bulk+insert+sqliteie=UTF-8oe=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
Re: [sqlite] Using Bulk Insert in SQLite
*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 sbhard...@ipolicynetworks.comwrote:
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
[sqlite] SQLITE_DEFAULT_FILE_PERMISSIONS
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 jc...@newton.ac.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
[sqlite] max length of integer?
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
Re: [sqlite] max length of integer?
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: http://www.sqlite.org/fileformat.html#varint_format 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
Re: [sqlite] max length of integer?
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?
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: http://www.sqlite.org/fileformat.html#varint_format 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
[sqlite] Tcl interface to the int sqlite3_open_v2 funtion's SQLITE_OPEN_READONLY flag?
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] Tcl interface to the int sqlite3_open_v2 funtion's SQLITE_OPEN_READONLY flag?
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] Using Bulk Insert in SQLite
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] Using Bulk Insert in SQLite
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
Re: [sqlite] NOT NULL
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 tinau...@libero.it 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 pontia...@gmail.com 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 tinau...@libero.it 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] How to use distinct in group_concat, together with specified separator
Is there any one can help? 2011/1/7 smart sl smar...@gmail.com 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] R: R: Crypto lib for Sqlite - suggest required
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 slav...@bigfraud.org 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
On 10 January 2011 14:22, smart sl smar...@gmail.com 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 smar...@gmail.com 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] How to use distinct in group_concat, together with specified separator
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 simon.james.dav...@gmail.com On 10 January 2011 14:22, smart sl smar...@gmail.com 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 smar...@gmail.com 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
On 10 January 2011 15:18, smart sl smar...@gmail.com 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] R: R: Crypto lib for Sqlite - suggest required
-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
[sqlite] Help with SQLite and VB2010 Express
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] Help with SQLite and VB2010 Express
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 keela...@yahoo.com 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] joining fts table with normal table
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
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 keela...@yahoo.com 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
Re: [sqlite] Help with SQLite and VB2010 Express
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 keela...@yahoo.com 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
[sqlite] fill blank fields
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 cdn.mark at virgin.net 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
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
Re: [sqlite] Help with SQLite and VB2010 Express
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 slav...@bigfraud.org wrote: From: Simon Slavin slav...@bigfraud.org Subject: Re: [sqlite] Help with SQLite and VB2010 Express To: General Discussion of SQLite Database sqlite-users@sqlite.org 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
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] fill blank fields
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
[sqlite] Propose minor incompatible API change
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] Propose minor incompatible API change
On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote: snip 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
Re: [sqlite] Propose minor incompatible API change
On Mon, Jan 10, 2011 at 8:52 PM, Chris Peachment ch...@ononbb.com wrote: On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote: snip 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
On 1/10/2011 8:52 PM, Chris Peachment wrote: On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote: snip 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
On Mon, Jan 10, 2011 at 9:00 PM, Igor Tandetnik itandet...@mvps.org 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 itandet...@mvps.org wrote: On 1/10/2011 8:52 PM, Chris Peachment wrote: On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote: snip 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] Help with SQLite and VB2010 Express
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
