[sqlite] import failing

[Hoover, Jeffrey]

I am trying to import a tab delimited file into a table containing 21 columns.

It fails on this record
Sapurv10037426m 176 RPS-BLAST(BLASTP)   cdd gnl|CDD|239293  
72  159 1   89  33.756.5174 70.792  
cd02995, PDI_a_PDI_a'_C, PDIa family, C-terminal TRX domain (a') subfamily; 
composed of the C-terminal redox active a' domains of PDI, ERp72, ERp57 (or 
ERp60) and EFP1. PDI, ERp72 and ERp57 are endoplasmic reticulum (ER)-resident 
eukaryotic proteins involved in oxidative protein folding. They are oxidases, 
catalyzing the formation of disulfide bonds of newly synthesized polypeptides 
in the ER. They also exhibit reductase activity in acting as isomerases to 
correct any non-native disulfide bonds, as well as chaperone activity to 
prevent protein aggregation and facilitate the folding of newly synthesized 
proteins. PDI and ERp57 have the abb'a' domain structure (where a and a' are 
redox active TRX domains while b and b' are redox inactive TRX-like domains). 
PDI also contains an acidic region (c domain) after the a' domain that is 
absent 
 in ERp57. ERp72 has an additional a domain at the N-terminus (a"aabb'a' domain 
structure). ERp57 interacts with the lectin chaperones, calnexin and 
calreticulin, and specifically promotes the oxidative folding of glycoproteins, 
while PDI shows a wider substrate specificity. ERp72 associates with several ER 
chaperones and folding factors to form complexes in the ER that bind nascent 
proteins. EFP1 is a binding partner protein of thyroid oxidase, which is 
responsible for the generation of hydrogen peroxide, a crucial substrate of 
thyroperoxidase, which functions to iodinate thyroglobulin and synthesize 
thyroid hormones. 0   null104 1e-16


With this message
expected 21 columns of data but found 16

If I change the substring a"aabb'a' to a''aabb'a' (two consecutive 's instead 
of ") the record loads.


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

Re: [sqlite] how to represent a tree in SQL

[Hoover, Jeffrey]

Isn't most of the complexity in the software?

Representing a tree is fairly simple, it just requires a foreign key in
the table referencing the primary key of the same table...

Create table tree_node (
node_id integer primary key,
distance_from_root integer not null,-- 0 implies root node
parent_node integer,-- FK to
node_id, column should be index
...data columns...
);

Create view tree as
   Select * from tree_node where distance_from_root=0;

All kinds of denormalizations and elaborations possible from here...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
Sent: Wednesday, October 14, 2009 9:41 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to represent a tree in SQL

Dear there,

I am developing a numerical application, where a single rooted dynamic
tree
is the main data structure. I intended to use SQLite for this purpose
and
also for other data as well. However I have no reference how to
represent a
tree using tables. I need these functionalities:
(1) basic tree: single root, multi-levels, arbitrary number of branches,
index each node, index each path (from root to a leaf), lookup parent,
lookup descendants
(2) dynamics: delete a path, add a path; maintain parent and descendants
table; maintain history of tree; lookup history
(3) each node has lots of  matrix and vectors, which will be updated
with
dynamics, and should be tracked

As you see, it is nontrivial to write a tree structure to support all
these
functions, while keep the code clean and neat. That's why I want to use
SQLite to keep things straight. Is there a good reference on this?

Michael Chen
___
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] cannot add data to sql tables

[Hoover, Jeffrey]

Could you be omitting the database commit?
Try adding an explicit commit after your inserts.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Tuesday, September 22, 2009 5:59 PM
To: sqlite-users
Subject: [sqlite] cannot add data to sql tables

Hi, 
I successfully did the following using C wrappers: 
1) created a .db file 
2) Added the tables 
3) Try to insert data into the tables created. 


When I try to 3) insert data into the tables, all the routines pass
(Prepare, Step, Finalize) without errors. 


