[sqlite] How do I unsubscribe?

[Anderson, James H \(IT\)]

Thanks.


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Version 3.3.14

[Anderson, James H \(IT\)]

Something that would be very useful in the shell code in a future
release would be the ability to handle data containing embedded binary
data. I use the shell to populate tables and many of the files I need to
load have the odd \n or \000 embedded in what whould be ascii text.
Sybase handles this situation but for sqlite I need to filter the data
first. Obviously not a priority but nevertheless something nice to get
fixed eventually...

jim

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 02, 2007 11:47 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Version 3.3.14

SQLite version 3.3.14 is now available on the SQLite website

   http://www.sqlite.org/

Version 3.3.14 focuses on performance improvements.  There 
have been several changes to the back-end layers (the pager
and the b-tree subsystems) that reduce the amount of disk
I/O.  A new optimization has been added to the INSERT command
which, as a side effect, makes VACUUM work significantly faster
for large databases and helps VACUUM to greatly reduce the
amount of fragmentation in the database.  We have also added 
the concept of "exclusive access mode".  In exclusive access 
mode, SQLite holds onto locks until you close the connection.
This allows for additional I/O reductions and corresponding
performance improvements, at the expense of concurrency.  The
core SQLite sources are now also available as a single huge 
file of C code (which we call "the amalgamation") rather than 
as a collection of smaller files.  

When all the latest changes are used and the code is
recompiled using the amalgamation with -O3 under gcc 4.1.0,
we are seeing performance improvements on Linux of about 35%
over version 3.3.13.  We are very interesting in hearing 
about performance changes on other compilers and with other 
operating systems.

Version 3.3.14 incorporates many changes over version 3.3.13.
These changes have been well tested on Linux, but as the
no so much on windows and other systems.  If you find problems,
please report them either on this list or at

  http://www.sqlite.org/cvstrac/tktnew

--
D. Richard Hipp  <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] stupid man's manual to sqlite?

[Anderson, James H \(IT\)]

Removing the index is one of the silliest suggestions I've ever
encountered. A book without an index is almost useless, unless of course
you're blessed with a photographic memory. As far as putting in on a CD
or on the web, that's fine as long as a _good_ index is also included in
the book. I often read books on the subway, train, in a car, etc., where
there is no possibility of access to a CD or the web. Many people I know
have similar reading habits and consequently have no use for this sort
of thing.

-Original Message-
From: A.J.Millan [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 10, 2007 6:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?


>I will get back to you about the index. I also would like to invite
>anyone else who has any specific things they would like to see changed
>in the index to tell me. Assuming I can get a revised index out
>electronically, I will make any such changes immediately to the file I
>have on-hand.

>-- Mike

I have continued thinking of the index matter, and I consider that the
possibility to take out it completely in the printed version, or leaving
it
summarized to a minimum, would have the additional advantage to be able
to
dedicate more pages to the truly useful things (in the past, I also
published some thing, so I have some idea about the editorial world.)

On the other hand, besides the mentioned possibility, to put on-line, or
in
a CD, the index, I believe that equally, an artifice could be included
that
allow "Google" any word or combination of them (in the case of a CD, the
original content could be ciphered to avoid the piracy).  As in Google,
besides the page and line-number, the result it could include certain
quantity of characters around all the matchs-points. For example, say 30
before and 30 later, to give an idea of the context.  Beside the utility
for
the book owners, the potential buyers could check what can wait.  It
could
be a good "hook " to attract possible clients.

As I stated before, the same thing for the idea.  Please, don't patent
it
(yes I have some paranoia about patents).  Following the Hipp's blessing
,
and inside my much smaller possibilities, I would like that these ideas
remains in the public domain.

Greetings

A. J. Millan
ZATOR Systems.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there an inverse for .import?

[Anderson, James H \(IT\)]

Not an option for what I'm doing. 

-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 09, 2007 5:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there an inverse for .import?

On Fri, 9 Mar 2007, Dennis Cote wrote:

> You can get csv output from sqlite using the .mode command to specify
the
> output format and the .output command to set the output file.
>
>   .mode csv
>   .headers on
>   .output mytable.csv
>   select * from mytable;
>   .output stdout

   Or, use the SQL format for both output and input.

   At the sqlite3> prompt, type .h(elp) and see all the choices.

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental
Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there an inverse for .import?

[Anderson, James H \(IT\)]

Yes, unlike .dump, that works. Thanks very much.

jim 

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 09, 2007 5:05 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there an inverse for .import?

Anderson, James H (IT) wrote:
> I need to "export" a table to a file in the same format as used by
> .import, but I don't see any such cmd. Am I missing something, or does
> such a cmd just not exist?
>
> jim
>
>   
You can get csv output from sqlite using the .mode command to specify 
the output format and the .output command to set the output file.

.mode csv
.headers on
.output mytable.csv
select * from mytable;
.output stdout

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there an inverse for .import?

[Anderson, James H \(IT\)]

I'm going to go with the approach indicated, below. Using .dump didn't
work in the sense that .dump does indeed dumpt *only* in "insert into"
format.

Thanks, Clark.

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 09, 2007 12:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there an inverse for .import?

>From the SQLite shell, you can send the output to a file using

.output myfile.txt

So

.output myfile.txt
select * from mytable;
.output stdout

will get you a pipe-delimited myfile.txt.  You can change the delimiter
using the .separator command, or you can use .mode to use a predefined
format.

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, March 9, 2007 9:04:29 AM
Subject: [sqlite] Is there an inverse for .import?

I need to "export" a table to a file in the same format as used by
.import, but I don't see any such cmd. Am I missing something, or does
such a cmd just not exist?

jim





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there an inverse for .import?

[Anderson, James H \(IT\)]

Thanks, I'll try that. I misunderstood the description of .dump to mean
that it dumped in SQL insert format. 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Friday, March 09, 2007 12:12 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there an inverse for .import?

On 3/9/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> I need to "export" a table to a file in the same format as used by
> .import, but I don't see any such cmd. Am I missing something, or does
> such a cmd just not exist?


.dump


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is there an inverse for .import?

[Anderson, James H \(IT\)]

I need to "export" a table to a file in the same format as used by
.import, but I don't see any such cmd. Am I missing something, or does
such a cmd just not exist?

jim

RE: [sqlite] stupid man's manual to sqlite?

[Anderson, James H \(IT\)]

FWIW, excepting the index which is truly terrible, this is one of the
better technical books I've encountered. It has certainly been
invaluable for me.

jim

-Original Message-
From: Mike Owens [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 08, 2007 9:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?

> More of a half is about SQL generalities, an a quarter a copy-paste of
the
> on-line manual without more comment.  At the end, the horrific index
i've
> ever seen.

For the record, Apress had the index generated by a third party. I've
used the index myself recently, and frankly it worked for me -- I
found what I was looking for.

The SQL and C API references to which you refer --- taken from the
on-line documentation --- start on page 365. Furthermore, the
inclusion of this material was and has been considered useful by
everyone who reviewed the book, as it serves as an authoritative and
succinct reference. Many technical books include a reference similar
to this as a convenience to the reader.

I devoted 100 pages on the subject of SQL. I tried to systematically
and progressively describe SQL so that someone completely unfamiliar
could read the chapter in order, and by the end of the chapter
understand topics such as 3NF, left outer joins, and the intricacies
of NULL.

For an average programmer who needs to use SQLite, I hope this book
can save a lot of time whether you are a unfamiliar to or experienced
with SQL or the SQLite API. I wrote this book purely because I like
SQLite, wanted to help get the word out, and someone presented me with
the opportunity to write a book. Once I decided to write it, I went to
great lengths to write a book that would be genuinely useful. Apress
has put a lot of time and effort into making this a useful book as
well.

As I said in the book, and I think on this list, I am always open to
suggestions and feedback. If you really feel the book has failed you
in some way, or missed an important topic, I would like to know about
it so I can try to make it better should it ever make it to a second
edition. While I have many other things going on in life just like
everybody else, it is important to me that the book serves its
purpose, and I am more than happy to take whatever efforts required to
do so.

-- Mike

On 3/8/07, A.J.Millan <[EMAIL PROTECTED]> wrote:
>
>
> > Thank you i will try my possibilities buying from amazon, while i am
based
> in
> > the Czech republic.
> > Unfortunately it is little bit expensive for me, to buy it without
looking
> > into it before.
> > Jakub
>
> I would recommend not to waste your's money in that book.
>
> More of a half is about SQL generalities, an a quarter a copy-paste of
the
> on-line manual without more comment.  At the end, the horrific index
i've
> ever seen.
>
> A.J.Millan
> ZATOR Systems.
>
>
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] stupid man's manual to sqlite?

[Anderson, James H \(IT\)]

You won't be disappointed , it's a terrific book!

-Original Message-
From: Jakub Ladman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 08, 2007 9:51 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?

Thank you i will try my possibilities buying from amazon, while i am based in 
the Czech republic.
Unfortunately it is little bit expensive for me, to buy it without looking 
into it before.
Jakub


Dne čtvrtek 08 březen 2007 15:25 Samuel R. Neff napsal(a):
> I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
> started working with SQLite and are very impressed by the book.  It's a
> very easy read, well written, and covers a ton of details of internals on
> SQLite. There are several chapters with examples on using the C API (as
> well as other language extensions).
>
> http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr_1
>/ 002-0953141-3838416
>
> HTH,
>
> Sam
>
>
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building
> products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
> -Original Message-
> From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 08, 2007 7:26 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] stupid man's manual to sqlite?
>
> Hi (much smarter) friends.
> I need to start with sqlite (interfaced via C language) very quickly,
> unfortunately.
> I have not any experiences with any SQL, only some books.
> I am searching for some "stupid man's manual to sqlite".
> I can not found any basic's in documentation section of sqlite.org
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] bug in the cmd shell

