Re: [sqlite] OR, IN: which is faster?

[Peter van Dijk]

On Feb 1, 2007, at 8:19 AM, Ion Silvestru wrote:


If we have a query where we compare a column to a set of values, then
which is faster: OR or IN?
Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
IN: (mycol IN "a", "b", "c" ...)


IN is much faster - OR disables any use of indexes for column mycol.

Cheers, Peter.

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

[sqlite] OR, IN: which is faster?

[Ion Silvestru]

If we have a query where we compare a column to a set of values, then
which is faster: OR or IN?
Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
IN: (mycol IN "a", "b", "c" ...)

Thanks.


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

[sqlite] How To Use ATTACH?

[Roger Miskowicz]

I know how to use ATTACH with sqlite3.exe but I am having problems using 
it in C++.  I am using a wrapper and what I am trying to do is 
illustrated by:


CppSQLite3DB db;

db.open("Stocks.db");

db.execDML("ATTACH   'Options.db' AS OPT;");

 sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
 sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
 sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";


  db.execDML(sSQL.c_str());

but this gives me an error saying table Options (in database file 
Options.db) is not available.


How do I get ATTACH to work in C++?

Thanks,
 Roger

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

Re: [sqlite] Obtaining randomness on win32

[Dan Kennedy]

On Wed, 2007-01-31 at 20:04 -0600, Jay Sprenkle wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > > Please pick a pseudo algorithm of your choice and give us the ability to
> > > seed it?
> > >
> >
> > You already have this capability.  Use the redefinably I/O feature
> > to insert your own random number seeder in place of the one that
> > comes built in.
> 
> 
> I don't follow that. You mean write a user defined function?

I think you'll need to define SQLITE_ENABLE_REDEF_IO when compiling
and then do something like:

  int myRandomSeedFunction(char *zOutputBuffer){ ... }

  sqlite3_os_switch()->xRandomSeed = myRandomSeedFunction;

The comments in os.h have the details.

Dan.


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

[sqlite] How to lock sqlite DB when access it

[chueng alex1985]

I want to lock a DB when I access it, in case of two processes write/read it
at the same time.

Forgive my poor english.

Thank you.

Re: [sqlite] Obtaining randomness on win32

[drh]

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > > Please pick a pseudo algorithm of your choice and give us the ability to
> > > seed it?
> > >
> >
> > You already have this capability.  Use the redefinably I/O feature
> > to insert your own random number seeder in place of the one that
> > comes built in.
> 
> 
> I don't follow that. You mean write a user defined function?
> 

Compile with -DSQLITE_ENABLE_REDEF_IO=1.  Then there will be a
global variable named

   sqlite3Os.xRandomSeed

which is a pointer to the function that seeds the random number
generator.  Before starting up SQLite for the first time, write
a pointer to whatever seeding function you want to use into the
variable above and that is what SQLite will use to seed its PRNG.

Not sure how to write the seeding function?  There are examples
in os_unix.c, os_win.c, and os_os2.c.

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


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

RE: [sqlite] Limit statement size?

[Joe Wilson]

> I wonder what the reason was to limit the number of table joins to 32.

http://www.sqlite.org/cvstrac/chngview?cn=3622



 

Finding fabulous fares is fun.  
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel 
bargains.
http://farechase.yahoo.com/promo-generic-14795097

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

Re: [sqlite] update inner join syntax

[Roger Miskowicz]

Thanks Richard, it worked perfectly.
 Roger

[EMAIL PROTECTED] wrote:

UPDATE stocks SET bOption=1 WHERE rowid IN
  (SELECT stocks.rowid FROM Stocks, Options
WHERE Stocks.sStockSymbol = Options.sStockSymbol);

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


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




  


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

Re: [sqlite] sqlite core function question

[T]

Hi Jeff,

I've encountered some functions that apparently aren't supported by  
SQLite


So have I, such as replacing occurrences of an inner string.


so I've created my own


I've yet to figure out/try that. Is there a library somewhere of  
prebuilt functions we can add? Is there and mechanism for  
standardizing added functions so that databases used in one system  
are more likely to work on another, because the same functions are  
there?


Also, several of my queries have a basic int() wrapper, that also  
seems to be unsupported.


You could use:

SELECT ROUND(x-0.499);

or better yet, I expected this to work, using the modulo operator %:

SELECT x - x % 1;

But it just returns zero. Further testing indicates that % ignores  
decimal parts of the operands. So this seems to work:


SELECT x % 1e100;

As long as x is less than 1e100.

Tom


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

[sqlite] Re: Preserving sort order of joined subquery

[Steve Krulewitz]

I should also mention that I have been able to achieve what I want
using a temp table with an autoincrement field.  First I do:

insert into temp_table (id) select id from table_a order by x;

Then I use this table in the join and sort on the autoincrement field:

select
 *
from
 temp_table
 join table_b on sub.id = table_b.id
order by
 temp_table.num;

This works, but I would like to avoid this step :)

cheers,
-steve

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

Re: [sqlite] Obtaining randomness on win32

[Jay Sprenkle]

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


> Please pick a pseudo algorithm of your choice and give us the ability to
> seed it?
>

You already have this capability.  Use the redefinably I/O feature
to insert your own random number seeder in place of the one that
comes built in.



I don't follow that. You mean write a user defined function?


--
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

Re: [sqlite] Abuse of the SQLite website

[Jay Sprenkle]

On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP



I created a script that scanned my site logs for such things and it
automates dropping them
into the iptables bit bucket. I'm sure you could come up with something
workable fairly quickly

Re: [sqlite] sqlite core function question

[Jeff Godfrey]

From: <[EMAIL PROTECTED]>


I'm not sure what "int()" does.  Maybe you are looking for round().
Or perhaps cast(expr AS int) will serve your needs.


Sorry, I should have been clearer.  INT just forces the result to be 
an integer.  So, your "cast" example is probably what I need.


Thanks for the quick response.

Jeff 



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

Re: [sqlite] What is the sqlite concatenation operator?

[T]

Hi James,


What is the sqlite concatenation operator?


Ohh, ooh, I actually know this one. Pick me, pick me... ;-)

The concatenation operator (for joining strings) is a double bar: ||

eg:

SELECT 'hi ' || 'there'

gives:

'hi there'

See:
http://www.sqlite.org/lang_expr.html
where it says, in part:

The || operator is "concatenate" - it joins together the two  
strings of its operands.


Hope this helps,
Tom


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

Re: [sqlite] sqlite core function question

[drh]

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> I'm currently converting some Access tables/views to SQLite. 
> I've encountered some functions that apparently aren't 
> supported by SQLite, so I've created my own (a power function
> and an "IIF" function).  Also, several of my queries have a 
> basic int() wrapper, that also seems to be unsupported.  Like
> the others, I've just added my own, but I wonder if I'm missing
> something.  The "expression" page doesn't seem to document
> an "int" function, but I wonder if there is some other equivalent?
> 