But when I run my .db file through sqlite3 shell and select * from
EntityTbl(which is the name of 
my table), nothing shows. 


Any ideas where my data went ? 
Kavita 
___
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] MySQL makes me wish for SQLite

[Hoover, Jeffrey]

I saw something about an ODBC module for SQLite.
Could you workaround the PHP-SQLite issues via ODBC?

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fred Williams
Sent: Friday, September 11, 2009 4:24 PM
To: General Discussion of SQLite Database
Subject: [sqlite] MySQL makes me wish for SQLite

Just began coming back to speed on MySQL after many years. (Thanks to
SQLite)  Boy does it SUCK!

I've already eaten up the better part of 150MB of disk space, run
installation repair once, (of many?) and still trying to find the
"admin" logon and password.  Damn!  It has been too long!

Please, is someone working on a good straight forward .PHP SQLite
interface?  I might even pay for it!

Need a "drop in" SQLite implementation for a website database.  I'll
endure MySQL for the meantime.  Like I use to prostitute myself with MS
Server.  BUT, I'm already ready to JUMP!

Sorry, I guess you might have noticed my frustration is showing a bit...

Fred
___
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] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

(apologies, I had a typo in my code sample, here is a correction)
if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "insert into
foo(col1,col2)
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
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] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "foo('col1', col2')
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
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] Repost: Accessing a DB while copying it causes Windowsto eat virtual memory

[Hoover, Jeffrey]

Why not just make the file read-only before copying it, then restoring 
write-access when the copy finishes?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Stan Bielski
Sent: Tuesday, July 07, 2009 6:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repost: Accessing a DB while copying it causes Windowsto 
eat virtual memory

On Tue, Jul 7, 2009 at 1:10 PM, Mark Spiegel wrote:

> This should give you a picture of who is opening the file and with what
> flags.  In particular, how is your SQLite app and Explorer opening the file?

Thanks for the pointer to Filemon; I'm always happy to learn about a
new tool. Apparently its functionality has been integrated into
Procmon, so that's what I used to follow your debugging steps.

To address some of the points you mention earlier:

* I'm running as Administrator, and I can see SYSTEM-owned processes
in Task Manager. Not sure if that means I can see all processes or
not, but I suspect that I can.

* I was actually wrong about VM going up. In fact, the Pagefile
doesn't seem to grow much. Physical Memory (as reported by Resource
Monitor) is what's growing. Once it peaks (at around 99%) the page
fault rate goes through the roof.

* When this situation occurs, the system is most definitely not
CPU-bound, as the CPU usage hovers around 1%. The high page fault rate
is what seems to be making other applications unresponsive.

* Working Set Size and Peak Working Set size of all processes in Task
Manager are very modest; the memory appears to be going to some sort
of caching mechanism in the OS.

Using procmon, I filtered on "Path" for my particular sqlite file and
repeated my experiments from the original post. I verified that
sqlite3.exe and Explorer.EXE are the only processes opening the
file.The thing that stands out in the pathological cases is the
presence of SHARING VIOLATION result code. Whichever one of sqlite or
Explorer attempts to open the file second will throw this error, and
that's when I see physical memory begin to grow.

Here's a trace of sqlite accessing the file, then Explorer copying it
(CSV format):

"Time of Day","Process Name","PID","Operation","Path","Result","Detail"
"6:01:46.1439881
PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Read Attributes, Disposition: Open, Options: Open For Backup,
Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
AllocationSize: n/a, Impersonating: TESTBED9\Administrator,
OpenResult: Opened"
"6:01:46.1446133
PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Read Attributes, Disposition: Open, Options: Open For Backup,
Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
AllocationSize: n/a, Impersonating: TESTBED9\Administrator,
OpenResult: Opened"
"6:01:46.1448474
PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Generic Read/Write, Disposition: OpenIf, Options: Synchronous
IO Non-Alert, Non-Directory File, Random Access, Attributes: N,
ShareMode: Read, Write, AllocationSize: 0, Impersonating:
TESTBED9\Administrator, OpenResult: Opened"
"6:01:56.1737399
PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SHARING
VIOLATION","Desired Access: Generic Read, Write Data/Add File,
Disposition: Open, Options: Sequential Access, Synchronous IO
Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read,
AllocationSize: n/a"
"6:01:56.1738486
PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Generic Read, Write Data/Add File, Disposition: Open, Options:
Sequential Access, Synchronous IO Non-Alert, Non-Directory File,
Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a,
OpenResult: Opened"