[Anderson, James H \(IT\)]

The .import cmd has a problem when it encounters binary zeros embedded
in data.

I have a large tab-separated text file which has some garbage (aka
binary zeros) in one of the fields of one of the records. The .import
cmd trips on this and terminates with this msg:

/u/crdceed/data/RTM_NY_ceed_positionsPB.dat line 951: expected 14
columns of data but found 19

I should think this would be considered a bug.

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Re: Any way to know the numbers of rows affected by a cmd?

[Anderson, James H \(IT\)]

Exactly what I was hoping for! Thanks very much. 

-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 6:39 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: Any way to know the numbers of rows affected
by a cmd?

Regarding:

Anderson, James H (IT) wrote:
> I guess I forgot to mention the context. I'm interested in doing this 
> from the cmd shell.
>
>   
James,

You can use the command
  
 pragma count_changes=1

in the shell to have it report the number of rows affected by insert,
update, or delete statements (see
http://www.sqlite.org/pragma.html#modify for details).
==
==

But if you're using the command-line demonstration program, I don't
think the pragma will help you.

Since the command shell program (sqlite3.exe) doesn't attempt to
implement a procedural language (variables, loops, ,etc)  I don't know
that you could do what you need to do with the count anyway.

You *may* find that if you replace your single selects with:
 Select 'The count is ',  count(*) from blah, blah, blah.   --
perform select just to get a count
 Select * from blah, blah, blah -- this
time for real
The second select goes much faster because of caching.

Or maybe run a test with EXPLAIN QUERY PLAN as prefix to the select in
order to be sure it's using the indicies you expect -- maybe
*everything* can be made much faster.

A fancier option would be to cache the results of your select into a
temporary table, such as:
  CREATE TEMP TABLE stuff_temp AS SELECT blah, blah, blah;
  SELECT 'The count of stuff is', SELECT COUNT(*) FROM stuff_temp;

  You might look at "PRAGMA temp_store" to optimize handling of
temporary tables.

I think that the command line sqlite3.exe was mainly intended as a
demonstration and a testing tool -- most users want to link the library
into a language of their choice -- all the more true if speed is
particularly important.

Don't get me wrong; I myself love to mess with sqlite3.exe and create
crazy batch files. ;-)



[opinions are my own, not necessarily those of my company]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Any way to know the numbers of rows affected by a cmd?

[Anderson, James H \(IT\)]

Yes, thanks, I can do that but if the table is large it's quite slow. I
was hoping there was a quicker way, as there is in sybase. 

-Original Message-
From: Tom VP [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 4:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any way to know the numbers of rows affected by a
cmd?

Indeed, try: select count() ...
update count() ...
delete count() ...

Dr. Tom
- Original Message - 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, March 01, 2007 3:40 PM
Subject: [sqlite] Any way to know the numbers of rows affected by a cmd?


For a select, the number of rows selected.

For an update, the number of rows updates.

For a delete the number of rows deleted.

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does 
not intend to waive confidentiality or privilege. Use of this email is 
prohibited when received in error.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: Any way to know the numbers of rows affected by a cmd?

[Anderson, James H \(IT\)]

I guess I forgot to mention the context. I'm interested in doing this
from the cmd shell. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 4:02 PM
To: SQLite
Subject: [sqlite] Re: Any way to know the numbers of rows affected by a
cmd?

Anderson, James H (IT)
 wrote:
> For a select, the number of rows selected.

Just count them as you step through them.

> For an update, the number of rows updates.
>
> For a delete the number of rows deleted.

sqlite3_changes, sqlite3_total_changes

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Any way to know the numbers of rows affected by a cmd?

[Anderson, James H \(IT\)]

For a select, the number of rows selected.

For an update, the number of rows updates.

For a delete the number of rows deleted.

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

[sqlite] sybase => sqlite "translation" ???

[Anderson, James H \(IT\)]

I'm trying to implement the equivalent of the following sybase query in
sqlite, but I suspect I've got it wrong. Any help much appreciated,
jim


update tmpRR_ML_final
   set id = case when a.CDRefSNP   <> 'NR' then b.id
 when a.CDRefSNP   =  'NR'
  and a.CDRefMoody <> 'NR' then c.id
 else 8
end 
 from TMP_credDerivOrig a,
  crRefRatingMapb,
  crRefRatingMapc,
  tmpRR_ML_finale
 where a.CDRefSNP   *= b.rating
   and a.CDRefMoody *= c.rating
   and b.isPrimary   = 2
   and c.isPrimary   = 3
   and e.externalEquiv is null
   and a.CDId= e.CDId
   and a.CDSellBuy   = e.CDSellBuy


My attempt at an sqlite "translation":

update tmpRR_ML_final
set id = ifnull(
(select case
when a.CDRefSNP   <> 'NR' then b.id
when a.CDRefMoody <> 'NR' then c.id
else 8
end
   from TMP_credDerivOrig  a
  LEFT JOIN crRefRatingMap b ON a.CDRefSNP   = b.rating
  LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
  where tmpRR_ML_final.CDId  = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3),
 id)

or, alternatively

update tmpRR_ML_final
set id =
(select case
when a.CDRefSNP   <> 'NR' then b.id
when a.CDRefMoody <> 'NR' then c.id
else 8
end
   from TMP_credDerivOrig  a
  LEFT JOIN crRefRatingMap b ON a.CDRefSNP   = b.rating
  LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
  where tmpRR_ML_final.CDId  = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3)
where exists (
select 1
   from TMP_credDerivOrig  a
  LEFT JOIN crRefRatingMap b ON a.CDRefSNP   = b.rating
  LEFT JOIN crRefRatingMap c ON a.CDRefMoody = c.rating
  where tmpRR_ML_final.CDId  = a.CDId
and tmpRR_ML_final.CDSellBuy = a.CDSellBuy
and tmpRR_ML_final.externalEquiv is null
and b.isPrimary = 2
and c.isPrimary = 3
);


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Re: Re: Looking for equivalent syntax

[Anderson, James H \(IT\)]

OK, thanks again. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 20, 2007 10:56 AM
To: SQLite
Subject: [sqlite] Re: Re: Looking for equivalent syntax

Anderson, James H (IT)
 wrote:
> In order to improve my understanding, I'd like to ask 2 questions re
> the
> sql, below.
>
> 1. what is the relationship between the "select * from where" within
> the
> "where exists" and the "select yadayadayada from where" within the
> set?

No direct relationship. However, the conditions in the two selects are 
designed to be similar enough, so that when EXISTS test succeeds by 
finding a suitable row, the select in SET would extract a field from 
that same row.

> 2. why is it not necessary to include the "b.CDEvent=c.CDEvent" which
> is
> present in the "where exists" in the "where" within the set?

Now that I think of it, it might be necessary to join to tmp_events in 
the SET clause after all. Suppse the data looks like this:

select CDId from C1_credDerivEvent;
1

select CDId, CDEvent from C1_tmp_credDerivEvent;
110
120

select CDEvent from tmp_events;
20

Here a select that uses all three tables would produce 20. A select that

omits tmp_events would produce two records with the values 10 and 20, 
from which SQLite would just pick the first one. This could be 10, 
giving a wrong answer.

So to be on the safe side, make it

update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
 from C1_tmp_credDerivEvent b, tmp_events c
 where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: Looking for equivalent syntax

[Anderson, James H \(IT\)]

In order to improve my understanding, I'd like to ask 2 questions re the
sql, below.

1. what is the relationship between the "select * from where" within the
"where exists" and the "select yadayadayada from where" within the set?
In other words, are they connected and if so, how?

2. why is it not necessary to include the "b.CDEvent=c.CDEvent" which is
present in the "where exists" in the "where" within the set?

Thanks,

jim

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 2:04 PM
To: SQLite
Subject: [sqlite] Re: Looking for equivalent syntax

Anderson, James H (IT)
 wrote:
> I'm trying to convert the following statement in Sybase syntax into
> the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent

update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
 from C1_tmp_credDerivEvent b
 where C1_credDerivEvent.CDId = b.CDId)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: Looking for equivalent syntax