I'm not sure what "int()" does.  Maybe you are looking for round().
Or perhaps cast(expr AS int) will serve your needs.

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


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

Re: [sqlite] Creating a view on an ATTACHed database

[drh]

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> 
> Out of curiosity though, is there a reason why ... a view 
> [across multiple attached databases] can't be stored permanently?

When you open a database and first try to use it, SQLite scans
the SQLITE_MASTER table and parses the schema.  Views are stored
in sqlite_master like all other parts of the schema.  IIRC, the
parser would get upset if it tried to parse a view that referenced
a table that did not yet exist.  For that reason, a view cannot
reference a table in a different database.  I might have fixed
the parser at some point so that it will accept a view definition
that includes undefined tables, but the restriction on views not
referencing tables in other databases seems a reasonable
restriction so I left that in.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite3_interrupt from another thread

[drh]

Roger Binns <[EMAIL PROTECTED]> wrote:
> 
> We had an earlier discussion about calling sqlite3_interrupt from
> another thread, which wasn't possible at the time:
> 
> It looks like the code is fixed for 3.3.12 (and possibly earlier).  I
> just wanted to double check that it is now officially safe to call from
> another thread.  The main documentation doesn't mention anything either way.
> 

Go to http://www.sqlite.org/cvstrac/search and do a search for
sqlite3_interrupt in tickets and in check-ins.  The result is

   http://www.sqlite.org/cvstrac/search?s=sqlite3_interrupt=1=1

From this we see that that sqlite3_interrupt() can be called from 
a separate thread as of check-in [3336] on 2006-07-26 associated 
with ticket #1897.  That was first delivered in version 3.3.7.

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


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

[sqlite] sqlite core function question

[Jeff Godfrey]

I'm currently converting some Access tables/views to SQLite.  I've encountered 
some functions that apparently aren't supported by SQLite, so I've created my 
own (a power function and an "IIF" function).  Also, several of my queries have 
a basic int() wrapper, that also seems to be unsupported.  Like the others, 
I've just added my own, but I wonder if I'm missing something.  The 
"expression" page doesn't seem to document an "int" function, but I wonder if 
there is some other equivalent?

Thanks,

Jeff

Re: [sqlite] Creating a view on an ATTACHed database

[Jeff Godfrey]

From: <[EMAIL PROTECTED]>



"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:


So, is it not possible to create a view across a "main" and
an "attached" database?




If I recall, you can create a TEMP VIEW across attached databases.


Thanks for the tip.  Adding TEMP is all it took to get things working.

Out of curiosity though, is there a reason why such a view can't be 
stored permanently?
Obviously, it can't be "used" until the other table(s) are attached, 
but being able

to store it would seem to make things a bit tidier...

I may be way off base - just wondering...

Again - thanks.

Jeff


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

[sqlite] sqlite3_interrupt from another thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

We had an earlier discussion about calling sqlite3_interrupt from
another thread, which wasn't possible at the time:

http://article.gmane.org/gmane.comp.db.sqlite.general/20427

It looks like the code is fixed for 3.3.12 (and possibly earlier).  I
just wanted to double check that it is now officially safe to call from
another thread.  The main documentation doesn't mention anything either way.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwUICmOOfHg372QQRAnJnAKDXpjxOhbXtMak9EVoDjXBPfmG7fACfcwUl
nOWwJ6XrTmXzNYVg/PqFyv8=
=1+/+
-END PGP SIGNATURE-

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

RE: [sqlite] Limit statement size?

[RB Smissaert]

I code in VB and I think I stay out of altering the C source code.

> just do a single REPLACE command with a SELECT on 2 or more subqueries on
> sub-sets of the tables (more efficient).

Will try that one.

> This stands to reason since you're only doing a single lookup per
> sub-table instead of the 6 lookups per sub-table you did with the 
> UPDATE command.

Maybe, but the speed is actually less as my figure of 5 to 6 times faster
was faulty due to me not noticing the error caused by the > 32 table joins.
I would say it is about 2 to 3 times faster. Still worth it, plus a nicer
looking SQL.

I wonder what the reason was to limit the number of table joins to 32.


RBS


-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 01 February 2007 00:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.

Let's do some grepping...

  #define BMS  (sizeof(Bitmask)*8)
 ...
  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask
  */
  if( pTabList->nSrc>BMS ){
sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
return 0;
  }
 ...

You could try changing src/sqliteInt.h:

 -typedef unsigned int Bitmask;
 +typedef u64 Bitmask;

and then recompiling sqlite. If all goes well, you should be able to
join up to 64 tables. Never tried it. It might work, or might not.

Alternatively, you can either perform 2 consecutive REPLACE commands
with half the tables in each update (less efficient), or just do a single 
REPLACE command with a SELECT on 2 or more subqueries on sub-sets of 
the tables (more efficient).

> Actually make that about 5 to 6 times as fast.

This stands to reason since you're only doing a single lookup per
sub-table instead of the 6 lookups per sub-table you did with the 
UPDATE command.



 


Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] update inner join syntax

[drh]

Roger Miskowicz <[EMAIL PROTECTED]> wrote:
> I am trying to do an update inner join, and have tried the following 
> without success.  Would someone please tell me the proper syntax for 
> sqlite3?
> 
> 
> UPDATE Stocks, Stocks-- SQL error: near ",": syntax error
> INNER JOIN Options
> ON Stocks.sStockSymbol = Options.sStockSymbol
> SET Stocks.bOption = 1;
> 
> UPDATE Stocks
> SET bOption = 1
> INNER JOIN Options-- SQL error: near "INNER": syntax error
> ON Stocks.sStockSymbol = Options.sStockSymbol;
> 
> UPDATE Stocks
> SET bOption = 1 
> FROM Stocks, Options-- SQL error: near "FROM": syntax error
> WHERE Stocks.sStockSymbol = Options.sStockSymbol;
> 
> UPDATE Stocks
> SET bOption = 1  -- SQL error: near "FROM": syntax error
> FROM Stocks INNER JOIN Options
> ON Stocks.sStockSymbol = Options.sStockSymbol;
> 


UPDATE stocks SET bOption=1 WHERE rowid IN
  (SELECT stocks.rowid FROM Stocks, Options
WHERE Stocks.sStockSymbol = Options.sStockSymbol);

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


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

RE: [sqlite] Limit statement size?

[Joe Wilson]

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.

Let's do some grepping...

  #define BMS  (sizeof(Bitmask)*8)
 ...
  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask
  */
  if( pTabList->nSrc>BMS ){
sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
return 0;
  }
 ...

You could try changing src/sqliteInt.h:

 -typedef unsigned int Bitmask;
 +typedef u64 Bitmask;

and then recompiling sqlite. If all goes well, you should be able to
join up to 64 tables. Never tried it. It might work, or might not.

Alternatively, you can either perform 2 consecutive REPLACE commands
with half the tables in each update (less efficient), or just do a single 
REPLACE command with a SELECT on 2 or more subqueries on sub-sets of 
the tables (more efficient).