And Explorer copying the file then sqlite3 opening it:

"6:30:23.1071085
PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Generic Read, Write Data/Add File, Disposition: Open, Options:
Sequential Access, Synchronous IO Non-Alert, Non-Directory File,
Attributes: n/a, ShareMode: Read, AllocationSize: n/a, OpenResult:
Opened"
"6:30:41.0615955
PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Read Attributes, Disposition: Open, Options: Open For Backup,
Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
AllocationSize: n/a, Impersonating: TESTBED9\Administrator,
OpenResult: Opened"
"6:30:41.2108393
PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired
Access: Read Attributes, Disposition: Open, Options: Open For Backup,
Open Reparse Point, Attributes: n/a, 

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows toeat virtual memory

[Hoover, Jeffrey]

Maybe if you write-protected the file before starting the copy...

My guess is that the process that connects to do the query opens the
file in read/write mode causing the operating system to think someone is
trying to write to the file.  So the OS tries to keep a copy of the data
being read by the COPY command around till the COPY is finished.

If the file is write-protected I believe the db connection will open the
file in read-only mode so the OS will probably recognized that it does
not need to retain a separate copy for COPY.

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stan Bielski
Sent: Tuesday, July 07, 2009 11:50 AM
To: sqlite-users
Subject: [sqlite] Repost: Accessing a DB while copying it causes Windows
toeat virtual memory

Sorry for the repost, but the original thread was hijacked by another
list user. This is a serious problem IMHO; it looks like the DB can't
be backed-up without rendering the machine unusable if a query hits it
while a copy is in progress.

Hello,

In the course of copying a largish (20 GB) database file while
accessing it via sqlite3, the machine became very unresponsive. I
opened task manager and found that the system was using a huge amount
of virtual memory, causing it to thrash. Per-process memory usage
looked normal and did not add up to anywhere near system-wide VM
usage.

I ran into this issue at a customer site and was able to reproduce it
using a local Windows 2008 installation. I have not installed any
backup software or a virus scanner. Storage is local disk, SQLite
version is 3.3.17.

At first I thought that this was a general Windows problem involving a
process accessing a file that is being copied, but other binaries I
tested do not cause the same behavior that sqlite3 does. I performed
the following experiments to try to diagnose the issue.

Case 1:

* I copy a 20 GB sqlite DB using Windows' own copy utility (e.g. via
explorer).
* At any point during the copy, I open the file being copied in sqlite3
* I exit sqlite3.
* During the rest of the copy the OS will consume virtual memory
linear (seemingly identical) to the amount of data copied since the
process opened the file.

I repeated this experiment using a similarly-sized file created from
/dev/zero (i.e. an invalid DB) and the results were the same.

Case 2:

* I copy the sqlite DB using Windows' own copy utility (e.g. via
explorer).
* At any point during the copy, I run 'strings' with the file as an
argument.
* I exit strings.
* The copy does not result in the OS consuming additional virtual
memory.

Case 3:

* I open the DB in sqlite3
* I let sqlite3 idle and do not input any commands.
* I copy a sqlite DB using Windows' own copy utility (e.g. via
explorer).
* I continue to let sqlite3 idle and do not input any commands.
* During the rest of the copy the OS will consume virtual memory
linear (seemingly identical) to the amount of data copied since the
process opened the file.

Is there a workaround for this issue? Any assistance or info is
appreciated.

Thanks,
-Stan
___
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] Database inserts gradually slowing down