[Anderson, James H \(IT\)]

Thanks! I'll give it a try. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 2:04 PM
To: SQLite
Subject: [sqlite] Re: Looking for equivalent syntax

Anderson, James H (IT)
 wrote:
> I'm trying to convert the following statement in Sybase syntax into
> the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent

update C1_credDerivEvent
set CDEvent = CDEvent || ',' ||
(select b.CDEvent
 from C1_tmp_credDerivEvent b
 where C1_credDerivEvent.CDId = b.CDId)
where exists (
select * from C1_tmp_credDerivEvent b, tmp_events c
where C1_credDerivEvent.CDId = b.CDId and b.CDEvent=c.CDEvent
)

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Looking for equivalent syntax

[Anderson, James H \(IT\)]

The original statement works fine in Sybase. I did not try it in Sqlite
because my previous experience is that the update statement in Sqlite
does not support the "from" clause. Am I missing something here?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Monday, February 19, 2007 1:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Looking for equivalent syntax

On 2/19/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> I'm trying to convert the following statement in Sybase syntax into
the
> equivalent sqlite syntax:
>
> update C1_credDerivEvent
>set a.CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId= b.CDId
>and b.CDEvent = c.CDEvent
>
> Here's what I came up with but it turns out not to be equivalent at
all:
>
> insert or replace into C1_credDerivEvent
>   select A.CDId as CDId,
>  A.CDEvent || ',' || B.CDEvent as CDEvent
>   from C1_credDerivEvent A,
>C1_tmp_credDerivEvent B,
>tmp_eventsC
>  where A.CDId= B.CDId
>and B.CDEvent = C.CDEvent;
>
> The Sybase statement simply updates each record for which the where
> clause is satified, yielding the same number of rows in the table
before
> the update as after.
>
> The Sqlite statement on the other hand, adds rows to the table.
>


well yes, you yourself are asking SQLite to "INSERT or REPLACE"... see
the INSERT part... that adds rows to the table.

In your Sybase version, you are only UPDATEin (equivalent to the
REPLACE part). SQLite is just doing what you are asking it to do.

What is wrong with your original statement? You never mentioned
whether that worked on not... did you try it? (listed again below)

update C1_credDerivEvent
  set a.CDEvent = a.CDEvent || ',' || b.CDEvent
 from C1_credDerivEvent a,
  C1_tmp_credDerivEvent b,
  tmp_eventsc
 where a.CDId= b.CDId
  and b.CDEvent = c.CDEvent

You could update it to the more standard-ish syntax like so

UPDATE C1_credDerivEvent
SET a.CDEvent = a.CDEvent || ',' || b.CDEvent
FROM C1_credDerivEvent a JOIN C1_tmp_credDerivEvent b ON
  a.CDId  = b.CDId JOIN tmp_events c ON b.CDEvent = c.CDEvent


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Looking for equivalent syntax

[Anderson, James H \(IT\)]

I'm trying to convert the following statement in Sybase syntax into the
equivalent sqlite syntax:

update C1_credDerivEvent
   set a.CDEvent = a.CDEvent || ',' || b.CDEvent
  from C1_credDerivEvent a,
   C1_tmp_credDerivEvent b,
   tmp_eventsc
 where a.CDId= b.CDId
   and b.CDEvent = c.CDEvent

Here's what I came up with but it turns out not to be equivalent at all:

insert or replace into C1_credDerivEvent
  select A.CDId as CDId,
 A.CDEvent || ',' || B.CDEvent as CDEvent
  from C1_credDerivEvent A,
   C1_tmp_credDerivEvent B,
   tmp_eventsC
 where A.CDId= B.CDId
   and B.CDEvent = C.CDEvent;

The Sybase statement simply updates each record for which the where
clause is satified, yielding the same number of rows in the table before
the update as after.

The Sqlite statement on the other hand, adds rows to the table.

Rowcount before "insert or replace": 691,066
Rowcount after "insert or replace": 7,594,268

What am I doing wrong? Is there an equivalent Sqlite syntax or not?

Thanks,
Jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] retrieval speedup help requested

[Anderson, James H \(IT\)]

Where does one get "analyze"? 

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Saturday, February 17, 2007 5:25 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] retrieval speedup help requested

Try running: analyze city_loc
after adding the index.

RBS


-Original Message-
From: Tom Shaw [mailto:[EMAIL PROTECTED] 
Sent: 17 February 2007 22:16
To: sqlite-users@sqlite.org
Subject: [sqlite] retrieval speedup help requested

In sqlite 3 I have two tables. city_loc has 156865 entries and 
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region 
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER 
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM 
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of
space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom




-
To unsubscribe, send email to [EMAIL PROTECTED]


-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Disk corruption?

[Anderson, James H \(IT\)]

Is this likely to just be a hardware error.

database disk image is malformed(1) at dbdimp.c line 398

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Re: another

[Anderson, James H \(IT\)]

A syntax error, "duplicate column name". 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 14, 2007 10:24 AM
To: SQLite
Subject: [sqlite] Re: another

Anderson, James H (IT)
 wrote:
> If I run the following sql
>
> create table table_C as
> select
> A.col_1,
> B.col_2
>
>   from table_A A,
>table_B B
> where A.col_3 = B.col_4
>
> The table_C is created with the following column names:
>
> "A.col_1",
> "B.col_2"
>
> It seems to me sqlite should strip off the alias qualifier and create
> the table as
>
> col_1,
> col_2

What do you think it should do for something like

create table C as
select A.col, B.col
from A, B;

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] another

[Anderson, James H \(IT\)]

If I run the following sql

create table table_C as
select
A.col_1,
B.col_2

  from table_A A,
   table_B B
where A.col_3 = B.col_4

The table_C is created with the following column names:

"A.col_1",
"B.col_2"

Quotes and all. How bizarre is that? Am I doing something wrong? (The
only way around this that I've found is to change the select to

select
A.col_1 as col_1,
B.col_2 as col_2

It seems to me sqlite should strip off the alias qualifier and create
the table as

col_1,
col_2

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] help with syntax