> Actually make that about 5 to 6 times as fast.

This stands to reason since you're only doing a single lookup per
sub-table instead of the 6 lookups per sub-table you did with the 
UPDATE command.



 

Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail

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

Re: [sqlite] Equivalent syntax?

[drh]

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> Is cast documented on the sqlite website? I couldn't find it. 
> 

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

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


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

[sqlite] update inner join syntax

[Roger Miskowicz]

I am trying to do an update inner join, and have tried the following 
without success.  Would someone please tell me the proper syntax for 
sqlite3?



UPDATE Stocks, Stocks-- SQL error: near ",": syntax error
   INNER JOIN Options
   ON Stocks.sStockSymbol = Options.sStockSymbol
   SET Stocks.bOption = 1;

UPDATE Stocks
   SET bOption = 1
   INNER JOIN Options-- SQL error: near "INNER": syntax error
   ON Stocks.sStockSymbol = Options.sStockSymbol;

UPDATE Stocks
   SET bOption = 1 
   FROM Stocks, Options-- SQL error: near "FROM": syntax error

   WHERE Stocks.sStockSymbol = Options.sStockSymbol;

UPDATE Stocks
   SET bOption = 1  -- SQL error: near "FROM": syntax error
   FROM Stocks INNER JOIN Options
   ON Stocks.sStockSymbol = Options.sStockSymbol;

Thanks,
 Roger



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

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

Re: [sqlite] Equivalent syntax?

[Kees Nuyt]

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

Re: [sqlite] Equivalent syntax?

[Kees Nuyt]

On Wed, 31 Jan 2007 18:31:20 -0500, you wrote:

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

http://www.sqlite.org/lang_expr.html
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Equivalent syntax?

[P Kishor]

On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

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


..

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

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

Re: [sqlite] Equivalent syntax?

[Nicolas Williams]

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

[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] UNIQUE constraint on column

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> 
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
> 
> Currently, I've implemented it as so (leaving out error handling, etc.):
> 
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK
>get rowid of last insert (sqlite3_last_insert_rowid)
> else if result is SQLITE_CONSTRAINT
>select rowid from Strings where value = 'foo'
> end if
> if rowid
>insert into Objects (string_id) VALUES (rowid)
> end if
> if no error
>commit transaction
> else
>rollback transaction
> end if
> 
> With my dataset, there is about a 10% chance of the string being a
> duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
> constraint violation.
> 
> I've tested "viloating" the internals of the VBDE and pulling the
> rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
> result is returned and it is measurably (5-10%) faster then doing the
> subsequent SELECT.
> 

What you are doing is the most efficient way that I can think of
right off hand.  If "violating" the internals is something you want
to do that's fine - just remember that it is likely to break in
irrepaiable ways in some future point release.  No tears.

Notice that an sqlite3_last_constraint_rowid() function doesn't really
work because an insert might fail due to multiple constraint violations
all on different rows.  In your schema, perhaps, there can be no more
than one constraint violated at a time, but it is easy enough to 
construct a schema where multiple rows can violate a constraint, so the
sqlite3_last_constraint_rowid() idea does not generalize well.

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


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

[sqlite] Preserving sort order of joined subquery

[Steve Krulewitz]

Hello all :)

Imagine the following query:

select
 *
from
 (
   select
 id
   from
 table_a
   order by
 x
 ) sub
 join table_b on sub.id = table_b.id;

I would like the order that is defined by the results subquery "sub"
to be preserved after the result of the join.  What I think I need is
a function that will number the results in the subquery so I can sort
on it in the outer query:

select
 *
from
 (
   select
 id,
 rownum() as rownum
   from
 table_a
   order by
 x
 ) sub
 join table_b on sub.id = table_b.id
order by
 sub.rownum;

I've looked at creating a user defined function or aggregate, but it
seems like the function/aggregate's lifetime is either per-row or per
grouping so I will not be able to count each row of the result.

Any ideas on how I can do this?

cheers,
-steve

-
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?

[drh]

"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]
-

[sqlite] Preserving sort order of joined subquery

[Steve Krulewitz]

Hello all :)

Imagine the following query:

select
 *
from
 (
   select
 id
   from
 table_a
   order by
 x, y, z
 ) sub
 join table_b on sub.id = table_b.id

I would like the order that is defined by the results subquery "sub"
to be preserved after the result of the join.  I do realize I could
move the "order by" clause to the outer query, but lets say the
subquery

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

Re: [sqlite] UNIQUE constraint on column

[Nicolas Williams]

On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote:
> 
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
> 
> Currently, I've implemented it as so (leaving out error handling, etc.):
> 
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK

Sounds like you should want to use INSERT OR IGNORE ... INTO Strings and
then SELECT the rowid of the string for use in INSERTing INTO Object.

Nico
-- 

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

Re: [sqlite] Creating a view on an ATTACHed database

[drh]

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> 
> So, is it not possible to create a view across a "main" and 
> an "attached" database? 
> 

If I recall, you can create a TEMP VIEW across attached databases.
But you can't create a persistent view because such a view would
not make sense to a processes that opened only one database without
opening the other attached databases.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Abuse of the SQLite website

[Robert L Cochran]

Peter James wrote:

On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Thoughts anyone?  Are there less drastic measures that might
be taken to prevent this kind of abuse?



A couple of people here mentioned CAPTCHA's.  This is sort of the 
standard
for preventing automated abuse (intentional or unintentional), and 
there are

lots of example implementations out there, maybe even in whatever "custom
software" you use. :-)

http://en.wikipedia.org/wiki/Captcha

Even if you don't go the CAPTCHA route, just forcing an HTTP POST to 
begin a

download will probably filter out a large proportion of errant traffic or
web bots.  I see your robots.txt file is in order for the downloads area,
but of course that's just a gentleman's agreement...

I would be really careful about using these. A significant number of 
people are visually impaired -- I work directly with one person who is 
(he uses powerful magnifiers to read normal sized 10-12 point text) and 
a second person who sits across from me in the office is blind. I don't 
know how they deal with captcha verification, I will have to ask. Notice 
what the wikipedia text has to say on accessibility issues. A maptcha is 
probably a better solution but if you don't understand you are looking 
at a math problem, you are also blocked out.


Bob Cochran


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

Re: [sqlite] Equivalent syntax?

[P Kishor]

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

Re: [sqlite] Equivalent syntax?

[P Kishor]

On 1/31/07, Anderson, James H (IT) <[EMAIL PROTECTED]> 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



Are you looking for something like ifnull or nullif?





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

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] UNIQUE constraint on column

[Shane Harrelson]

On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

Shane Harrelson wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>
>> The official way to find the conflicting entry is to do a query.
>>
>>   SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>>
>
> Thank you for the reply.  I assumed this was most likely the case, and
> as I said in my original email, I was hoping to avoid having to do a
> seperate select query for the sake of speed... especially since the
> value I needed was so tantalizing close in the VDBE struct.
>
Shane,