[Hoover, Jeffrey]

I vacuum frequently, particular after large updates or deletes.

Two other potential optimizations of inserts (you may already be doing
this):
- use bulk insert
- encapsulate the inserts within a transaction

One other note, if you have a primary key whose value is continually
increasing your pk index can become imbalanced and therefore
inefficient.  You may be able to get around this by using INTEGER
PRIMARY KEY (or INTEGER PRIMARY KEY AUTOINCREMENT).  As a side benefit
this kind of column queries very efficiently since the column value is
row's address (the ROWID).

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wes Freeman
Sent: Tuesday, June 16, 2009 4:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

VACUUM cleaned up the file in my current test, after 1200 iterations,
making it run at ~4.6seconds again, rather than ~5.1. It seemed to get
it almost back to the performance of a clean file.

Didn't know about the vacuum command--Cool. By the way, the vacuum
operation takes ~1.6 seconds for my test database. Seems like a
reasonable thing to run before a backup or something, if your
application would be prone to this sort of fragmentation.

Wes

On Tue, Jun 16, 2009 at 1:44 PM, Hoover, Jeffrey<jhoo...@jcvi.org>
wrote:
> Wouldn't a period VACUUMing of the database alleviate fragmentation?
>
> - Jeff
>
___
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] Database inserts gradually slowing down

[Hoover, Jeffrey]

Wouldn't a period VACUUMing of the database alleviate fragmentation?

- Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Konrad J Hambrick
Sent: Tuesday, June 16, 2009 1:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down


Jens --

I have seen Index and Record Fragmentation cause
the kind of slowdowns you are describing.

Especially when there are a lot deletes followed
by a 'largish' (100,000-record) batch of inserts.

I have found on occasion that the speed is more
consistent if I can find a way to drop and create
a 'fresh' table from time to time.

The fresh table eliminates the fragmentation.

Sounds like that won't work without a lot of effort
since you have records remaining in the table.

-- kjh


On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
> 
> Dropping the table is not an option since I always will have some data in the 
> table (even though I delete everything in my test). Maybe the culprit is that 
> I delete everything from the table?
> 
> JP
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
> Sent: 16. júní 2009 15:51
> To: kon...@payplus.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
> 
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick wrote:
>> Hmmm ...
>>
>> Is there a way to replace step 5 (delete everything from the table)
>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>
> 
> This shouldn't be necessary unless the table has triggers.  From
> http://sqlite.org/lang_delete.html :
> 
> "When the WHERE is omitted from a DELETE statement and the table being
> deleted has no triggers, SQLite uses an optimization to erase the
> entire table content without having to visit each row of the table
> individual. This "truncate" optimization makes the delete run much
> faster."
> 
___
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] What am I doing wrong ?

[Hoover, Jeffrey]

is autocommit on?
try adding a manual commit.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olivier FAURAX
Sent: Thursday, March 19, 2009 10:56 AM
To: General Discussion of SQLite Database
Subject: [sqlite] What am I doing wrong ?

Hello,

I'm beginner and uses sqlite3.
I'm trying to record informations in a database, but this fails.
I have followed http://www.sqlite.org/c3ref/stmt.html

rc = sqlite3_open(DB_FILENAME, _db);
rc = sqlite3_exec(metadata_db,
  "CREATE TABLE IF NOT EXISTS metadata "
  "( filename TEXT, key INT, data BLOB, length INT);",
  NULL, NULL,
  );
rc = sqlite3_prepare_v2(metadata_db,
   "INSERT INTO metadata VALUES "
   "( ?1, ?2, ?3, ?4 );",
   0, , NULL);
rc = sqlite3_bind_text(sthl, 1, filename, 12, NULL);
rc = sqlite3_bind_int(sthl, 2, key);
rc = sqlite3_bind_blob(sthl, 3, data, data_length, NULL);
rc = sqlite3_bind_int(sthl, 4, data_length);
rc = sqlite3_step(sthl);
rc = sqlite3_finalize(sthl);
rc = sqlite3_close(metadata_db);