[Anderson, James H \(IT\)]

That did the trick. Thanks very much.

jim 

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 13, 2007 4:55 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] help with syntax

Anderson, James H (IT) wrote:
> I have the following sybase query (a left join) that I'm having
trouble
> translating into the "LEFT JOIN" syntax. Any help appreciated.
>
> Thanks,
> jim.
>
>
> create table tmpRR_ML as
> select
> b.ratingCodeas ratingCode,
> a.CDSpreadCurve as CDSpreadCurve
>   from tmpRR a,
>MasterList b,
>crRefRating c
>  where b.dunsNumber = b.ultimateDuns;
>and a.Credit_Ultimate_Party_Id *= b.dunsNumber
>and b.ratingCode *= c.rating;
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>
>   
James,

I suspect this should be the following for SQLite, but I'm just guessing

at the *= syntax from sybase.

create table tmpRR_ML as
select
b.ratingCodeas ratingCode,
a.CDSpreadCurve as CDSpreadCurve
  from tmpRR a 
left join MasterList b on a.Credit_Ultimate_Party_Id = b.dunsNumber
left join crRefRating c on b.ratingCode = c.rating
 where b.dunsNumber = b.ultimateDuns;

HTH
Dennis Cote





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] help with syntax

[Anderson, James H \(IT\)]

Thanks, Dennis, I'll try that. Yes, "*=" means "LEFT JOIN" in sybase
syntax. 

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 13, 2007 4:55 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] help with syntax

Anderson, James H (IT) wrote:
> I have the following sybase query (a left join) that I'm having
trouble
> translating into the "LEFT JOIN" syntax. Any help appreciated.
>
> Thanks,
> jim.
>
>
> create table tmpRR_ML as
> select
> b.ratingCodeas ratingCode,
> a.CDSpreadCurve as CDSpreadCurve
>   from tmpRR a,
>MasterList b,
>crRefRating c
>  where b.dunsNumber = b.ultimateDuns;
>and a.Credit_Ultimate_Party_Id *= b.dunsNumber
>and b.ratingCode *= c.rating;
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>
>   
James,

I suspect this should be the following for SQLite, but I'm just guessing

at the *= syntax from sybase.

create table tmpRR_ML as
select
b.ratingCodeas ratingCode,
a.CDSpreadCurve as CDSpreadCurve
  from tmpRR a 
left join MasterList b on a.Credit_Ultimate_Party_Id = b.dunsNumber
left join crRefRating c on b.ratingCode = c.rating
 where b.dunsNumber = b.ultimateDuns;

HTH
Dennis Cote





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] help with syntax

[Anderson, James H \(IT\)]

I have the following sybase query (a left join) that I'm having trouble
translating into the "LEFT JOIN" syntax. Any help appreciated.

Thanks,
jim.


create table tmpRR_ML as
select
b.ratingCodeas ratingCode,
a.CDSpreadCurve as CDSpreadCurve
  from tmpRR a,
   MasterList b,
   crRefRating c
 where b.dunsNumber = b.ultimateDuns;
   and a.Credit_Ultimate_Party_Id *= b.dunsNumber
   and b.ratingCode *= c.rating;


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Looks like removing the semi-colon did the trick. Thanks very much. 

-Original Message-
From: Matt Sergeant [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 08, 2007 11:09 AM
To: Anderson, James H (IT)
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 8-Feb-07, at 11:04 AM, Anderson, James H ((IT)) wrote:

> The output after setting $dbh->trace(3):

Ah. Please re-try after taking the semi-colon off the end of your SQL.


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

The output after setting $dbh->trace(3):

insert or replace into TMP_credDerivOrig
select
A.date,
A.CDId,
A.CDName,
A.CDTicket,
A.tradeId,
A.tapsAccount,
A.CDBook,
coalesce(A.CDFid,'') CDFid,
A.CDStatus,
A.CDTradeDate,
A.CDExpDate,
A.CDNotional,
A.CDCurr,
A.CDSellBuy,
A.CDType,
A.CDExerType,
A.CDEntity,
A.CDCusip,
A.CDSetlType,
A.CDCredInit,
A.CDSingleEntry,
A.CDMaterialType,
A.CDEffDate,
A.CDPremFreq,
A.CDPaymentType,
A.CDUpfrontSetl,
A.CDPublicInfo,
A.CDCollReq,
A.CDSpreadCurve,
A.CDPremium,
A.CDOptType,
A.CDAccrue,
A.CDRefPrice,
A.CDPremiumAmnt,
A.CDLastCoupLength,
A.CDWhoDelivers,
A.CDCollateralText,
A.CDFactorReason,
A.CDDefStartProt,
A.CDDefEndProt,
A.CDDefProtType,
B.CDComment,
A.CDEvent,
A.CDCurveType,
A.CrvShName,
A.CDRefEntity,
A.CDRefIndustry,
A.CDRefCountry,
A.CDRefSNP,
A.CDRefMoody,
A.CDRefMSRating,
A.CDRefId
  from TMP_credDerivOrig   A,
   C1_credDerivComment B
 where A.CDId = B.CDId;
-> prepare for DBD::SQLite::db (DBI::db=HASH(0x8159970)~0x8389c6c '

insert or replace into TMP_credDerivOrig
select
A.date,
A.CDId,
A.CDName,
A.CDTicket,
A.tradeId,
A.tapsAccount,
A.CDBook,
coalesce(A.CDFid,'') CDFid,
A.CDStatus,
A.CDTradeDate,
A.CDExpDate,
A.CDNotional,
A.CDCurr,
A.CDSellBuy,
A.CDType,
A.CDExerType,
A.CDEntity,
A.CDCusip,
A.CDSetlType,
A.CDCredInit,
A.CDSingleEntry,
A.CDMaterialType,
A.CDEffDate,
A.CDPremFreq,
A.CDPaymentType,
A.CDUpfrontS...') thr#813b008
dbih_setup_handle(DBI::st=HASH(0x8390cb8)=>DBI::st=HASH(0x8389d8c),
DBD::SQLite::st, 8390c88, Null!)
dbih_make_com(DBI::db=HASH(0x8389c6c), 838a0b8, DBD::SQLite::st,
124, 0) thr#813b008
sqlite trace: prepare statement: 

insert or replace into TMP_credDerivOrig
select
A.date,
A.CDId,
A.CDName,
A.CDTicket,
A.tradeId,
A.tapsAccount,
A.CDBook,
coalesce(A.CDFid,'') CDFid,
A.CDStatus,
A.CDTradeDate,
A.CDExpDate,
A.CDNotional,
A.CDCurr,
A.CDSellBuy,
A.CDType,
A.CDExerType,
A.CDEntity,
A.CDCusip,
A.CDSetlType,
A.CDCredInit,
A.CDSingleEntry,
A.CDMaterialType,
A.CDEffDate,
A.CDPremFreq,
A.CDPaymentType,
A.CDUpfrontSetl,
A.CDPublicInfo,
A.CDCollReq,
A.CDSpreadCurve,
A.CDPremium,
A.CDOptType,
A.CDAccrue,
A.CDRefPrice,
A.CDPremiumAmnt,
A.CDLastCoupLength,
A.CDWhoDelivers,
A.CDCollateralText,
A.CDFactorReason,
A.CDDefStartProt,
A.CDDefEndProt,
A.CDDefProtType,
B.CDComment,
A.CDEvent,
A.CDCurveType,
A.CrvShName,
A.CDRefEntity,
A.CDRefIndustry,
A.CDRefCountry,
A.CDRefSNP,
A.CDRefMoody,
A.CDRefMSRating,
A.CDRefId
  from TMP_credDerivOrig   A,
   C1_credDerivComment B
 where A.CDId = B.CDId; at dbdimp.c line 258
<- prepare= DBI::st=HASH(0x8390cb8) at DBUtilLite.pm line 185 via
at DBUtilLite.pm line 185
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x8390b80)~INNER)
thr#813b008
<- DESTROY= undef at DBUtilLite.pm line 186 via  at DBUtilLite.pm
line 186
-> execute for DBD::SQLite::st (DBI::st=HASH(0x8390cb8)~0x8389d8c)
thr#813b008
sqlite trace: Execute returned 0 cols
 at dbdimp.c line 391