Why do you want the rowid of the conflicting row?

I only ask because I suspect there may be a better way to do what you
want to do.

Dennis Cote



I have two tables, an "Objects" table with a foreign key into a second
"Strings" table which is composed of unique values.  It is a many to
one relationship, that is, several Objects may reference the same
String.   When an Object is added, its associated String is added to
the Strings table.   If the String already exists in the Strings
table, I'd like the new Object to reference the existing copy.

Currently, I've implemented it as so (leaving out error handling, etc.):

begin transaction
insert into Strings (value) VALUES ( 'foo')
if string insert result is SQLITE_OK
  get rowid of last insert (sqlite3_last_insert_rowid)
else if result is SQLITE_CONSTRAINT
  select rowid from Strings where value = 'foo'
end if
if rowid
  insert into Objects (string_id) VALUES (rowid)
end if
if no error
  commit transaction
else
  rollback transaction
end if

With my dataset, there is about a 10% chance of the string being a
duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
constraint violation.

I've tested "viloating" the internals of the VBDE and pulling the
rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
result is returned and it is measurably (5-10%) faster then doing the
subsequent SELECT.

Any help or suggestions with how to do this better would be appreciated.

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

Re: [sqlite] Equivalent syntax?

[Dennis Cote]

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

[sqlite] Creating a view on an ATTACHed database

[Jeff Godfrey]

Hi All,

I have an open sqlite3 database (name = dbSerial), to which I've attached a 2nd 
database (name = dbParent).  Now, I'm trying to create a view by joining a view 
from dbSerial with another view from dbParent.  Attempting to create the view 
generates the following error:

Error:  view [tcoverage] cannot reference objects in database dbParent

Specifically, here's my (contrived) view creation code...

  SELECT c.zone, t.zone
  FROM precoverage AS c
  INNER JOIN dbParent.target
  AS t ON (c.zone = t.zone)

So, is it not possible to create a view across a "main" and an "attached" 
database?  If not, what's my best option (copy the necessary data to a single 
(in memory?) database?)...

Thanks for any pointers.

Jeff

Re: [sqlite] Equivalent syntax?

[drh]

"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]
-

[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.

Re: [sqlite] NULL always greater?

[Clark Christensen]

Dennis,

Thanks for the timely reply.  

max(coalesce(col1, 0), coalesce(col2, 0))

is a lot cleaner than the 

max(
case when col1 is null then 0 else col1 end, 
case when col2 is null then 0 else col2 end
   )

solution I came up with.  Though the performance seems to be about the same.

The "coalesce" word hasn't been in my vocabulary, so it has no meaning for me.  
I'll have to read-up :-))

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, January 31, 2007 11:40:02 AM
Subject: Re: [sqlite] NULL always greater?

Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
>
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
>
> FWIW, I'm on 3.3.12 on both Windows and Linux.
>
> Any help is appreciated.
>
>   
Clark,

You must reassign the value used for the comparison if it is null.

The coalesce function will return the first non null value in its 
arguments, and this may be all you need. If you only want the value from 
col2 if col1 is null then simply use

coalesce(col1, col2)

You will only get a null result if both columns are null.

If you really wan the max of the two columns you can use coalesc to 
convert nulls into zeros for the max function.

max(coalesce(col1, 0), coalesce(col2, 0))

This will give a result of zero if both columns are null.

HTH
Dennis Cote

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





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

RE: [sqlite] Limit statement size?

[RB Smissaert]

There is one important problem though that I just discovered.
Just found out that the maximum number of tables in a join is 32!
So, with my base table that is only 31 to add.
This trouble doesn't of course apply to the old UPDATE method.
So, I think after all I need the old way of doing it or what I could do is
see how many tables are to be added and pick the method accordingly.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 17:54
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

Actually make that about 5 to 6 times as fast.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 17:39
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

Can confirm now that the method with INSERT OR REPLACE is faster indeed.
My timings tell me it is about twice as fast and that is worth it as that
could be up to a few seconds.
I now wonder if there is an even faster way avoiding all the GROUP tables
and doing it all in one statement.
Thanks again for the assistance.

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 00:51
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> I can see now what the trouble is if I do the SELECT without the INSERT OR
> REPLACE (shortened):

Can't suggest anything without seeing the schema for all the tables 
involved and any unique indexes related to those tables.

It should work. Perhaps you're not specifying some columns related to 
a unique index on the table being updated. Maybe there's a bug in REPLACE?
Specifically, what does ".schema A3TestB67_J" return?

I suspect this REPLACE technique with its reduced number of database lookups
ought to be much faster than all those subselects you are using now for 
every column, but if you're happy with the timings with the old way and 
it works, there's no point changing it. 

I am curious with it not updating the table, though.

> 
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1,
> GROUP_2 g2,
> GROUP_3 g3,
> GROUP_4 g4
> where
> t1.PATIENT_ID = g2.PID and
> t1.PATIENT_ID = g3.PID and
> t1.PATIENT_ID = g4.PID
> 
> I only get the rows that have entries in all groups. So, this is like an
> inner join and I need a left join.
> Have tried this, but it didn't alter the table, although there was no
error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)
> 
> My old method is actually quite fast and not sure if I can improve on it.
> 
> RBS
> 
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: 30 January 2007 05:53
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > Thanks, that is how I understood it to be.
> > I must be overlooking something simple here.
> 
> Check your SELECT sub-statement within the REPLACE statement to see 
> what rows it returns.
> 
> .header on
> .mode tabs
> 
> create table t1(id primary key, e2_a, e2_b, e3_a, e3_b);
> insert into t1 values(3, 30,31, 23,230);
> insert into t1 values(4, 40,41, 24,240);
> insert into t1 values(5, 50,51, 25,250);
> 
> create table e2(id primary key, a, b);
> insert into e2 values(3, 300, 310);
> insert into e2 values(4, 400, 410);
> insert into e2 values(5, 500, 510);
> 
> create table e3(id primary key, a, b);
> insert into e3 values(3, 23.1, 230.1);
> insert into e3 values(4, 24.1, 240.1);
> insert into e3 values(5, 25.1, 250.1);
> 
> select * from t1 order by id;
> 
> replace into t1(id, e2_a, e2_b, e3_a, e3_b)
>   select t1.id, e2.a, e2.b, e3.a, e3.b
>   from t1, e2, e3 
>   where t1.id = e2.id and t1.id = e3.id;
> 
> select * from 

Re: [sqlite] UNIQUE constraint on column

[Kees Nuyt]

Hi Shane,

On Wed, 31 Jan 2007 09:29:24 -0500, you wrote:

>On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> "Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>> > when i try to insert a row into a table that has a UNIQUE constraint
>> > on a column, and I get the SQLITE_CONSTRAINT result code because i'm
>> > inserting a duplicate value, is there anyway to determine the rowid of
>> > the conflict?
>> >
>> > looking at the internals of the VDBE, i found that the rowid of the
>> > conflicting row is pushed on top of the VDBE stack.
>> >
>> > if i'm willing to violate the interface, i can dereference the rowid
>> > from the internals of the VDBE struct.  i'd rather not do this... is
>> > there a more formal mechanism for getting this value without having do
>> > to do a separate "select" query?  something like
>> > sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
>> >
>>
>> The official way to find the conflicting entry is to do a query.
>>
>>   SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>> --
>> D. Richard Hipp  <[EMAIL PROTECTED]>
>>
>
>Thank you for the reply.  I assumed this was most likely the case, and
>as I said in my original email, I was hoping to avoid having to do a
>seperate select query for the sake of speed... especially since the
>value I needed was so tantalizing close in the VDBE struct.

The SELECT will be quite fast, chances are the required pages of
the unique index will still be in the cache.
In my opinion it is always better to write portable code, so I
would prefer to rely on generic SQL than on yet another
implementation specific API.

Just my 2 cents.
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] UNIQUE constraint on column

[Dennis Cote]

Shane Harrelson wrote:

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


The official way to find the conflicting entry is to do a query.

  SELECT rowid FROM table WHERE uniquecolumn=?;




Thank you for the reply.  I assumed this was most likely the case, and
as I said in my original email, I was hoping to avoid having to do a
seperate select query for the sake of speed... especially since the
value I needed was so tantalizing close in the VDBE struct.


Shane,

Why do you want the rowid of the conflicting row?

I only ask because I suspect there may be a better way to do what you 
want to do.


Dennis Cote

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

Re: [sqlite] NULL always greater?

[Dennis Cote]

Clark Christensen wrote:

I've read through numerous discussions here about comparing values with null, 
and how SQLite functions work with null values, and I thought I understood.

Now it seems appropriate to use the max(col1, col2) function to find the latest 
of two dates (integer Unix times), and some rows will contain null in one 
column or the other.  But, max() always returns null when one of its args is 
null.  That just seems backwards :-))

FWIW, I'm on 3.3.12 on both Windows and Linux.

Any help is appreciated.

  

Clark,

You must reassign the value used for the comparison if it is null.

The coalesce function will return the first non null value in its 
arguments, and this may be all you need. If you only want the value from 
col2 if col1 is null then simply use


   coalesce(col1, col2)

You will only get a null result if both columns are null.

If you really wan the max of the two columns you can use coalesc to 
convert nulls into zeros for the max function.


   max(coalesce(col1, 0), coalesce(col2, 0))

This will give a result of zero if both columns are null.

HTH
Dennis Cote

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

RE: [sqlite] Limit statement size?

[RB Smissaert]

Actually make that about 5 to 6 times as fast.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 17:39
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

Can confirm now that the method with INSERT OR REPLACE is faster indeed.
My timings tell me it is about twice as fast and that is worth it as that
could be up to a few seconds.
I now wonder if there is an even faster way avoiding all the GROUP tables
and doing it all in one statement.
Thanks again for the assistance.

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 00:51
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> I can see now what the trouble is if I do the SELECT without the INSERT OR
> REPLACE (shortened):

Can't suggest anything without seeing the schema for all the tables 
involved and any unique indexes related to those tables.

It should work. Perhaps you're not specifying some columns related to 
a unique index on the table being updated. Maybe there's a bug in REPLACE?
Specifically, what does ".schema A3TestB67_J" return?

I suspect this REPLACE technique with its reduced number of database lookups
ought to be much faster than all those subselects you are using now for 
every column, but if you're happy with the timings with the old way and 
it works, there's no point changing it. 

I am curious with it not updating the table, though.

> 
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1,
> GROUP_2 g2,
> GROUP_3 g3,
> GROUP_4 g4
> where
> t1.PATIENT_ID = g2.PID and
> t1.PATIENT_ID = g3.PID and
> t1.PATIENT_ID = g4.PID
> 
> I only get the rows that have entries in all groups. So, this is like an
> inner join and I need a left join.
> Have tried this, but it didn't alter the table, although there was no
error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)
> 
> My old method is actually quite fast and not sure if I can improve on it.
> 
> RBS
> 
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: 30 January 2007 05:53
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > Thanks, that is how I understood it to be.
> > I must be overlooking something simple here.
> 
> Check your SELECT sub-statement within the REPLACE statement to see 
> what rows it returns.
> 
> .header on
> .mode tabs
> 
> create table t1(id primary key, e2_a, e2_b, e3_a, e3_b);
> insert into t1 values(3, 30,31, 23,230);
> insert into t1 values(4, 40,41, 24,240);
> insert into t1 values(5, 50,51, 25,250);
> 
> create table e2(id primary key, a, b);
> insert into e2 values(3, 300, 310);
> insert into e2 values(4, 400, 410);
> insert into e2 values(5, 500, 510);
> 
> create table e3(id primary key, a, b);
> insert into e3 values(3, 23.1, 230.1);
> insert into e3 values(4, 24.1, 240.1);
> insert into e3 values(5, 25.1, 250.1);
> 
> select * from t1 order by id;
> 
> replace into t1(id, e2_a, e2_b, e3_a, e3_b)
>   select t1.id, e2.a, e2.b, e3.a, e3.b
>   from t1, e2, e3 
>   where t1.id = e2.id and t1.id = e3.id;
> 
> select * from t1 order by id;
> 
> id  e2_ae2_be3_ae3_b
> 3   30  31  23  230
> 4   40  41  24  240
> 5   50  51  25  250
> 
> id  e2_ae2_be3_ae3_b
> 3   300 310 23.1230.1
> 4   400 410 24.1240.1
> 5   500 510 25.1250.1
> 
> 
> > 
> > RBS
> > 
> > -Original Message-
> > From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> > Sent: 29 January 2007 23:52
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Limit statement size?
> > 
> > RB 

RE: [sqlite] Limit statement size?

[RB Smissaert]

Can confirm now that the method with INSERT OR REPLACE is faster indeed.
My timings tell me it is about twice as fast and that is worth it as that
could be up to a few seconds.
I now wonder if there is an even faster way avoiding all the GROUP tables
and doing it all in one statement.
Thanks again for the assistance.

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 00:51
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> I can see now what the trouble is if I do the SELECT without the INSERT OR
> REPLACE (shortened):

Can't suggest anything without seeing the schema for all the tables 
involved and any unique indexes related to those tables.

It should work. Perhaps you're not specifying some columns related to 
a unique index on the table being updated. Maybe there's a bug in REPLACE?
Specifically, what does ".schema A3TestB67_J" return?

I suspect this REPLACE technique with its reduced number of database lookups
ought to be much faster than all those subselects you are using now for 
every column, but if you're happy with the timings with the old way and 
it works, there's no point changing it. 

I am curious with it not updating the table, though.