I checked all "rc", they are all 0, except sqlite3_step returns 101 (aka
SQLITE_DONE) which is correct according to doc.

The problem is that I obtain a correct database (with 4 fields), but
nothing in it. If I retry this code with an existing database, I still
have the table, but nothign in it.

The only working case is when I add a dummy sqlite3_exec before the
close : this way, the statement is executed (I see data in the db), but
the dummy sqlite3_exec isn't executed (it's a simple INSERT).
This suggests that there is a kind of "flushing" that must be done, and
that it has been done to exec the new sqlite3_exec.

Please let me know what I did wrong.

Have a nice day
-- 
Olivier FAURAX
___
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] Newbie question

[Hoover, Jeffrey]

In the first query the (select id from tbl2 where name='Joe') is referred to as 
a SUBQUERY.

The second query is uses a JOIN because you are joining together data from 
different rows.

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dermot
Sent: Wednesday, March 18, 2009 12:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Newbie question

2009/3/18 Hoover, Jeffrey <jhoo...@jcvi.org>:
>
> Assumming  this is only one row in tbl2 where name='Joe'...
>
> this should work:
> SELECT * FROM tbl1
> WHERE description='someval'
> AND foreign_key_id=(select id from tbl2 where name='Joe');
>
> this is better:
> select tbl1.* from tbl1, tbl2
> where tbl1.description='someval'
> AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id;
>
> if there may be many rows in tbl2 where name =- 'Joe' then
> SELECT * FROM tbl1
> WHERE description='someval'
> AND foreign_key_id in (select id from tbl2 where name='Joe');

Both of the top 2 worked. Thank you. I'll try and stick to the less
ambiguous form and bear in mind all the comments about single quotes.

A bit more information though. Is there a term for that type of SELECT
statement?

Thanx,
Dp.
___
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] Newbie question

[Hoover, Jeffrey]

I think its because sqlite (and most rdbms's) expect literal strings to
be enclose in ' not ".

I think sybase is an except, accepting either.
In SQLite you use " to enclose table/column names that contain
non0standard characters or where object id is case sensitive, such as

select "grant#" from "Current_Projects";

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hardy, Andrew
Sent: Wednesday, March 18, 2009 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Newbie question


I'm relatively newbie too, but just curious, is it anything to do with
(select id from tbl2 where name="Joe") not being guarenteed to return a
scaler?

Ie only a single value?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot
Sent: 18 March 2009 15:47
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Newbie question

2009/3/18 Igor Tandetnik :
> Dermot  wrote:
>> I want to do a select query a bit like this:
>>
>> SELECT * FROM tbl1 WHERE description="someval" AND 
>> foreign_key_id=(select id from tbl2 where name="Joe");
>>
>> This gives me a syntax error and my other efforts are not yielding 
>> results.
>
> What's the text of the error message?




SELECT * FROM tbl1 WHERE description="someval" AND
foreign_key_id=(select id from tbl2 where name="Joe");

This attempt give the error:

SQL error: near "=": syntax error

SELECT * FROM tbl1 WHERE description="someval" AND
foreign_key_id='select id from tbl2 where name="Joe"';

This return nothing, although it should. If I break the queries into 2
separate select statements they verify.

This is SQLite 3.5.1.

Thanx,
Dp.
___
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] Newbie question

[Hoover, Jeffrey]

Assumming  this is only one row in tbl2 where name='Joe'...

this should work:
SELECT * FROM tbl1
WHERE description='someval'
AND foreign_key_id=(select id from tbl2 where name='Joe');

this is better:
select tbl1.* from tbl1, tbl2
where tbl1.description='someval'
AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id;

if there may be many rows in tbl2 where name =- 'Joe' then
SELECT * FROM tbl1
WHERE description='someval'
AND foreign_key_id in (select id from tbl2 where name='Joe');

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot
Sent: Wednesday, March 18, 2009 11:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Newbie question