<- execute= 566606 at DBUtilLite.pm line 187 via  at DBUtilLite.pm
line 187
-> rows for DBD::SQLite::st (DBI::st=HASH(0x8390cb8)~0x8389d8c)
thr#813b008
<- rows= 566606 at DBUtilLite.pm line 189 via  at test.pl line 29
rows affected: 566606

-> prepare for DBD::SQLite::db (DBI::db=HASH(0x8159970)~0x8389c6c '
') thr#813b008
dbih_setup_handle(DBI::st=HASH(0x8390cc4)=>DBI::st=HASH(0x8390a6c),
DBD::SQLite::st, 8390ca0, Null!)
dbih_make_com(DBI::db=HASH(0x8389c6c), 838a0b8, DBD::SQLite::st,
124, 0) thr#813b008
sqlite trace: prepare statement: 
 at dbdimp.c line 258
<- prepare= DBI::st=HASH(0x8390cc4) at DBUtilLite.pm line 185 via
at DBUtilLite.pm line 185
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x8389d8c)~INNER)
thr#813b008
<- DESTROY= undef at DBUtilLite.pm line 186 via  at DBUtilLite.pm
line 186
-> execute for DBD::SQLite::st (DBI::st=HASH(0x8390cc4)~0x8390a6c)
thr#813b008
sqlite trace: Execute returned 0 cols
 at dbdimp.c line 391
