Re: [sqlite] specifying field type, any benefit?
If you have dot zero(s) values (such as, 4.0) do not save them in an
INTEGER column because then they will lose their float characteristics.
insert into students values(John, 4.0)
select age from students where first_name = 'John'
4 -- got converted to integer
Regards
/sd
Mark
WyszomierskiTo:
sqlite-users@sqlite.org
[EMAIL PROTECTED]cc:
Subject: [sqlite] specifying
field type, any benefit?
01/04/2006 03:54
PM
Please respond to
sqlite-users
Hi all,
I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:
create table students (first_name TEXT, age INTEGER);
I'm just wondering if there is any point to specifying the field type as if
I try adding a string type into the age field, it seems to be accepted ok:
insert into students values('hello');
Does sqlite have any problem regarding setting a field defined as INTEGER
from a text string (any limits etc?), are there any performance gains to be
had with specifying the field type?
Thanks,
Mark
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
Re: [sqlite] using tcl/tk and sqlite
In addition to what D.R. Hipp said, you are also creating and closing your
db before you get the chance to enter any data, what you really need is to
create a procedure to (1) open db (2) enter data and (3) close db every
time the execute button is pressed. Below is a complete working example
of what you want to do. Notice when you use the entry widget you don't
have to worry about using any types of quotes or slashes.
#- create your DB -
load c:/tcla/lib/sqlite/tclsqlite3 sqlite3
sqlite3 db c:/test.sql
db eval {
CREATE TABLE t1(a,b,c);
}
set a 0
set b 0
set c 0
# fire up this proc when the execute button is hit -
proc updateDB {a b c} {
sqlite3 db c:/test.sql ;# open db
db eval {insert into t1 values ($a,$b,$c)} ;# enter data
db close ;# close db
}
#--- create pack your buttons in tcl --
entry .entry1 -width 20 -textvariable a
entry .entry2 -width 20 -textvariable b
entry .entry3 -width 20 -textvariable c
button .button1 -text execute -command {updateDB $a $b $c}
pack .entry1 .entry2 .entry3 .button1
#--- the end --
D. Richard Hipp
[EMAIL PROTECTED] To:
sqlite-users@sqlite.org
cc:
08/01/2005 03:30 Subject: Re: [sqlite] using
tcl/tk and sqlite
PM
Please respond to
sqlite-users
See my comments below.
On Mon, 2005-08-01 at 21:06 +0200, Peter Berkel wrote:
Hallo,
I am not an expert in programming and I try make a frontend
for sqlite using tcl/tk. See example code below.
I have the following problem. I try to entry data which I want to insert
in a sqlite database. The Values of the textvariables a b and c in the
entry widget are set in the insert statement.
What do I wrong and how can I solve the problem so that I can use the
entry widget to insert, modify and delete dat from a sqlite database.
Thanks for helping me out.
Peter Berkel
load tclsqlite3 sqlite3
sqlite3 db test.db
db eval {
CREATE TABLE t1(a,b,c);
}
set a 0
set b 0
set c 0
entry .entry1 -width 20 -textvariable a
entry .entry2 -width 20 -textvariable b
entry .entry3 -width 20 -textvariable c
db eval {insert into t1 values ($a,$b,$c)}
The statement above does the insert, but it does
so immediately, not in response to the button
press. To run this command in response to the
button pressed, do this:
button .button1 -text execute -commmand {
db eval {insert into t1 values($a,$b,$c)}
}
button .button1 -text execute
pack .entry1 .entry2 .entry3 .button1
db close
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of, or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
[sqlite] ORDER BY question
Hello, I'm using sqlite3 and have a list of numbers I'd like returned sorted in increasing order: 1 1.1 1.10 1.11 1.9 10 11 9 12 However, if their column is defined as numeric, the 1.10 gets converted to 1.1 (which is not what I want) and the result (using ORDER BY) is: 1 1.1 1.1 1.11 1.9 9 10 11 12 I tried defining the column as a text and while the 1.10 is preserved, but I get dictionary-style results: 1 1.1 1.10 1.11 1.9 10 11 12 9 Any quick and easy solutions. Thanks, SD The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
Re: [sqlite] ORDER BY question
I tried that but got: no such collation sequence: NUMERIC Dan Kennedy [EMAIL PROTECTED]To: sqlite-users@sqlite.org o.com cc: Subject: Re: [sqlite] ORDER BY question 07/26/2005 02:48 PM Please respond to sqlite-users --- [EMAIL PROTECTED] wrote: Hello, I'm using sqlite3 and have a list of numbers I'd like returned sorted in increasing order: 1 1.1 1.10 1.11 1.9 10 11 9 12 However, if their column is defined as numeric, the 1.10 gets converted to 1.1 (which is not what I want) and the result (using ORDER BY) is: 1 1.1 1.1 1.11 1.9 9 10 11 12 I tried defining the column as a text and while the 1.10 is preserved, but I get dictionary-style results: 1 1.1 1.10 1.11 1.9 10 11 12 9 You could define the column as text and use ORDER BY field COLLATE NUMERIC. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
[sqlite] searching for proper date and time stamps
Hello, I'm using the TCL binding of sqlite3 One of the columns in my table has a date and time stamp, something like this: 07/20/2004 01:35:40 06/20/2005 01:37:01 06/20/2005 12:10:07 06/20/2005 12:17:08 06/20/2005 01:35:00 If I want to get all dates that are later than 06/20/2005 01:00:00 I might try: select c1 from t1 where c1 '06/20/2005 01:35:00' But that also returns All the above including 07/20/2004 01:35:40 because (I suppose) comparison starts from the left and it sees 07 which is than 06. Any ideas or hints in general where I can get the dates that I'm looking for? Thanks The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
[sqlite] error with tclsqlite-3_0_8.zip
I just downloaded tclsqlite-3_0_8.zip and tried to load tclsqlite3.dll but it failed with: couldn't find procedure Tclsqlite_Init Is there something I'm doing wrong? Thanks, Shamil The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
[sqlite] How to handle slashes (\) in text columns
I'm using the TCL binding of SQLITE and was trying to figure out how to
enter various characters into a text column. To enter a slash, all I
normally would do is to append another slash to it. However, this only
works fine if the slash is not the last character in the field. Consider
the following:
% db eval insert into t1 values('\')
%-- no character
% db eval insert into t1 values('\\')
% \\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST')
% \TEST -- returns as expected
% db eval insert into t1 values('TEST\\')
% TEST\\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST\\')
% \\TEST\\ -- now not even the first slash was escaped!!
In short, what I'm asking is how can I insert text with one slash at the
end?
-Shamil D.
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
Re: [sqlite] How to handle slashes (\) in text columns
I'm afraid I still get //
% db eval insert into t1 values('')
% //
Quoting Hell it still is...
-Shamil D.
Lawrence Chitty
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
lworld.comcc:
Subject: Re: [sqlite] How to handle
slashes (\) in text columns
07/20/2004 05:30 PM
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 20, 2004 10:02 PM
Subject: [sqlite] How to handle slashes (\) in text columns
I'm using the TCL binding of SQLITE and was trying to figure out how to
enter various characters into a text column. To enter a slash, all I
normally would do is to append another slash to it. However, this only
works fine if the slash is not the last character in the field. Consider
the following:
% db eval insert into t1 values('\')
%-- no character
% db eval insert into t1 values('\\')
% \\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST')
% \TEST -- returns as expected
% db eval insert into t1 values('TEST\\')
% TEST\\ -- slash did not escape!!
% db eval insert into t1 values('\\TEST\\')
% \\TEST\\ -- now not even the first slash was escaped!!
In short, what I'm asking is how can I insert text with one slash at the
end?
I'm rather rusty on Tcl, but I think that this is what tcl'ers refer to as
quoting hell.
The short answer is that you need 4 slashes, e.g.
db eval insert into t1 values('')
The reason goes something like this - the first pass of the quoted stuff
expands variables and does the slash escaping which leaves us with 2
slashes. The eval then causes the slash escaping to happen again.
There may be better ways to handle this ?. I did a quick search but didn't
come up with any conclusive. You could ask this question on comp.lang.tcl
and see if they have a cleaner suggestion if no-one else here replies.
Lawrence
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16/07/04
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
Re: [sqlite] Read/Write permission
Ops... I forget that replying to a message sent to sqlite-users goes only to the originator and not to the group. Anyway, I looked through sqlite.org but couldn't find how to properly open an sqlite database in read-only mode. If any knows how to do so (preferably in TCL binding), please let me know. -Shamil Darren Duncan [EMAIL PROTECTED]To: [EMAIL PROTECTED] can.net cc: Subject: Re: [sqlite] Read/Write permission 06/09/2004 03:41 PM At 3:35 PM -0400 6/9/04, [EMAIL PROTECTED] wrote: I'm working on a TCL binding of SQLite database, which is sitting somewhere in a shared folder at my company. Few would write to it, but many others will read from it. Therefore, in order to protect that database (and other files in the same location) from accidents, the few that will write to it have read/write permission to that folder, and all others have just read permission. I quickly discovered that those with only read permission to that folder cannot access the SQLite database to be able to read from it! Is this the expect behavior? SQLite is probably trying to open the database in read/write mode by default, which wouldn't work if the folder is read-only. I think you need to change your call to 'open' so that it explicitely says to do it read-only. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Trying to build tclsqlite.dll AND the tclsqlite.c file
bcc32 was inserted because I am using the DOS command prompt. In either case, I don't understand why I get a message telling me it cannot find nmake.cpp I also would like some clarification about the tclsqlite.c file. The TCL binding of SQLite indicates that the current binary version available for download is 2.0 (determined with the package require sqlite command). I suppose that's because tclsqlite.c has the following statement in it: tcl_PkgProvide(interp, sqlite, 2.0); Is it possible that the binary distribution of tclsqlite.dll is really version 2.8 and NOT 2.0? It could be that the reference to version # 2.0 in tclsqlite.c needs to be corrected to some other number! Thanks, -Shamil Nuno Lucas [EMAIL PROTECTED]To: .pt cc: Subject: Re: [sqlite] Trying to build tclsqlite.dll 05/28/2004 08:44 PM Please respond to nuno.lucas === On 2004-05-28, shamil_daghestani wrote === .. 3. Do the command: nmake -f ..\sqlite\Makefile.msvctcl. .. To do Step 3, In the Command Prompt I did: C:\ bcc32 nmake -f ...\sqlite\Makefile.msvctcl .. Shouldn't you type nmake -f ... instead of bcc32 nmake -f ... !? ^ Regards, ~Nuno Lucas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Trying to build tclsqlite.dll
I'm using XP and trying to build a Tcl biding of SQLite using Borland 5.5 Builder but have run against a few obstacles. This is the very first time I'm trying such a thing, so bare with me please. I downloaded sqlite-2.8.13.tar.gz and unzipped the files into the C:\SQLite folder. Then I followed these steps at: http://www.sqlite.org/cvstrac/wiki?p=BuildOnWindowsWithTcl 1. Download the two files, Makefile.msvctcl and make_helper.tcl. Install them in the SQLite directory (ie. same dir as configure and Makefile-linux.gcc). 2. Do some configuration at the top of Makefile.msvctcl - mainly to tell it where you installed TCL. 3. Do the command: nmake -f ..\sqlite\Makefile.msvctcl. in Step 2. I only changed the two lines to the following: TCL_INC = /Ic:\Tcl8.4\include TCL_LIB = c:\Tcl8.4\lib\tcl84.lib To do Step 3, In the Command Prompt I did: C:\ bcc32 nmake -f ..\sqlite\Makefile.msvctcl But it returns the following error: could not find file 'nmake.cpp' Any ideas on what I might be doing wrong would be appreciated. -Shamil D. The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Newbie --question about multiple PCs accessing sqlite
I'm contemplating using the TCL binding of sqlite, but the database is to be located in a shared folder and about 100 users will be accessing it for read/write purposes. I would like to take some time to learn as much as possible to safeguard the database from getting corrupt due to several users accessing it at the same time. However, the sqlite website "http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: “A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.“ I have 3 questions: 1.From the above statement, Am I right to assume that sqlite is not for me? 2.If sqlite is feasible for my purpose, where can I find good information to avoid ending up with a corrupt database 3.The TCL binding I downloaded says version 2.0 and I do not have a C++ compiler, where can I get the most recent binary of TCL sqlite? Thanks. The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of , or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] TCL and SQLITE
I'm just getting around to using SQLITE to see how it performs. Therefore,
I'm just a beginner.
I have a couple of questions about it: I downloaded the TCL binding and did
pkg_mkIndex to create the pkgIndex.tcl file. Then I did package require
sqlite, I got version 2.0. However, I see from the SQLITE website that
the latest version is 2.8.13. What's the reason for that? Is TCL binding
no longer supported by newer versions of SQLITE?
The second question is about speed. I created the most basic table and did
a loop to insert 1000 integers and that took 207 seconds... an awful lot of
time!! What did I do wrong, if any, in the example below.
% package require sqlite
2.0
% sqlite db c:/newDB
0x008752B8
% set start [clock seconds]
1082034600
% db eval {CREATE TABLE t1(a int)}
% for {set j 1} {$j = 1000} {incr j} {db eval INSERT INTO t1 VALUES($j)}
% puts total processing time = [expr [clock seconds] - $start]
total processing time = 207
%
thanks,
SD
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of, or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] TCL and SQLITE
Thanks Christian. I figured something just wasn't right... Total
processing time dropped from 207 to just 0.7 seconds
SD
Christian Smith
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
.comcc:
Subject: Re: [sqlite] TCL and SQLITE
04/15/2004 09:51
AM
On Thu, 15 Apr 2004 [EMAIL PROTECTED] wrote:
Try wrapping the inserts in transaction.
I'm afraid I don't understand...Could you show be in my example below how
I
can do that?
Something like:
% package require sqlite
% sqlite db c:/newDB
% set start [clock seconds]
% db eval {CREATE TABLE t1(a int)}
% db evel BEGIN TRANSACTION
% for {set j 1} {$j = 1000} {incr j} {db eval INSERT INTO t1 VALUES($j)}
% db evel COMMIT TRANSACTION
% puts total processing time = [expr [clock seconds] - $start] total
processing time = 207
%
SD
Christian Smith
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
.comcc:
[EMAIL PROTECTED]
Subject: Re: [sqlite] TCL
and SQLITE
04/15/2004 09:30
AM
On Thu, 15 Apr 2004 [EMAIL PROTECTED] wrote:
I'm just getting around to using SQLITE to see how it performs.
Therefore,
I'm just a beginner.
I have a couple of questions about it: I downloaded the TCL binding and
did
pkg_mkIndex to create the pkgIndex.tcl file. Then I did package require
sqlite, I got version 2.0. However, I see from the SQLITE website
that
the latest version is 2.8.13. What's the reason for that? Is TCL
binding
no longer supported by newer versions of SQLITE?
Should be. Maybe the 2.0 is the minimum supported version
The second question is about speed. I created the most basic table and
did
a loop to insert 1000 integers and that took 207 seconds... an awful lot
of
time!! What did I do wrong, if any, in the example below.
Try wrapping the inserts in transaction.
Without that, each insert requires fsyncs to flush data to disk and
purging of page cache as cache data is not reused from one transaction to
the next. All this slows inserts down as their is a lot of IO going on.
In a single transaction, there is only one lot of fsyncs and the cache is
maintained throughout.
% package require sqlite
2.0
% sqlite db c:/newDB
0x008752B8
% set start [clock seconds]
1082034600
% db eval {CREATE TABLE t1(a int)}
% for {set j 1} {$j = 1000} {incr j} {db eval INSERT INTO t1
VALUES($j)}
% puts total processing time = [expr [clock seconds] - $start]
total processing time = 207
%
thanks,
SD
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of, or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
/\\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review,