> 
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1,
> GROUP_2 g2,
> GROUP_3 g3,
> GROUP_4 g4
> where
> t1.PATIENT_ID = g2.PID and
> t1.PATIENT_ID = g3.PID and
> t1.PATIENT_ID = g4.PID
> 
> I only get the rows that have entries in all groups. So, this is like an
> inner join and I need a left join.
> Have tried this, but it didn't alter the table, although there was no
error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)
> 
> My old method is actually quite fast and not sure if I can improve on it.
> 
> RBS
> 
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: 30 January 2007 05:53
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > Thanks, that is how I understood it to be.
> > I must be overlooking something simple here.
> 
> Check your SELECT sub-statement within the REPLACE statement to see 
> what rows it returns.
> 
> .header on
> .mode tabs
> 
> create table t1(id primary key, e2_a, e2_b, e3_a, e3_b);
> insert into t1 values(3, 30,31, 23,230);
> insert into t1 values(4, 40,41, 24,240);
> insert into t1 values(5, 50,51, 25,250);
> 
> create table e2(id primary key, a, b);
> insert into e2 values(3, 300, 310);
> insert into e2 values(4, 400, 410);
> insert into e2 values(5, 500, 510);
> 
> create table e3(id primary key, a, b);
> insert into e3 values(3, 23.1, 230.1);
> insert into e3 values(4, 24.1, 240.1);
> insert into e3 values(5, 25.1, 250.1);
> 
> select * from t1 order by id;
> 
> replace into t1(id, e2_a, e2_b, e3_a, e3_b)
>   select t1.id, e2.a, e2.b, e3.a, e3.b
>   from t1, e2, e3 
>   where t1.id = e2.id and t1.id = e3.id;
> 
> select * from t1 order by id;
> 
> id  e2_ae2_be3_ae3_b
> 3   30  31  23  230
> 4   40  41  24  240
> 5   50  51  25  250
> 
> id  e2_ae2_be3_ae3_b
> 3   300 310 23.1230.1
> 4   400 410 24.1240.1
> 5   500 510 25.1250.1
> 
> 
> > 
> > RBS
> > 
> > -Original Message-
> > From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> > Sent: 29 January 2007 23:52
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Limit statement size?
> > 
> > RB Smissaert wrote:
> > > Had a go at this, but sofar I haven't been able yet to get it to work.
> > > I get no error, but A3Test115_J remains just at it is.
> > > I couldn't find much information about INSERT OR REPLACE in the 

Re: [sqlite] Does SQLite support user-defined data-types ?

[John Stanton]

You need to revise your Java interface or maybe find another.  What are 
you using?


Jerome CORRENOZ wrote:
Now, I can use user-dfined types by declaring them with SQL create 
tables. Fine !


But when I try to use the following code to get an object, I get a 
ClassCastException:

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery(
  "SELECT * FROM EMP");
  while (rset.next()) {
  Dept dept = (Dept)rset.getObject("DEPT");
  }

Is it normal or is it due to the fact that SQLite doesn't support SQL3 
commands, mainly the getTypeMap() command that maps a Java class with a 
SQL type ?

Notice that my Java class implements SQLData but it seems having no effect.

Thanks for your answer,
Jerome

John Stanton wrote:

Sqlite is flexible in typing.  You can create user defined types just 
by declaring them in your SQL which creates tables.  Thereafter the 
API will return the declared type and the actual type so that you can 
process it appropriately.


Jerome CORRENOZ wrote:


Hi,

I'm starting with SQLite and I would like to know if it is possible 
to create user-defined data-types through the following SQL command: 
create type MyType ... ?


In fact, I need it to map a database with an object-oriented model, 
so that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









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

Re[2]: [sqlite] adding years,months, days with decimals using datetime function

[ivailo91]

I faced the same problem recently (before I joined this newsgroup). I backed 
off from SQL to C++ level,
which was very uncomfortable. It would be very handy if you implement the same 
decimal-point parsing
for years too ;)

Best Regards,
Ivailo Karamanolev

On Wednesday, January 31, 2007, 6:04:14 PM, [EMAIL 
PROTECTED] wrote:
> "info" <[EMAIL PROTECTED]> wrote:
>> Hi,
>> 
>> If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns
>> 2000-02-16 00:00:00. This means that it added one month plus half a month.
>> Makes sense.
>> 
>> If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02
>> 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours.
>> 
>> But with the expression datetime('2000-01-01','1.5 years') the result is
>> 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra
>> 6 months.
>> 
>> Can anyone explain why using decimals works for months and days and not for
>> years?
>> 

> Because nobody has ever written the code to do that.  :-)
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>


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


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

RE: [sqlite] adding years,months, days with decimals using datetime function

[info]

So, it is not a bug but an unadvertised feature. :-)

What will happen most likely? That working with decimals for years will also
be added in the future, or that working with decimals for months and days
will be removed?

Rick van der Lans

-Oorspronkelijk bericht-
Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Verzonden: woensdag 31 januari 2007 17:04
Aan: sqlite-users@sqlite.org
Onderwerp: Re: [sqlite] adding years,months, days with decimals using
datetime function

"info" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> If I use the expression datetime('2000-01-01','1.5 months'), SQLite
returns
> 2000-02-16 00:00:00. This means that it added one month plus half a month.
> Makes sense.
> 
> If I use the expression datetime('2000-01-01','1.5 days'), I get
2000-01-02
> 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12
hours.
> 
> But with the expression datetime('2000-01-01','1.5 years') the result is
> 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an
extra
> 6 months.
> 
> Can anyone explain why using decimals works for months and days and not
for
> years?
> 

Because nobody has ever written the code to do that.  :-)
--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





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

Re: [sqlite] Can't build 3.3.12 on my Unix

[GBanschbach]

<[EMAIL PROTECTED]> wrote on 01/30/2007 03:24:51 PM:

> [EMAIL PROTECTED] wrote:
> >
> > I probably should ask my question this way: What is the *safe* method
for
> > 32 bit machines to build Sqlite 3.3x?
> >
>
> "Safe" is relative.  I know of a few companies that use the technique
> I outlined earlier.  But I have never personally tested a 32-bit build
> so I cannot say what problems might come up.  I would expect to find
> problems if you try to insert an integer that cannot be represented in
> only 32-bits, for example.  But as long as you stick to smaller
> integers I'm guessing everything will likely work ok.
>
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
>
-

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

>

Good News!  I dropped the xlong.h, and put the defined the x64 stuff as
long and unsigned long,
and it compiled 1 2 3:

>From  sqlite3.h:
#ifdef SQLITE_INT64_TYPE
  typedef SQLITE_INT64_TYPE sqlite_int64;
  typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;
#elif defined(_MSC_VER) || defined(__BORLANDC__)
  typedef __int64 sqlite_int64;
  typedef unsigned __int64 sqlite_uint64;
#else
  typedef long  sqlite_int64;
  typedef unsigned long sqlite_uint64;
  typedef long  i64;
  typedef unsigned long u64;
#endif