sqlite error 21 recorded: not an error at dbdimp.c line 398
!! ERROR: 21 'not an error(21) at dbdimp.c line 398' (err#0)
<- execute= undef at DBUtilLite.pm line 187 via  at DBUtilLite.pm
line 187
1   -> FETCH for DBD::SQLite::st (DBI::st=HASH(0x8390a6c)~INNER
'ParamValues') thr#813b008
.. FETCH DBI::st=HASH(0x8390a6c) 'ParamValues' = undef
   ERROR: 21 'not an error(21) at dbdimp.c line 398' (err#0)
1   <- FETCH= undef at DBUtilLite.pm line 187 via  at DBUtilLite.pm line
187
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
[for Statement "
"] at /u/crdceed/lib/perl5/DBUtilLite.pm line 187.
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
[for Statement "
"] at /u/crdceed/lib/perl5/DBUtilLite.pm line 187.
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x8390a6c)~INNER)
thr#813b008
   ERROR: 21 'not an error(21) at dbdimp.c line 398' (err#0)
<- DESTROY= undef
-> DESTROY for DBD::SQLite::db (DBI::db=HASH(0x8389c6c)~INNER)
thr#813b008
   ERROR: 21 'not an error(21) at dbdimp.c line 398' (err#0)
<- 

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

I get the exact same failure after making the suggested changes.

DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
[for Statement "
"] at /u/crdceed/lib/perl5/DBUtilLite.pm line 185.
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
[for Statement "
"] at /u/crdceed/lib/perl5/DBUtilLite.pm line 185.

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 7:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

Jim,

Line 398 in dbdimp.c appears to be in DBD-SQLite's $sth->execute code.

I agree with Puneet.  If you wrap your DBI calls in eval blocks and test
$@, you might get more info about the error (or maybe not).  Also,
setting RaiseError, and ShowErrorStatement in $dbh wouldn't hurt.

$dbh = DBI->connect("dbi:SQLite:dbname=$dbname","","",
{RaiseError=>1, ShowErrorStatement=>1});
$sql = "insert or replace into...";
eval { $sth = $dbh->prepare( $sql ) };
die $@ if ($@);
eval { $sth->execute };
die $@ if ($@);


Not sure if any of that helps at all.  "not an error" on $sth->execute
is bound to be nasty to diagnose.  FWIW, it looks like "not an error" is
the result of a call to sqlite3_errmsg().

Unrelated to your issue is a DBI presentation you may be interested to
look through at
http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 3:28:00 PM
Subject: RE: [sqlite] What does this mean???

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl that
> violated whatever it was at line 398 of the c program dbdimp.c.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/educa

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Found the following in sqlite.h.in. This indicates to me that DBD-SQLite
isn't handling this "error" string correctly.

/*
** Return the error code for the most recent sqlite3_* API call
associated
** with sqlite3 handle 'db'. SQLITE_OK is returned if the most recent 
** API call was successful.
**
** Calls to many sqlite3_* functions set the error code and string
returned
** by sqlite3_errcode(), sqlite3_errmsg() and sqlite3_errmsg16()
** (overwriting the previous values). Note that calls to
sqlite3_errcode(),
** sqlite3_errmsg() and sqlite3_errmsg16() themselves do not affect the
** results of future invocations.
**
** Assuming no other intervening sqlite3_* API calls are made, the error
** code returned by this function is associated with the same error as
** the strings  returned by sqlite3_errmsg() and sqlite3_errmsg16().
*/
int sqlite3_errcode(sqlite3 *db);

/*
** Return a pointer to a UTF-8 encoded string describing in english the
** error condition for the most recent sqlite3_* API call. The returned
** string is always terminated by an 0x00 byte.
**
** The string "not an error" is returned when the most recent API call
was
** successful.
*/
const char *sqlite3_errmsg(sqlite3*);

/*
** Return a pointer to a UTF-16 native byte order encoded string
describing
** in english the error condition for the most recent sqlite3_* API
call.
** The returned string is always terminated by a pair of 0x00 bytes.
**
** The string "not an error" is returned when the most recent API call
was
** successful.
*/
const void *sqlite3_errmsg16(sqlite3*); 

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 7:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

Jim,

Line 398 in dbdimp.c appears to be in DBD-SQLite's $sth->execute code.

I agree with Puneet.  If you wrap your DBI calls in eval blocks and test
$@, you might get more info about the error (or maybe not).  Also,
setting RaiseError, and ShowErrorStatement in $dbh wouldn't hurt.

$dbh = DBI->connect("dbi:SQLite:dbname=$dbname","","",
{RaiseError=>1, ShowErrorStatement=>1});
$sql = "insert or replace into...";
eval { $sth = $dbh->prepare( $sql ) };
die $@ if ($@);
eval { $sth->execute };
die $@ if ($@);


Not sure if any of that helps at all.  "not an error" on $sth->execute
is bound to be nasty to diagnose.  FWIW, it looks like "not an error" is
the result of a call to sqlite3_errmsg().

Unrelated to your issue is a DBI presentation you may be interested to
look through at
http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 3:28:00 PM
Subject: RE: [sqlite] What does this mean???

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl t

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Thanks, Clark. I'll give that a try.

jim 

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 7:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

Jim,

Line 398 in dbdimp.c appears to be in DBD-SQLite's $sth->execute code.

I agree with Puneet.  If you wrap your DBI calls in eval blocks and test
$@, you might get more info about the error (or maybe not).  Also,
setting RaiseError, and ShowErrorStatement in $dbh wouldn't hurt.

$dbh = DBI->connect("dbi:SQLite:dbname=$dbname","","",
{RaiseError=>1, ShowErrorStatement=>1});
$sql = "insert or replace into...";
eval { $sth = $dbh->prepare( $sql ) };
die $@ if ($@);
eval { $sth->execute };
die $@ if ($@);


Not sure if any of that helps at all.  "not an error" on $sth->execute
is bound to be nasty to diagnose.  FWIW, it looks like "not an error" is
the result of a call to sqlite3_errmsg().

Unrelated to your issue is a DBI presentation you may be interested to
look through at
http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 3:28:00 PM
Subject: RE: [sqlite] What does this mean???

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl that
> violated whatever it was at line 398 of the c program dbdimp.c.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NO

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl that
> violated whatever it was at line 398 of the c program dbdimp.c.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

sqlite-version: 3.3.5
DBD-SQLite: 1.12

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 6:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

What version of SQLite, and what version of DBD-SQLite?

I saw this predictably with DBD-SQLite 1.09 and SQLite 3.2.7 where I did
something like:

$sth = $dbh->prepare("select foo, bar from mytable where rowid =
?");
for $i (1..5)  {
($myfoo, $mybar) = $dbh->selectrow_array($sth, undef, $i);
}

It would work for the first iteration, then raise a "not an error" error
on the second.  It was annoying, but I usually just worked around it
using $sth->execute/bind_columns/fetch inside the loop.

I updated to DBD-SQLite 1.13 with SQLite 3.3.12, and it worked like it's
documented for DBI (fixed the problem).

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 2:33:21 PM
Subject: [sqlite] What does this mean???

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Context :)

insert or replace into TMP_credDerivOrig
select
A.date,
A.CDId,
A.CDName,
A.CDTicket,
A.tradeId,
A.tapsAccount,
A.CDBook,
coalesce(A.CDFid,'') CDFid,
A.CDStatus,
A.CDTradeDate,
A.CDExpDate,
A.CDNotional,
A.CDCurr,
A.CDSellBuy,
A.CDType,
A.CDExerType,
A.CDEntity,
A.CDCusip,
A.CDSetlType,
A.CDCredInit,
A.CDSingleEntry,
A.CDMaterialType,
A.CDEffDate,
A.CDPremFreq,
A.CDPaymentType,
A.CDUpfrontSetl,
A.CDPublicInfo,
A.CDCollReq,
A.CDSpreadCurve,
A.CDPremium,
A.CDOptType,
A.CDAccrue,
A.CDRefPrice,
A.CDPremiumAmnt,
A.CDLastCoupLength,
A.CDWhoDelivers,
A.CDCollateralText,
A.CDFactorReason,
A.CDDefStartProt,
A.CDDefEndProt,
A.CDDefProtType,
B.CDComment,
A.CDEvent,
A.CDCurveType,
A.CrvShName,
A.CDRefEntity,
A.CDRefIndustry,
A.CDRefCountry,
A.CDRefSNP,
A.CDRefMoody,
A.CDRefMSRating,
A.CDRefId
  from TMP_credDerivOrig   A,
   C1_credDerivComment B
 where A.CDId = B.CDId;
rows affected: 566606 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 5:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> not an error(21) at dbdimp.c line 398
>
> I'm using DBD::SQLite and got this error. What does it mean and how
best
> to hanle it?
>


well, how about some context? What is the structure of the table(s)
you are querying, what is the query, the relevant code snippet,
something to go on?

The error string itself means that you did something via Perl that
violated whatever it was at line 398 of the c program dbdimp.c.

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

[sqlite] What does this mean???

[Anderson, James H \(IT\)]

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Another sybase conversion question

[Anderson, James H \(IT\)]

Thanks, Joe! That looks like it might be the solution. 

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 9:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another sybase conversion question

--- "Anderson, James H (IT)" <[EMAIL PROTECTED]> wrote:
> The following works fine in sybase, but because in sqlite "update"
does
> not support "from" it doesn't work. Is there another way of doing
this?
> 
> Thanks,
> 
> Jim
> 
> update C1_credDerivEvent
>set CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId = b.CDId
>and b.CDApplicable = 'Yes'
>and b.CDEvent  = c.CDEvent;

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



 


Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo!
Games.
http://videogames.yahoo.com/platform?platform=120121


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Another sybase conversion question

[Anderson, James H \(IT\)]

Thanks, Rich. Yes, I have the book and it's terrific! As you say, the
index is shamefully bad. I'm gradually working my way through it but I,
too, am under pressure to produce results so I'm forced to ask many
questions that I could probably find the answers to myself if I had more
time. 

-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 6:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another sybase conversion question

On Fri, 2 Feb 2007, Anderson, James H (IT) wrote:

> The following works fine in sybase, but because in sqlite "update"
does
> not support "from" it doesn't work. Is there another way of doing
this?

Jim,

   I'm in the midst of trying to meet a deadline, so I'll let someone
more
knowledgable translate for you.

   But, if you're going to do much with SQLite, I strongly recommend
Mike
Owens' "The Definitive Guide to SQLite" by Apress. The index is
shamefully
bad, but the book is a gem and has been a great help to me. Very highly
recommended -- despite the index. :-)

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental
Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Another sybase conversion question

[Anderson, James H \(IT\)]

The following works fine in sybase, but because in sqlite "update" does
not support "from" it doesn't work. Is there another way of doing this?

Thanks,

Jim

update C1_credDerivEvent
   set CDEvent = a.CDEvent || ',' || b.CDEvent
  from C1_credDerivEvent a,
   C1_tmp_credDerivEvent b,
   tmp_eventsc
 where a.CDId = b.CDId
   and b.CDApplicable = 'Yes'
   and b.CDEvent  = c.CDEvent;


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] "select into" ?

[Anderson, James H \(IT\)]

Perfect! Thanks. 

-Original Message-
From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 5:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] "select into" ?

Anderson, James H (IT) wrote:
> Sybase supports "select into" but sqllite does not seem to. Here's a
> sybase example:
> 
> select distinct CDId, CDEvent
>   into credDerivEvent
>   from credDerivOrig
> 
> This query creates the table credDerivEvent. Is there anythin
comparable
> in sqlite?

How about

CREATE TABLE credDerivEvent AS SELECT DISTINCT CDId, CDEvent FROM 
credDerivOrig

HTH,

Gerry


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] "select into" ?

[Anderson, James H \(IT\)]

"select into" creates the table; "insert into" requires that the table
already exist. 

-Original Message-
From: Jeff Godfrey [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 5:12 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] "select into" ?

> - Original Message - 
> From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
>
> Sybase supports "select into" but sqllite does not seem to. Here's a
> sybase example:
>
> select distinct CDId, CDEvent
>   into credDerivEvent
>   from credDerivOrig
>
> This query creates the table credDerivEvent. Is there anythin 
> comparable
> in sqlite?

I'm no expert, but how does that differ from "insert into ??? select 
??? from ???", which is supported by sqlite?

Jeff



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] "select into" ?

[Anderson, James H \(IT\)]

Sybase supports "select into" but sqllite does not seem to. Here's a
sybase example:

select distinct CDId, CDEvent
  into credDerivEvent
  from credDerivOrig

This query creates the table credDerivEvent. Is there anythin comparable
in sqlite?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

That's an interesting idea, thanks, Kees. 

-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 2:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Function question


Hi Jim,

On Thu, 1 Feb 2007 09:08:44 -0500, you wrote:

>From the CLP (i.e., w/o user-defined functions) is there any way to get
>the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
>spaces from the right/left end of a column value.)

As far as I can tell load_extension(X,Y) can be used in the
command line program. 

I usually postprocess output by piping it through awk to solve
these kinds of problems.

sqlite3 databasefile outfile

>jim

HTH
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

OK, thanks. 

-Original Message-
From: Nemanja Corlija [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 2:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Function question

On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Does the fact that I have received no reply mean that there's no way
to
> get this functionality within the CLP?
>
> -Original Message-
> From: Anderson, James H (IT)
> Sent: Thursday, February 01, 2007 9:09 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Function question
>
> From the CLP (i.e., w/o user-defined functions) is there any way to
get
> the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
> spaces from the right/left end of a column value.)
>
SQLite doesn't have any trim functions by default. Here's a list of
available functions:

http://www.sqlite.org/lang_expr.html#corefunctions

If you decide to write one your self, you can compile it as a loadable
extension and load it at runtime from SQL using load_extension()
function.

-- 
Nemanja Corlija <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

Does the fact that I have received no reply mean that there's no way to
get this functionality within the CLP? 

-Original Message-
From: Anderson, James H (IT) 
Sent: Thursday, February 01, 2007 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Function question

>From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Function question

[Anderson, James H \(IT\)]

>From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