2009/3/18 Igor Tandetnik :
> Dermot  wrote:
>> I want to do a select query a bit like this:
>>
>> SELECT * FROM tbl1 WHERE description="someval" AND
>> foreign_key_id=(select id from tbl2 where name="Joe");
>>
>> This gives me a syntax error and my other efforts are not yielding
>> results.
>
> What's the text of the error message?




SELECT * FROM tbl1 WHERE description="someval" AND
foreign_key_id=(select id from tbl2 where name="Joe");

This attempt give the error:

SQL error: near "=": syntax error

SELECT * FROM tbl1 WHERE description="someval" AND
foreign_key_id='select id from tbl2 where name="Joe"';

This return nothing, although it should. If I break the queries into 2
separate select statements they verify.

This is SQLite 3.5.1.

Thanx,
Dp.
___
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 result of subquery both as value and conditional test...

[Hoover, Jeffrey]

use the coalesce function

coalesce(xxx,'A') returns 'A' if xxx is null, otherwise returns the
value of xxx (but remember that '' is NOT null in SQLite).

coalesce((select y_value from y where y_id = x_id),'darn')
replaces your case statement

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
Sent: Tuesday, February 24, 2009 3:01 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using result of subquery both as value and
conditional test...

On 25/02/2009 6:15 AM, John Elrick wrote:
> I may be overlooking something obvious, however, I cannot discern from

> the documentation if this is possible.
> 
> given a simple example:
> 
> create table x (x_id integer);
> create table y (y_id integer, y_value varchar);
> 
> insert into x values (1);
> insert into x values (2);
> insert into y values (1, 'Hello world');
> 
> select case when
>   (select y_value from y where y_id = x_id)
> is null then
>   'darn'
> else
>   (select y_value from y where y_id = x_id)
> end
> from x
> 
> 
> Is there any way to eliminate the second (select y_value from y where 
> y_id = x_id)?   If so, what would the query look like?
> 

The bog-standard should-work-everywhere SQL way is to use the COALESCE 
function; see http://en.wikipedia.org/wiki/Null_(SQL)

Sqlite has COALESCE and also IFNULL; see 
http://www.sqlite.org/lang_corefunc.html

sqlite> create table foo (id integer, data text);
sqlite> insert into foo values(5, 'bar');
sqlite> insert into foo values(6, null);
sqlite> select * from foo;
5|bar
6|
sqlite> select coalesce((select data from foo where id = 6), 'darn');
darn
sqlite> select coalesce((select data from foo where id = 5), 'darn');
bar
sqlite>

HTH,
John
___
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] Little SQL help please..

[Hoover, Jeffrey]

Use the min function (since they are all have the same value)

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of johnny depp
(really!)
Sent: Tuesday, February 17, 2009 3:44 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Little SQL help please..


Hello,

I'm investigating if i should uses sqlite from within java?
I need an embedded database, no fancy user management or anything... I'm
only interested in speed.
Sqlite looks good for the job..So I went out and found the someone has
written a java sqlite wrapper.
Great! 
But I ran into a problem:

I need to group some rows, and if the values for a column are all the
same I
want to return that value,
else I want to return "not the same".. like so:

For this table:

col1, col2
A  "1"
A  "2"
B  "1"
B  "1"

And the query: select . group by col1; // can't write the query
because
I don't know it..

I would like the result to be:
col1 col2
A "not the same"
B "1"

I think the query should be this:

SELECT col1, 
   CASE count(distinct col2) > 1 THEN "not the same"
ELSE  first(col2)
END 
   AS col2
FROM table GROUP BY col1

The problem is the aggregation function first does not exist,
this would not be a problem in c or c++ because I would plug it in the
system (I read),
the problem is I'm calling sqlite from java and so I can't plug in my
own
functions..
Or I will have to look into jni and i don't want to do that..seem scary
stuff..