I will begin testing to see what that does to the capabilities of the
databaseBut maybe later I will get GCC version 4 built, and i can try
again with the original source.
Thanks again!


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

Re: [sqlite] adding years,months, days with decimals using datetime function

[drh]

"info" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns
> 2000-02-16 00:00:00. This means that it added one month plus half a month.
> Makes sense.
> 
> If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02
> 12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours.
> 
> But with the expression datetime('2000-01-01','1.5 years') the result is
> 2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra
> 6 months.
> 
> Can anyone explain why using decimals works for months and days and not for
> years?
> 

Because nobody has ever written the code to do that.  :-)
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] SQLite read-only

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there a possibility to open a SQLite database read-only so that
> database file will not be locked? This request is for updating
> reasons, to overwrite the database file with a newer version, while
> users have opened the database only for read.
> 


No, not really.  If a database were opened read-only without
a read-lock and it was trying to read while another database
was writing, then the reader might see partially updated information,
which is incorrect.

That said, you can play games with the user-defined I/O layer to
make it do anything you want.  But on your own head be it.

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


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

Re: [sqlite] Does julianday work according to the manual?

[drh]

"info" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that the
> statement:
> 
> Select julianday('-4714-11-24 12:00:00');
> 
> Should return 0.0. But it doesn't, it returns -365.0
> 
> Does this mean, that the manual should say "since noon in Greenwich on
> November 24, 4713 B.C.? Or am I missing something?
> 

The year -4713 and 4714 b.c. are the same year.  When using
historical notation (eg: "b.c.") you skip the zero year and go
straight from 1 a.d. to 1 b.c.  When using astronomical notation
(eg: -4713) there is a 0 year, which corresponds to 1 b.c.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] NULL always greater?

[Clark Christensen]

Aah, perfect.  Thanks for the pointer.

 -Clark

- Original Message 
From: Dan Kennedy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, January 30, 2007 10:49:34 PM
Subject: Re: [sqlite] NULL always greater?

The basic rule is that the result of any comparison involving a
NULL value (including comparing against another NULL) is NULL.

See the following for the details:

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

Dan.


On Tue, 2007-01-30 at 16:41 -0800, Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
> 
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
> 
> FWIW, I'm on 3.3.12 on both Windows and Linux.
> 
> Any help is appreciated.
> 
> Thanks!
> 
>  -Clark
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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





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

RE: [sqlite] Limit statement size?

[RB Smissaert]

> Your INSERT OR REPLACE statement is in error.

Yes, you are right. In the end it all came down to a simple mistake on my
side. Sorry if I have wasted anybody's time.
Got this all working now and will now see if it is faster than my old
method.

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2007 03:49
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

Your INSERT OR REPLACE statement is in error. 
You have fewer columns in your SELECT clause than are specified 
in your INSERT column name list. You should have seen an error like 
this in SQLite version 3.3.12:

  SQL error: X values for Y columns

Assuming PATIENT_ID is the sole unique key for A3TestB67_J and
your SQL column counts match, the REPLACE should work.

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> Have tried this, but it didn't alter the table, although there was no
error:
> 
> INSERT OR REPLACE INTO 
> A3TestB67_J(PATIENT_ID,
> ENTRY_ID_E1,
> READ_CODE_E1,
> TERM_TEXT_E1,
> START_DATE_E1,
> ADDED_DATE_E1,
> NUMERIC_VALUE_E1,
> ENTRY_ID_E2,
> READ_CODE_E2,
> TERM_TEXT_E2,
> START_DATE_E2,
> ADDED_DATE_E2,
> NUMERIC_VALUE_E2,
> ENTRY_ID_E3,
> READ_CODE_E3,
> TERM_TEXT_E3,
> START_DATE_E3,
> ADDED_DATE_E3,
> NUMERIC_VALUE_E3,
> ENTRY_ID_E4,
> READ_CODE_E4,
> TERM_TEXT_E4,
> START_DATE_E4,
> ADDED_DATE_E4,
> NUMERIC_VALUE_E4)
> select
> t1.PATIENT_ID,
> g2.ENTRY_ID,
> g2.READ_CODE,
> g2.TERM_TEXT,
> g2.START_DATE,
> g2.ADDED_DATE,
> g2.NUMERIC_VALUE,
> g3.ENTRY_ID,
> g3.READ_CODE,
> g3.TERM_TEXT,
> g3.START_DATE,
> g3.ADDED_DATE,
> g3.NUMERIC_VALUE,
> g4.ENTRY_ID,
> g4.READ_CODE,
> g4.TERM_TEXT,
> g4.START_DATE,
> g4.ADDED_DATE,
> g4.NUMERIC_VALUE
> from
> A3TestB67_J t1
> left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID)
> left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID)
> left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID)



 


No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] Does julianday work according to the manual?

[Doug Currie]

On Wednesday, January 31, 2007 Rick van der Lans wrote:

> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that the
> statement:

> Select julianday('-4714-11-24 12:00:00');

> Should return 0.0. But it doesn't, it returns -365.0

> Does this mean, that the manual should say "since noon in Greenwich on
> November 24, 4713 B.C.? Or am I missing something?


sqlite> Select julianday('-0001-11-24 12:00:00');
1721022.0
sqlite> Select julianday('-11-24 12:00:00');
1721388.0
sqlite> Select julianday('0001-11-24 12:00:00');
1721753.0

There is no year 0. The calendar goes from -1 BCE to 1 CE. -11-24
is 1 BCE. So, if you want November 24, 4714 B.C. you need to say

sqlite> Select julianday('-4713-11-24 12:00:00');
0.0

e

-- 
Doug Currie
Londonderry, NH, USA


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

[sqlite] adding years,months, days with decimals using datetime function

[info]

Hi,

If I use the expression datetime('2000-01-01','1.5 months'), SQLite returns
2000-02-16 00:00:00. This means that it added one month plus half a month.
Makes sense.

If I use the expression datetime('2000-01-01','1.5 days'), I get 2000-01-02
12:00:00. Again this makes sense: 1.5 days is equal to 1 day plus 12 hours.

But with the expression datetime('2000-01-01','1.5 years') the result is
2001-01-01 00:00:00. Which means, SQLite added only 1 year and not an extra
6 months.

Can anyone explain why using decimals works for months and days and not for
years?

Rick van der Lans




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

[sqlite] SQLite read-only

[Ion Silvestru]

Hi,

Is there a possibility to open a SQLite database read-only so that
database file will not be locked? This request is for updating
reasons, to overwrite the database file with a newer version, while
users have opened the database only for read.

Thanks in advance.


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

Re: [sqlite] Does julianday work according to the manual?

[Trey Mack]

Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of 
julian days...


http://en.wikipedia.org/wiki/Julian_day



(Footnote from that page..)
^ This equals November 24, 4714 BC in the proleptic Gregorian calendar.


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

Re: [sqlite] Does julianday work according to the manual?

[Michael Schlenker]

info schrieb:

Hi,