Thanks. Unfortunately my background is sybase and that's anything but
standard :-( 

-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 6:52 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

On Wed, 31 Jan 2007 17:30:29 -0500, you wrote:

>BTW, what is the concatenation operator? 

Standard SQL:  string || string
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

Thanks, somehow I had missed it. 

-Original Message-
From: Nicolas Williams [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 6:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

On Wed, Jan 31, 2007 at 06:31:20PM -0500, Anderson, James H (IT) wrote:
> Is cast documented on the sqlite website? I couldn't find it. 

http://www.sqlite.org/

Click on 'syntax', click on 'expression', arrive at:

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


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What is the sqlite concatenation operator?

[Anderson, James H \(IT\)]

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

Is cast documented on the sqlite website? I couldn't find it. 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 5:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> In the case shown, for example,
> 
> convert(char(3), NULL) CDRefIndustry,
> 
> It creates a char(3) column, sets it to null, and names it
> CDRefIndustry.
> 

Dennis Cote's guess was mostly right then.  A strict equivalent 
in SQLite (and in standard SQL) would be:

  cast(NULL AS char(3)) CDRefIndustry

But the cast is not really necessary in SQLite.  You could
get by with just this:

  NULL CDRefIndustry

So, tell me James, what is MorganStanley doing with SQLite?  ;-)

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

Experimenting :)  

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 5:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> In the case shown, for example,
> 
> convert(char(3), NULL) CDRefIndustry,
> 
> It creates a char(3) column, sets it to null, and names it
> CDRefIndustry.
> 

Dennis Cote's guess was mostly right then.  A strict equivalent 
in SQLite (and in standard SQL) would be:

  cast(NULL AS char(3)) CDRefIndustry

But the cast is not really necessary in SQLite.  You could
get by with just this:

  NULL CDRefIndustry

So, tell me James, what is MorganStanley doing with SQLite?  ;-)

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

OK, thanks, I'll try that. 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, January 31, 2007 5:34 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> In the case shown, for example,
>
> convert(char(3), NULL) CDRefIndustry,
>
> It creates a char(3) column, sets it to null, and names it
> CDRefIndustry.

Since SQLite has no datatypes, char(3) doesn't mean anything to it. How
about

SELECT null AS CDRefIndustry

or

SELECT '' AS CDRefIndustry


>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 31, 2007 5:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Equivalent syntax?
>
> "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> > What's the equivalent sqlite syntax for sybase convert function?
>
> Can you describe what the convert function in sybase does?  That
> might help us to find the equivalent function in SQLite for you.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

I don't see the cast function listed in the Core Functions section of
the web page...

BTW, what is the concatenation operator? 

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 5:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

Anderson, James H (IT) wrote:
> What's the equivalent sqlite syntax for sybase convert function? For
> example,
>
> select distinct
> date, CDId, CDName, CDTicket,
> tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid,
> CDStatus, CDTradeDate, CDExpDate, CDNotional,
> CDCurr, CDSellBuy, CDType, CDExerType,
> CDEntity, CDCusip, CDSetlType, CDCredInit,
> CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq,
> CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq,
> CDSpreadCurve, CDPremium, CDOptType, CDAccrue,
> CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers,
> CDCollateralText, CDFactorReason, CDDefStartProt,
CDDefEndProt,
> CDDefProtType,
> convert(char(80), NULL) CDComment,
> convert(varchar(100), NULL) CDEvent,
> convert(char(11), NULL) CDCurveType,
> convert(char(11), NULL) CrvShName,
> convert(varchar(90), NULL) CDRefEntity,
> convert(char(3), NULL) CDRefIndustry,
> convert(char(3), NULL) CDRefCountry,
> convert(char(4), NULL) CDRefSNP,
> convert(char(4),NULL) CDRefMoody,
> convert(char(4),NULL) CDRefMSRating,
> CDRefId
> into TMP_credDerivOrig 
> from credDerivOrig_C1
>
>   
Based on context I would say the cast function is the rough equivalent.

But, since all text types in SQLite are equivalent there is no need to 
convert them from one type to another (i.e. from varchar(100) to 
char(80)). I suspect this query would translate to SQLite by simply 
removing the convert(...) clauses.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

In the case shown, for example,

convert(char(3), NULL) CDRefIndustry,

It creates a char(3) column, sets it to null, and names it
CDRefIndustry.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 5:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Equivalent syntax?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> What's the equivalent sqlite syntax for sybase convert function? 

Can you describe what the convert function in sybase does?  That
might help us to find the equivalent function in SQLite for you.
--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Equivalent syntax?

[Anderson, James H \(IT\)]

What's the equivalent sqlite syntax for sybase convert function? For
example,

select distinct
date, CDId, CDName, CDTicket,
tradeId, tapsAccount, CDBook, coalesce(CDFid,'') CDFid,
CDStatus, CDTradeDate, CDExpDate, CDNotional,
CDCurr, CDSellBuy, CDType, CDExerType,
CDEntity, CDCusip, CDSetlType, CDCredInit,
CDSingleEntry, CDMaterialType, CDEffDate, CDPremFreq,
CDPaymentType, CDUpfrontSetl, CDPublicInfo, CDCollReq,
CDSpreadCurve, CDPremium, CDOptType, CDAccrue,
CDRefPrice, CDPremiumAmnt, CDLastCoupLength, CDWhoDelivers,
CDCollateralText, CDFactorReason, CDDefStartProt, CDDefEndProt,
CDDefProtType,
convert(char(80), NULL) CDComment,
convert(varchar(100), NULL) CDEvent,
convert(char(11), NULL) CDCurveType,
convert(char(11), NULL) CrvShName,
convert(varchar(90), NULL) CDRefEntity,
convert(char(3), NULL) CDRefIndustry,
convert(char(3), NULL) CDRefCountry,
convert(char(4), NULL) CDRefSNP,
convert(char(4),NULL) CDRefMoody,
convert(char(4),NULL) CDRefMSRating,
CDRefId
into TMP_credDerivOrig 
from credDerivOrig_C1

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

[sqlite] Where can I find doc on user defined types?

[Anderson, James H \(IT\)]

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] Another (possibly dumb) question

[Anderson, James H \(IT\)]

Thanks. 

-Original Message-
From: Ralf Junker [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 25, 2007 11:37 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another (possibly dumb) question


>For select, update, insert, or delete is there a way to get the number
>of rows affected?

http://www.sqlite.org/capi3ref.html#sqlite3_changes

Ralf 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Another (possibly dumb) question

[Anderson, James H \(IT\)]

For select, update, insert, or delete is there a way to get the number
of rows affected?

Thanks,
jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] How to conditionally drop a table?

[Anderson, James H \(IT\)]

Sorry, I should have checked the site first. My background is sybase and
I didn't expect anything so simple.

Thanks very much,

jim 

-Original Message-
From: G. Roderick Singleton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 25, 2007 10:49 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to conditionally drop a table?

On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> I need a way to drop a table only if that table exists. How would I do
> that?
> 
> Thanks,

Are you asking if http://sqlite.org/lang_droptable.html works or have
you had problems?
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How to conditionally drop a table?

[Anderson, James H \(IT\)]

I need a way to drop a table only if that table exists. How would I do
that?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] date - SQL extraction from day, month and year

[Anderson, James H \(IT\)]

Thanks! 

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 26, 2006 1:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date - SQL extraction from day, month and year

This might help. 
http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

A quick glance through the date.c file in the Sqlite distribution is all

you needed to understand the extent and rationale behind the Sqlite date

support.  You can also use the code there as a template to create your 
own user functions for more detailed date manipulation.