So my questions is, is there another way to do it without using the
(none
existing) function first?


Kind Regards,
Nick.






























-- 
View this message in context:
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22052925.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Date datatype

[Hoover, Jeffrey]

It depends on the purpose for which you are using SQLite.  It sure makes
it ugly trying to convert an existing application TO SQLite.
Oracle-like TO_DATE and TO_CHAR functions would be a big help.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Wednesday, January 21, 2009 10:57 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date datatype

Hoover, Jeffrey <jhoo...@jcvi.org> wrote:
> You are comparing the values as strings.
>
> Instead, format your dates as -MM-DD and use the date function to
> convert strings to dates for comparison:

If you use -MM-DD format consistently, then simple string comparison

just happens to give the same results as date comparison.

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] Date datatype

[Hoover, Jeffrey]

that's what date masks are for..

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Wednesday, January 21, 2009 10:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date datatype

Hoover, Jeffrey <jhoo...@jcvi.org> wrote:
> Seems the date function could use a lot of work.
>
> Be nice if it understood some other formats, too, such as 02-JAN-09

Is that January 2nd, 2009 or January 9th, 2002?

> or 11/17/2004...

If it were 11/12/2004 instead, would it be December 11th or November 
12th?

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] Date datatype

[Hoover, Jeffrey]

Sorry about my previous post.  I was wrong.  It appears that the DATE
function is exceptionally rigid.

You MUST use 2-digit months and 2-digits days, using a leading zero for
values < 10.  Probably need a 4-digit year, too.

Seems the date function could use a lot of work.

Be nice if it understood some other formats, too, such as 02-JAN-09 or
11/17/2004...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey
Sent: Wednesday, January 21, 2009 10:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date datatype

You are comparing the values as strings.

Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:

 

  select date from envelope where date > date('2009-01-20') limit 3;

 

here are some examples:

 

  sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');

  2009-07-01

 

  sqlite> select date('2009-07-01') where
date('2009-07-01')<date('2009-06-01');

  (no row returned)

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype

 

It seems i'm having an hard time with dates in sqlite:

 

sqlite> .schema

CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT 

NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, 

`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, 

`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, 

`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` 

int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, 

`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);

 

sqlite> select date from envelope where date > '2009/01/20' limit 3;

2009/1/7

2009/1/7

2009/1/7

 

why?

 

-- 

 

bye,

P.

 

___

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] Date datatype

[Hoover, Jeffrey]

You are comparing the values as strings.

Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:

 

  select date from envelope where date > date('2009-01-20') limit 3;

 

here are some examples:

 

  sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');

  2009-07-01

 

  sqlite> select date('2009-07-01') where
date('2009-07-01') .schema

CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT 

NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, 

`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, 

`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, 

`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` 

int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, 

`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);

 

sqlite> select date from envelope where date > '2009/01/20' limit 3;

2009/1/7

2009/1/7

2009/1/7

 

why?

 

-- 

 

bye,

P.

 

___

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] 'UPDATE shop_orders SET comments=comments||? WHERE oid=?', ('test', '1')

[Hoover, Jeffrey]

if comments is null then comments||? is also null, try this:

comments=coalesce(comments,'')||?



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Cuykens
Sent: Sunday, January 18, 2009 10:15 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] 'UPDATE shop_orders SET comments=comments||? WHERE
oid=?',('test', '1')

How do i do the following ?

comments=comments||?

When I add a comment nothing happens ?

(please reply to my email address no subscription)
___
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] need a CURRENT_USER() function

[Hoover, Jeffrey]

Can anyone tell me set-by-step how to add a CURRENT_USER() function to
SQLLITE that will return the current linux login?

 

I'm not a C programmer and I have JUST picked up SQLite.

I am trying to convert a Sybase schema to SQLite.  The schema has
triggers triggers but so far they convert in a pretty straightforward
manner...that is, util I ran into a reference to "current_user" in
Sybase...

 

Please help.

 

Jeff.

 

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