The manual states that the function julianday returns the number of days
since noon in Greenwich on November 24, 4714 B.C. That would imply that the
statement:

Select julianday('-4714-11-24 12:00:00');

Should return 0.0. But it doesn't, it returns -365.0

Does this mean, that the manual should say "since noon in Greenwich on
November 24, 4713 B.C.? Or am I missing something?


Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of 
julian days...


http://en.wikipedia.org/wiki/Julian_day

Michael


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

[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] UNIQUE constraint on column

[Shane Harrelson]

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to determine the rowid of
> the conflict?
>
> looking at the internals of the VDBE, i found that the rowid of the
> conflicting row is pushed on top of the VDBE stack.
>
> if i'm willing to violate the interface, i can dereference the rowid
> from the internals of the VDBE struct.  i'd rather not do this... is
> there a more formal mechanism for getting this value without having do
> to do a separate "select" query?  something like
> sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
>

The official way to find the conflicting entry is to do a query.

  SELECT rowid FROM table WHERE uniquecolumn=?;

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



Thank you for the reply.  I assumed this was most likely the case, and
as I said in my original email, I was hoping to avoid having to do a
seperate select query for the sake of speed... especially since the
value I needed was so tantalizing close in the VDBE struct.

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

[sqlite] Does julianday work according to the manual?

[info]

Hi,

The manual states that the function julianday returns the number of days
since noon in Greenwich on November 24, 4714 B.C. That would imply that the
statement:

Select julianday('-4714-11-24 12:00:00');

Should return 0.0. But it doesn't, it returns -365.0

Does this mean, that the manual should say "since noon in Greenwich on
November 24, 4713 B.C.? Or am I missing something?

Rick van der Lans




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

Re: [sqlite] UNIQUE constraint on column

[drh]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
> when i try to insert a row into a table that has a UNIQUE constraint
> on a column, and I get the SQLITE_CONSTRAINT result code because i'm
> inserting a duplicate value, is there anyway to determine the rowid of
> the conflict?
> 
> looking at the internals of the VDBE, i found that the rowid of the
> conflicting row is pushed on top of the VDBE stack.
> 
> if i'm willing to violate the interface, i can dereference the rowid
> from the internals of the VDBE struct.  i'd rather not do this... is
> there a more formal mechanism for getting this value without having do
> to do a separate "select" query?  something like
> sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?
> 

The official way to find the conflicting entry is to do a query.

   SELECT rowid FROM table WHERE uniquecolumn=?;

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


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

Re: [sqlite] Obtaining randomness on win32

[drh]

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > The pseudo-random number generator (PRNG) in SQLite is becoming more
> > important so it seem good to make sure it is well seeded.
> 
> 
> I'd like to be able to seed the random number generator so I can have
> repeatable
> sequences of random numbers (for testing, etc.). The only really random
> numbers
> are generated from hardware, and that's problematic. You can't make it cross
> platform, or even cross processor, without a lot of switches/if
> statements/etc.
> Please pick a pseudo algorithm of your choice and give us the ability to
> seed it?
> 

You already have this capability.  Use the redefinably I/O feature
to insert your own random number seeder in place of the one that
comes built in.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] Re: Newbie Question

[Roger Miskowicz]

Stocks is a table in SOC and  Options is a table in Options (Opt).

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

[sqlite] UNIQUE constraint on column

[Shane Harrelson]

when i try to insert a row into a table that has a UNIQUE constraint
on a column, and I get the SQLITE_CONSTRAINT result code because i'm
inserting a duplicate value, is there anyway to determine the rowid of
the conflict?

looking at the internals of the VDBE, i found that the rowid of the
conflicting row is pushed on top of the VDBE stack.

if i'm willing to violate the interface, i can dereference the rowid
from the internals of the VDBE struct.  i'd rather not do this... is
there a more formal mechanism for getting this value without having do
to do a separate "select" query?  something like
sqlite3_last_insert_rowid() -- ie.  sqlite3_last_conflict_rowid()?

thanks.

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

Re: [sqlite] Obtaining randomness on win32

[Jay Sprenkle]

On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


The pseudo-random number generator (PRNG) in SQLite is becoming more
important so it seem good to make sure it is well seeded.



I'd like to be able to seed the random number generator so I can have
repeatable
sequences of random numbers (for testing, etc.). The only really random
numbers
are generated from hardware, and that's problematic. You can't make it cross
platform, or even cross processor, without a lot of switches/if
statements/etc.
Please pick a pseudo algorithm of your choice and give us the ability to
seed it?



On

Unix this is easy - just open /dev/urandom and read out as much
randomness as you need.  But I do not know how to do this on
win32 and wince.  The current implementation seeds the random
number generator on these platforms by grabbing a copy of the
current system time.  See the sqlite3WinRandomSeed() function
in os_win.c for details.  This is not a very good method for
seeding a PRNG.

Can someone with more knowledge of win32 and wince please suggest
a better method for seeding the PRNG on those platforms?

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
--
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

[sqlite] Re: Hardware problems at www.sqlite.org

[drh]

Recent outages on www.sqlite.org are due to hardware
problems and are unrelated to the recent download abuse.
Our ISP will be swapping out servers soon (which will
result in another outage of an hour to two, of course.)
Thank you for your patience.

If you ever need access to www.sqlite.org and you find
that it is unavailable, remember that there is a mirror
site at

   http://www.hwaci.com/sw/sqlite

The mirror does not support the wiki or trouble tickets
or the timeline, but it does hold all of the static pages
which include most of the documentation and the source
code.
--
D. Richard Hipp  <[EMAIL PROTECTED]>



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

Re: [sqlite] Abuse of the SQLite website

[emilia12]

hello drh, list

 I used to think that only in my country they can forbid
knives and forks because someone can misuse them, but
obviously it is true for other countries too.

Regards,
Emily

-

Спортни залагания!
bg.sportingbet.com


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

Re: [sqlite] Does SQLite support user-defined data-types ?

[Jerome CORRENOZ]

Now, I can use user-dfined types by declaring them with SQL create 
tables. Fine !


But when I try to use the following code to get an object, I get a 
ClassCastException:

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery(
  "SELECT * FROM EMP");
  while (rset.next()) {
  Dept dept = (Dept)rset.getObject("DEPT");
  }

Is it normal or is it due to the fact that SQLite doesn't support SQL3 
commands, mainly the getTypeMap() command that maps a Java class with a 
SQL type ?

Notice that my Java class implements SQLData but it seems having no effect.

Thanks for your answer,
Jerome

John Stanton wrote:

Sqlite is flexible in typing.  You can create user defined types just 
by declaring them in your SQL which creates tables.  Thereafter the 
API will return the declared type and the actual type so that you can 
process it appropriately.


Jerome CORRENOZ wrote:


Hi,

I'm starting with SQLite and I would like to know if it is possible 
to create user-defined data-types through the following SQL command: 
create type MyType ... ?


In fact, I need it to map a database with an object-oriented model, 
so that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
-