Anderson, James H (IT) wrote:
> Where are the date support functions documented? 
> 
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, December 26, 2006 12:40 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] date - SQL extraction from day, month and year
> 
> Everybody?  Most Sqlite users know that Sqlite implements a DATE type 
> and has date support functions.
> 
> Karsten Koschinski wrote:
> 
>>Hey,
>>
>>I have a database with a field where dates are stored! As everybody
> 
> knows SQLite has no special date support such as a special datatype
for
> date or something else. Now I want to select only those records, where
> the year ist e.g. 2005. How can I do this?
> 
>>"SELECT * FROM database WHERE (EXTRACT DAY FROM date) = 2005" does not
> 
> work.
> 
>>The database was created with SQLite Analyzer 3.0.8. The datatyp of
> 
> the field date ist Text.
> 
>>Thanks
>>
>>Karsten
> 
> 
> 
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
> 
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] date - SQL extraction from day, month and year

[Anderson, James H \(IT\)]

Where are the date support functions documented? 

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 26, 2006 12:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date - SQL extraction from day, month and year

Everybody?  Most Sqlite users know that Sqlite implements a DATE type 
and has date support functions.

Karsten Koschinski wrote:
> Hey,
> 
> I have a database with a field where dates are stored! As everybody
knows SQLite has no special date support such as a special datatype for
date or something else. Now I want to select only those records, where
the year ist e.g. 2005. How can I do this?
> 
> "SELECT * FROM database WHERE (EXTRACT DAY FROM date) = 2005" does not
work.
> 
> The database was created with SQLite Analyzer 3.0.8. The datatyp of
the field date ist Text.
> 
> Thanks
> 
> Karsten



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

Thanks! 

-Original Message-
From: jphillip [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 19, 2006 4:24 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Is there a method for doing bulk insertion?


issue .help
look for .separator 

example for a csv file with colon(:) separators
issue .separator ':'

use an editor to change the existing separator character(s) to the 
character you want to use.



On Tue, 19 Dec 2006, Anderson, James H (IT) wrote:

> So I can assume that there's no way to use a delimiter other than a
> comma to import a CSV file? 
> 
> -Original Message-
> From: jphillip [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, December 19, 2006 3:47 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Is there a method for doing bulk insertion?
> 
> 
> 
> .help pretty well sums it up.
> 
> On Tue, 19 Dec 2006, Anderson, James H (IT) wrote:
> 
> > How do I find doc on .import?
> > 
> > Is there a way to specify the delimiter for the CSV file?
> > 
> > Thanks,
> > 
> > jim 
> > 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Monday, December 18, 2006 9:12 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Is there a method for doing bulk insertion?
> > 
> > "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> > > or do I have to creation a gazillion insert statements?
> > > 
> > 
> > The sqlite3 command-line shell has a ".import" command which
> > can be used to read CSV data.  But the way this works internally
> > is that the command-line shell constructs an INSERT statement,
> > parses each line of the CSV file and binds the values to that
> > INSERT statement, then runs the INSERT statement for each line.
> > So at the end of the day, a bunch of INSERT statements are still
> > getting evaluated - you just don't see them.
> > 
> > On my workstation, an INSERT statement can be parsed, compiled,
> > and evaluated in 25-40 microseconds.  That's about 3 rows
> > per second.  How much performance do you need?
> > 
> > --
> > D. Richard Hipp  <[EMAIL PROTECTED]>
> > 
> > 
> >
>

> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
>

> > -
> > 
> > 
> > NOTICE: If received in error, please destroy and notify sender.
Sender
> does not intend to waive confidentiality or privilege. Use of this
email
> is prohibited when received in error.
> > 
> >
>

> -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
>

> -
> > 
> > 
> 
> You have to be BRAVE to grow OLD.
> There are no old CARELESS pilots or electricians.
> 
> 
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
> 
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 
> 

You have to be BRAVE to grow OLD.
There are no old CARELESS pilots or electricians.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

Thanks! 

-Original Message-
From: Jeff Godfrey [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 19, 2006 4:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a method for doing bulk insertion?

Take a look at the ".separator" command.  It seems to be what you 
need...

Jeff


- Original Message - 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, December 19, 2006 2:52 PM
Subject: RE: [sqlite] Is there a method for doing bulk insertion?


So I can assume that there's no way to use a delimiter other than a
comma to import a CSV file?



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

So I can assume that there's no way to use a delimiter other than a
comma to import a CSV file? 

-Original Message-
From: jphillip [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 19, 2006 3:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Is there a method for doing bulk insertion?



.help pretty well sums it up.

On Tue, 19 Dec 2006, Anderson, James H (IT) wrote:

> How do I find doc on .import?
> 
> Is there a way to specify the delimiter for the CSV file?
> 
> Thanks,
> 
> jim 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 18, 2006 9:12 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a method for doing bulk insertion?
> 
> "Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> > or do I have to creation a gazillion insert statements?
> > 
> 
> The sqlite3 command-line shell has a ".import" command which
> can be used to read CSV data.  But the way this works internally
> is that the command-line shell constructs an INSERT statement,
> parses each line of the CSV file and binds the values to that
> INSERT statement, then runs the INSERT statement for each line.
> So at the end of the day, a bunch of INSERT statements are still
> getting evaluated - you just don't see them.
> 
> On my workstation, an INSERT statement can be parsed, compiled,
> and evaluated in 25-40 microseconds.  That's about 3 rows
> per second.  How much performance do you need?
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
> 
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 
> 

You have to be BRAVE to grow OLD.
There are no old CARELESS pilots or electricians.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

How do I find doc on .import?

Is there a way to specify the delimiter for the CSV file?

Thanks,

jim 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 9:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a method for doing bulk insertion?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> or do I have to creation a gazillion insert statements?
> 

The sqlite3 command-line shell has a ".import" command which
can be used to read CSV data.  But the way this works internally
is that the command-line shell constructs an INSERT statement,
parses each line of the CSV file and binds the values to that
INSERT statement, then runs the INSERT statement for each line.
So at the end of the day, a bunch of INSERT statements are still
getting evaluated - you just don't see them.

On my workstation, an INSERT statement can be parsed, compiled,
and evaluated in 25-40 microseconds.  That's about 3 rows
per second.  How much performance do you need?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

Thanks. 

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 8:32 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a method for doing bulk insertion?

On 12/18/06, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> I was hoping there was the equivalent of Sybase's BCP program. I was
> also hoping something programmatic was available, i.e., not something
> from the command shell. Maybe a little background would help.
>
> I'm planning on using the perl package DBD::SQLite. My department is a
> big sybase user but because of the nature of our workload, we
experience
> a lot of contention in both the transaction log and tempdb (the
database
> that houses temporary tables). I'm investigating the feasibility of
> transferring data into SQLite, doing all the data manipulations there,
> and then transferring it back to the appropriate sybase tables. I
> suspect this could be a big win for a number of our applications.
>
> But if it can be avoided, I don't want to do a CSV conversion, nor do
I
> want to shell out of the code to invoke this.

I created a c++ version for my own use. The source code is downloadable
if that's of any help to you. See my sig line for the address.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

I was hoping there was the equivalent of Sybase's BCP program. I was
also hoping something programmatic was available, i.e., not something
from the command shell. Maybe a little background would help.

I'm planning on using the perl package DBD::SQLite. My department is a
big sybase user but because of the nature of our workload, we experience
a lot of contention in both the transaction log and tempdb (the database
that houses temporary tables). I'm investigating the feasibility of
transferring data into SQLite, doing all the data manipulations there,
and then transferring it back to the appropriate sybase tables. I
suspect this could be a big win for a number of our applications.

But if it can be avoided, I don't want to do a CSV conversion, nor do I
want to shell out of the code to invoke this.

jim

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 9:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a method for doing bulk insertion?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> or do I have to creation a gazillion insert statements?
> 

The sqlite3 command-line shell has a ".import" command which
can be used to read CSV data.  But the way this works internally
is that the command-line shell constructs an INSERT statement,
parses each line of the CSV file and binds the values to that
INSERT statement, then runs the INSERT statement for each line.
So at the end of the day, a bunch of INSERT statements are still
getting evaluated - you just don't see them.

On my workstation, an INSERT statement can be parsed, compiled,
and evaluated in 25-40 microseconds.  That's about 3 rows
per second.  How much performance do you need?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

...or do I have to creation a gazillion insert statements?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.