RE: [sqlite] Transpose table

2006-12-13 Thread RB Smissaert
The example I gave shows exactly what I need to do.
I have a column of ID numbers with duplicates. I have to make this column
hold only unique ID numbers by moving the values to the first row where that
ID number appears, with that increasing the number of columns.
Hope this makes it a clearer.

RBS

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: 14 December 2006 06:59
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Transpose table

Can you please provide a use case for your example, so we know what 
you're trying to accomplish?  That should help us to help you better. 
-- Darren Duncan

At 12:08 AM + 12/14/06, RB Smissaert wrote:
>I am moving my code away from VBA and transferring it to SQL.
>There is one particular routine where I haven't found a good replacement
for
>and that is to transpose a table from a vertical layout to a horizontal
one,



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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



Re: [sqlite] Transpose table

2006-12-13 Thread Darren Duncan
Can you please provide a use case for your example, so we know what 
you're trying to accomplish?  That should help us to help you better. 
-- Darren Duncan


At 12:08 AM + 12/14/06, RB Smissaert wrote:

I am moving my code away from VBA and transferring it to SQL.
There is one particular routine where I haven't found a good replacement for
and that is to transpose a table from a vertical layout to a horizontal one,



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



Re: [sqlite] faster SELECT time on second run

2006-12-13 Thread Joe Wilson
>From personal experience on Windows and Linux, if your sqlite database 
is smaller than the amount of available RAM, then sequentially 
read()ing the database file in 8K or larger chunks (and ignoring the 
read results) outside of the SQLite API is several times faster than 
first running a dummy query. It's not even important that the initial 
database read() takes place in the same process, since it's an OS thing.

Most modern OSes (and hard drives?) implement very efficient sequential 
read-ahead. Jumping around to various spots in the file tend to increase 
disk-head movement and defeats this look-ahead mechanism. OS file-caching 
is now so finely tuned that SQLite actually runs slightly faster on a 
disk-based file database than a :memory: database. Try it for yourself on 
a cold file-cache. It's completely counter-intuitive.

--- John Stanton <[EMAIL PROTECTED]> wrote:
> If you want to get as much as possible of the Sqlite database into 
> physical memory on the computer, read all of it.  If you just want to 
> preload the cache and VM so that the first user gets faster response 
> execute a dummy query like one you would expect the first user to run.
> 
> Since the VM logic will work on a least recently used algorithm the 
> preload using a read of the file will not be very successful if you have 
> a huge database and not much physical memory.  You will only have the 
> tail of the DB in physical memory.
> 
> My preference would be to execute a dummy query as part of the DB open. 
>   That would leave the cache primed with a working set of pages likely 
> to satisfy the first user query.  After that the cache takes care of 
> itself.  This method not only primes the Sqlite cache but also makes the 
> pages resident in physical memory.  Reading the file alone does not 
> prime the Sqlite cache.


 

Need a quick answer? Get one in minutes from people who know.
Ask your question on www.Answers.yahoo.com

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



Re: [sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion
I don't know how sqlite handles parenthesis in joins. the first query is 
not recognized by the syntax (mpm.MediumID undefined). The second one 
just returns ids with name and value being empty. However I only use 
3.3.4, is there any documentation on this ?


Igor Tandetnik wrote:


Alexandre Guion <[EMAIL PROTECTED]> wrote:


I tried this one of course, and it doesn't work, it could be a bug. It
returns every property for every medium (not just 'myprops')



I believe this should work (untested):

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m LEFT JOIN
(MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) ON mpm.MediumID=m.MediumID

ORDER BY value
;


Or this:

SELECT m.MediumID AS id, IFNULL(x.Name, '') AS name, IFNULL(x.Value,
'') AS value
FROM Media AS m LEFT JOIN
(SELECT mpm.MediumID as MediumID, pn.Text AS Name, pv.Text AS Value
FROM MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) AS x ON x.MediumID=m.MediumID

ORDER BY value
;

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





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



[sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Igor Tandetnik

Alexandre Guion <[EMAIL PROTECTED]> wrote:

I tried this one of course, and it doesn't work, it could be a bug. It
returns every property for every medium (not just 'myprops')


I believe this should work (untested):

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m LEFT JOIN
(MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) ON mpm.MediumID=m.MediumID

ORDER BY value
;


Or this:

SELECT m.MediumID AS id, IFNULL(x.Name, '') AS name, IFNULL(x.Value,
'') AS value
FROM Media AS m LEFT JOIN
(SELECT mpm.MediumID as MediumID, pn.Text AS Name, pv.Text AS Value
FROM MediumPropMap AS mpm
   JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
) AS x ON x.MediumID=m.MediumID

ORDER BY value
;

Igor Tandetnik 



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



Re: [sqlite] faster SELECT time on second run

2006-12-13 Thread Jay Sprenkle

On 12/12/06, John Stanton <[EMAIL PROTECTED]> wrote:

If you want to get as much as possible of the Sqlite database into
physical memory on the computer, read all of it.  If you just want to
preload the cache and VM so that the first user gets faster response
execute a dummy query like one you would expect the first user to run.

Since the VM logic will work on a least recently used algorithm the
preload using a read of the file will not be very successful if you have
a huge database and not much physical memory.  You will only have the
tail of the DB in physical memory.

My preference would be to execute a dummy query as part of the DB open.
  That would leave the cache primed with a working set of pages likely
to satisfy the first user query.  After that the cache takes care of
itself.  This method not only primes the Sqlite cache but also makes the
pages resident in physical memory.  Reading the file alone does not
prime the Sqlite cache.


That sounds like an excellent idea. I did assume it was the operating
system disk cache that was being primed, that may not be correct.

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



[sqlite] Transpose table

2006-12-13 Thread RB Smissaert
I am moving my code away from VBA and transferring it to SQL.
There is one particular routine where I haven't found a good replacement for
and that is to transpose a table from a vertical layout to a horizontal one,
like this example:

ID  Value
-
1   A
1   B
1   A
3   G
3   D
5   A

This should become:

ID  Value1  Value2  Value3

1   A   B   A
3   G   D
5   A

I can't see any way to do this fast and my VB method running on arrays was
very fast.
Does anybody have any suggestions how to handle this?
Thanks for any advice.


RBS



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



Re: [sqlite] Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion
I tried this one of course, and it doesn't work, it could be a bug. It 
returns every property for every medium (not just 'myprops')


Igor Tandetnik wrote:


Alexandre Guion <[EMAIL PROTECTED]> wrote:


SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON mpm.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

WHERE pn.Text='myprop' AND p.UserID=1
GROUP BY id ORDER BY value
;

This doesn't work, as I could see in other threads. It returns only
the media that have the property 'myprop', apparently the where
clause is applied on the global result, but why ?



Because that's how SQL works. What did you expect?


anyway, second attempt :

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN PropName AS pn ON (pn.Text='myprop')
LEFT JOIN Property AS p ON (p.PropNameID=pn.PropNameID AND
mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id
ORDER BY value
;



Do joins in the same order you did in the first query, just move 
conditions from WHERE to ON:


SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id ORDER BY value
;

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





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



[sqlite] Re: Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Igor Tandetnik

Alexandre Guion <[EMAIL PROTECTED]> wrote:

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON mpm.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

WHERE pn.Text='myprop' AND p.UserID=1
GROUP BY id ORDER BY value
;

This doesn't work, as I could see in other threads. It returns only
the media that have the property 'myprop', apparently the where
clause is applied on the global result, but why ?


Because that's how SQL works. What did you expect?


anyway, second attempt :

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN PropName AS pn ON (pn.Text='myprop')
LEFT JOIN Property AS p ON (p.PropNameID=pn.PropNameID AND
mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id
ORDER BY value
;


Do joins in the same order you did in the first query, just move 
conditions from WHERE to ON:


SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text,
'') AS value
FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
LEFT JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id ORDER BY value
;

Igor Tandetnik 



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



[sqlite] Select with left outer join - Am I going mad ? Second edition

2006-12-13 Thread Alexandre Guion

Hey all,
I'm getting crazy on a problem with left join in SQLite. Either I didn't 
understand something, or there's a bug somewhere.

Here's a sample of my DB

CREATE TABLE Media (
MediumId INTEGER PRIMARY KEY,
...
)
CREATE TABLE MediumPropMap (
MediumId INTEGER NOT NULL, (foreign key from Media)
PropId INTEGER NOT NULL, (foreign key from Property)
UNIQUE (MediumId, PropId)
)

CREATE TABLE Property ( 
PropId INTEGER PRIMARY KEY,

PropValueId INTEGER NOT NULL, (foreign key from PropValue)
PropNameId INTEGER NOT NULL, (foreign key from PropName)
UserId INTEGER NOT NULL
)

CREATE TABLE PropValue (
PropValueId PRIMARY KEY,
Text TEXT
)

CREATE TABLE PropName (
PropNameId PRIMARY KEY,
Text TEXT
)

Now what I'm trying to do is to get an SQL query that would return all 
the media sorted by one of their property (or few properties at the same 
time), *taking into account that not all the media have that property*. 
The answer is, of course, to use left joins, and my first attempt was :


SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text, 
'') AS value

FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN Property AS p ON mpm.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

WHERE pn.Text='myprop' AND p.UserID=1
GROUP BY id ORDER BY value
;

This doesn't work, as I could see in other threads. It returns only the 
media that have the property 'myprop', apparently the where clause is 
applied on the global result, but why ?


anyway, second attempt :

SELECT m.MediumID AS id, IFNULL(pn.Text, '') AS name, IFNULL(pv.Text, 
'') AS value

FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
LEFT JOIN PropName AS pn ON (pn.Text='myprop')
LEFT JOIN Property AS p ON (p.PropNameID=pn.PropNameID AND 
mpm.PropID=p.PropID AND p.UserID=1)

LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id
ORDER BY value
;

Not right either, returns now the correct count of media, but none of my 
properties that I'm looking for.


Another attempt, trying to get things simpler :

SELECT mpm.MediumID AS id, IFNULL(pv.Text,'') as value
FROM Media AS mpm
INNER JOIN PropName AS pn ON pn.Text='rating'
LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND 
p.PropNameID=pn.PropNameID AND p.UserID=1)

LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID
GROUP BY id
ORDER BY value
;

Now the result is really far from what I'm looking for. Things are 
getting to get weird here...


I finally found one that was working :

SELECT m.MediumID AS id, IFNULL(pn0.Text, '') AS name, IFNULL(pv.Text, 
'') AS value

FROM Media AS m
LEFT JOIN MediumPropMap AS mpm ON ( mpm.MediumID=m.MediumID AND 
mpm.PropID IN
(SELECT p.PropID FROM Property AS p, PropName AS pn WHERE 
pn.Text='myprop' AND p.PropNameID=pn.PropNameID)

)
LEFT JOIN Property AS p ON mpm0.PropID=p.PropID
LEFT JOIN PropName AS pn ON p.PropNameID=pn.PropNameID
LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY id
ORDER BY  value
;

But this one gets really long when used on a property that has many 
different values in the DB, I believe sqlite is here creating an 
intermediate result table and therefore get things really long to process.


Any other ideas ? And can someone explain why the first attempts weren't 
working ?


Thanks,

- Alex


Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton
If you are using a B-Tree index for a file system why incorporate 
Sqlite, why not just write the file system code?  B-Trees are well 
documented.


Cesar Rodas wrote:
I am developing a File System, and I'd like to use B+ Tree and not lost 
time

and CPU understanding SQL...



On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote:



It is hard to imagine why you would want to use Sqlite B-Tree access.

Kees Nuyt wrote:
> On Wed, 13 Dec 2006 13:02:37 -0400, you wrote:
>
>
>>I mean the SQLite Core API, something like Berkeley DB.
>>I'd like to use SQLite B+tree API.
>
>
> It has quite recently been discussed, you may want to try to
> search the mailing list archives.
> The conclusion was: the B+tree API is usable but it is easy to
> shoot yourself in the foot.
>
> http://www.sqlite.org/arch.html might be a good starting point
> for further research.



- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 










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



Re: [sqlite] Check for empty table

2006-12-13 Thread Dennis Cote

RB Smissaert wrote:

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.

But I am not sure if a table always has the hidden field ROWID.

RBS



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


  

RB,

This was discussed recently in this thread 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg19148.html


I believe Igor's suggestion is optimal for SQLite. It may be faster to 
add a limit clause to the subquery for other database engines.


HTH
Dennis Cote

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



[sqlite] Re: Re: Check for empty table

2006-12-13 Thread Igor Tandetnik

Nicolas Williams <[EMAIL PROTECTED]> wrote:

On Wed, Dec 13, 2006 at 01:33:35PM -0500, Igor Tandetnik wrote:

Nicolas Williams
<[EMAIL PROTECTED]>
wrote:

On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote:
count(*) doesn't read every record in the table.


Does too. Run EXPLAIN and see for yourself.


Ah.  Yes.  It could optimize "select count(*) > 0 from sometable;" as
"select 1 from sometable limit 1;"...


If you are checking for existence, why not say so explicitly?

select exists (select * from sometable);

Igor Tandetnik 



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



Re: [sqlite] Re: File Syste

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 05:03:50PM -0400, Cesar Rodas wrote:
> On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote:
> >
> >It is hard to imagine why you would want to use Sqlite B-Tree access.
> 
> I am developing a File System, and I'd like to use B+ Tree and not lost time
> and CPU understanding SQL...

If SQLite had recursive queries you could save yourself a lot of
development time using SQL instead of writing more code in some other
language to implement the same logic that you would implement in SQL.

Nico
-- 

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



Re: [sqlite] Re: Check for empty table

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 01:33:35PM -0500, Igor Tandetnik wrote:
> Nicolas Williams <[EMAIL PROTECTED]> wrote:
> >On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote:
> >count(*) doesn't read every record in the table.
> 
> Does too. Run EXPLAIN and see for yourself.

Ah.  Yes.  It could optimize "select count(*) > 0 from sometable;" as
"select 1 from sometable limit 1;"...

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



Re: [sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas

I am developing a File System, and I'd like to use B+ Tree and not lost time
and CPU understanding SQL...



On 13/12/06, John Stanton <[EMAIL PROTECTED]> wrote:


It is hard to imagine why you would want to use Sqlite B-Tree access.

Kees Nuyt wrote:
> On Wed, 13 Dec 2006 13:02:37 -0400, you wrote:
>
>
>>I mean the SQLite Core API, something like Berkeley DB.
>>I'd like to use SQLite B+tree API.
>
>
> It has quite recently been discussed, you may want to try to
> search the mailing list archives.
> The conclusion was: the B+tree API is usable but it is easy to
> shoot yourself in the foot.
>
> http://www.sqlite.org/arch.html might be a good starting point
> for further research.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


[sqlite] Reusing SQLiteCommand

2006-12-13 Thread jfbaro


Hi,

I am new to C# and SQLite and I am having problems using Paramaters.Add...

I prepare the "INSERT" statement and inside a loop I set the values for each
parameter and call ExecuteNonQuery();
Something similar to that:

checkCmd.CommandText = "SELECT userId FROM users WHERE
userId = @userId";

IDbDataParameter userIdPar = checkCmd.CreateParameter();

userIdPar.ParameterName = "@userId";

userIdPar.DbType = DbType.String;
checkCmd.Parameters.Add(userIdPar);
   
updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE users SET userName =
@userName, userPin = @userPin WHERE userId = @userId";
IDbDataParameter userIdUp = updateCmd.CreateParameter();
userIdUp.ParameterName = "@userId";
userIdUp.DbType = DbType.Int32;
IDbDataParameter userNameUp = updateCmd.CreateParameter();
userNameUp.ParameterName = "@userName";
userNameUp.DbType = DbType.String;
IDbDataParameter userPinUp = updateCmd.CreateParameter();
userPinUp.ParameterName = "@userPin";
userPinUp.DbType = DbType.String;
updateCmd.Parameters.Add(userIdUp);
updateCmd.Parameters.Add(userNameUp);
updateCmd.Parameters.Add(userPinUp);


insertCmd = conn.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (userId,
userName, userPin) VALUES (@userId, @userName, @userPin)";
IDbDataParameter userIdIn = insertCmd.CreateParameter();
userIdIn.ParameterName = "@userId";
userIdIn.DbType = DbType.Int32;
IDbDataParameter userNameIn = insertCmd.CreateParameter();
userNameIn.ParameterName = "@userName";
userNameIn.DbType = DbType.String;
IDbDataParameter userPinIn = insertCmd.CreateParameter();
userPinIn.ParameterName = "@userPin";
userPinIn.DbType = DbType.String;
insertCmd.Parameters.Add(userIdIn);
insertCmd.Parameters.Add(userNameIn);
insertCmd.Parameters.Add(userPinIn);


 while (objBinaryReaderRx.BaseStream.Position < PacketSize)
{

int userId = objBinaryReaderRx.ReadInt32();
string userName = objBinaryReaderRx.ReadString();
string userPIN = objBinaryReaderRx.ReadString();

userIdPar.Value = userId;   

readerCheck = checkCmd.ExecuteReader();

if (readerCheck.Read())
{
userIdUp.Value = userId;
userNameUp.Value = userName;
userPinUp.Value = userPIN;

updateCmd.ExecuteNonQuery();
}
else
{
userIdIn.Value = userId;
userNameIn.Value = userName;
userPinIn.Value = userPIN;

insertCmd.ExecuteNonQuery();   
}

readerCheck.Close();
}
   

transaction.Commit();

return true;

catch{...}
finally{...}

The first INSERT works fine, but the second time I get: "Operation is not
valid due to the current state of the object."

I am using .NET 1.1, Finisar 1.1 and SQLite 3

Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/Reusing-SQLiteCommand-tf2816640.html#a7861212
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton

It is hard to imagine why you would want to use Sqlite B-Tree access.

Kees Nuyt wrote:

On Wed, 13 Dec 2006 13:02:37 -0400, you wrote:



I mean the SQLite Core API, something like Berkeley DB.
I'd like to use SQLite B+tree API.



It has quite recently been discussed, you may want to try to
search the mailing list archives.
The conclusion was: the B+tree API is usable but it is easy to
shoot yourself in the foot. 


http://www.sqlite.org/arch.html might be a good starting point
for further research.



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



Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Dennis Cote

Michele Santucci wrote:
Following your hint I create a simple project (linked sqlite3.lib and 
included sqlite3.h) where I just do this:


Edit2->Text = AnsiString( sqlite3_libversion() );

I got this at compile time:

[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::nConstraint' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aConstraint' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::nOrderBy' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aOrderBy' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aConstraintUsage' in class without constructors


Pretty strange



Michele,

This is also a bug in the Borland/CodeGear C++ compiler (see 
http://qc.borland.com/wc/qcmain.aspx?d=32959 ). I am trying to get this 
report reopened, but in the meantime you will probably have to change 
the sqlite3.h file to add a conditional around the sqlite3_index_info 
definition, and remove the offending const qualifiers like this:


   #ifdef __BORLANDC__
   struct sqlite3_index_info {
 /* Inputs */
 int nConstraint; /* Number of entries in aConstraint */
 const struct sqlite3_index_constraint {
int iColumn;  /* Column on left-hand side of 
constraint */

unsigned char op; /* Constraint operator */
unsigned char usable; /* True if this constraint is usable */
int iTermOffset;  /* Used internally - xBestIndex 
should ignore */

 } *aConstraint;  /* Table of WHERE clause constraints */
 int nOrderBy;/* Number of terms in the ORDER BY clause */
 const struct sqlite3_index_orderby {
int iColumn;  /* Column number */
unsigned char desc;   /* True for DESC.  False for ASC. */
 } *aOrderBy; /* The ORDER BY clause */

 /* Outputs */
 struct sqlite3_index_constraint_usage {
   int argvIndex;   /* if >0, constraint is part of argv to 
xFilter */
   unsigned char omit;  /* Do not code a test for this 
constraint */

 } *aConstraintUsage;
 int idxNum;/* Number used to identify the index */
 char *idxStr;  /* String, possibly obtained from 
sqlite3_malloc */
 int needToFreeIdxStr;  /* Free idxStr using sqlite3_free() if 
true */

 int orderByConsumed;   /* True if output is already ordered */
 double estimatedCost;  /* Estimated cost of using this index */
   };
   #else
   struct sqlite3_index_info {
 /* Inputs */
 const int nConstraint; /* Number of entries in aConstraint */
 const struct sqlite3_index_constraint {
int iColumn;  /* Column on left-hand side of 
constraint */

unsigned char op; /* Constraint operator */
unsigned char usable; /* True if this constraint is usable */
int iTermOffset;  /* Used internally - xBestIndex 
should ignore */

 } *const aConstraint;  /* Table of WHERE clause constraints */
 const int nOrderBy;/* Number of terms in the ORDER BY 
clause */

 const struct sqlite3_index_orderby {
int iColumn;  /* Column number */
unsigned char desc;   /* True for DESC.  False for ASC. */
 } *const aOrderBy; /* The ORDER BY clause */

 /* Outputs */
 struct sqlite3_index_constraint_usage {
   int argvIndex;   /* if >0, constraint is part of argv to 
xFilter */
   unsigned char omit;  /* Do not code a test for this 
constraint */

 } *const aConstraintUsage;
 int idxNum;/* Number used to identify the index */
 char *idxStr;  /* String, possibly obtained from 
sqlite3_malloc */
 int needToFreeIdxStr;  /* Free idxStr using sqlite3_free() if 
true */

 int orderByConsumed;   /* True if output is already ordered */
 double estimatedCost;  /* Estimated cost of using this index */
   };
   #endif

Be advised, that after I do this I get a similar unresolved external 
error for _sqlite3_libversion. I'm trying to see what might be causing 
this, but the generated sqlite3.lib file looks OK on first inspection.
 
HTH

Dennis Cote

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



Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Kees Nuyt
On Wed, 13 Dec 2006 20:33:29 +0100, you wrote:

>The page where this is documented :
>   http://www.sqlite.org/sqlite.html
>is quite hard to find, the only link i found is on the 
>   http://www.sqlite.org/download.html 
>page, next to the "Precompiled Binaries For Windows"
>sqlite-3_3_8.zip link. Which is very appropriate, but a bit
>modest ;)

Oops, there's another link on 
http://www.sqlite.org/quickstart.html
as well:
"Additional documentation is available here  "
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Re: File Syste

2006-12-13 Thread Kees Nuyt
On Wed, 13 Dec 2006 13:02:37 -0400, you wrote:

> I mean the SQLite Core API, something like Berkeley DB.
> I'd like to use SQLite B+tree API.

It has quite recently been discussed, you may want to try to
search the mailing list archives.
The conclusion was: the B+tree API is usable but it is easy to
shoot yourself in the foot. 

http://www.sqlite.org/arch.html might be a good starting point
for further research.
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Re: File Syste

2006-12-13 Thread John Stanton
The Sqlite API is fully documented and the source code is clearly 
written and commented and reads quite simply as a definition of its 
philosophy and function.


Cesar Rodas wrote:

I mean.. is there a manual that explain how to use SQLite Core in low level
API, like berkeley DB, without SQL.

Thank to all

On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote:



Hello to all



I starting a project http://code.google.com/p/gxdfs/ that is a 
Distributed
File System, following the white paper of GFS(Google File system). If 
some

one want to contribute please send emails with ideas.

I would like to know how sqlite works.. is there a manual that explain
every ".c" file?

--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)









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



[sqlite] Logo usage

2006-12-13 Thread Muhammad Hassan

Can i use Sqlite logo on my companies website technology page
http://badrit.com/page/technology, i want to refer to the technologies we
used?
Please reply.
Thanks.

--
Muhammad Hassan Nasr
www.BadrIT.com

mobile: +2 010 606 8822
e-mail: [EMAIL PROTECTED]


Re: [sqlite] Check for empty table

2006-12-13 Thread Scott Hess

On 12/13/06, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

"Scott Hess" <[EMAIL PROTECTED]> writes:
> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?

COUNT(*) is generally slow.  It actually iterates over every row, so with
large tables, it's very slow.  By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.


Hmm, that's a good point, I was thinking in terms of the empty case.
Indeed, in the past I've used one of:

 SELECT max(rowid) FROM table;
 SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1;

as a proxy for COUNT(*).  Obviously this only works for a table which
hasn't seen deletions (or who's deletions have only been at the end
and you created the table appropriately).  I don't remember why both
versions stick in my mind, maybe I tried both and just don't remember
which was faster.  Also, this returns null if the table is empty, so
you might want:

 SELECT ifnull(max(rowid),0) FROM table;

but, honestly, if you're this worried about speed you should manage
the null test in your C code.

-scott

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



[sqlite] Re: Check for empty table

2006-12-13 Thread Igor Tandetnik

Nicolas Williams <[EMAIL PROTECTED]> wrote:

On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote:
count(*) doesn't read every record in the table.


Does too. Run EXPLAIN and see for yourself.

Igor Tandetnik

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



Re: [sqlite] Check for empty table

2006-12-13 Thread Martin Jenkins

Scott Hess wrote:

What's wrong with "SELECT COUNT(*) FROM table;"?
It scans the whole table. This is OK if the table is small (nearly) 
empty, but if it has a couple of million rowZzzz


Martin

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



Re: [sqlite] Check for empty table

2006-12-13 Thread Nicolas Williams
On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote:
> Nothing wrong, but is it the fastest?

count(*) doesn't read every record in the table.

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



Re: [sqlite] Check for empty table

2006-12-13 Thread Derrell . Lipman
"Scott Hess" <[EMAIL PROTECTED]> writes:

> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?

COUNT(*) is generally slow.  It actually iterates over every row, so with
large tables, it's very slow.  By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.

Derrell

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



RE: [sqlite] Check for empty table

2006-12-13 Thread RB Smissaert
Nothing wrong, but is it the fastest?

RBS

-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: 13 December 2006 17:25
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Check for empty table

On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Trying to find the fastest way to determine if a table has no rows.
>
> I think this will do:
> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
> If a table has rows then the result should be 1.

What's wrong with "SELECT COUNT(*) FROM table;"?

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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



Re: [sqlite] Check for empty table

2006-12-13 Thread Scott Hess

On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.


What's wrong with "SELECT COUNT(*) FROM table;"?

-scott

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



Re: [sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas

I mean the SQLite Core API, something like Berkeley DB. I'd like to use
SQLite B+tree API.

On 13/12/06, Pat Wibbeler <[EMAIL PROTECTED]> wrote:


The public C API is well-documented here:
http://www.sqlite.org/capi3.html

There may be other documentation of the internals, but I'd imagine the
public API will get you pretty far.


Pat

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 13, 2006 6:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Re: File Syste

I mean.. is there a manual that explain how to use SQLite Core in low
level
API, like berkeley DB, without SQL.

Thank to all

On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote:
>
> Hello to all
>
>
>
> I starting a project http://code.google.com/p/gxdfs/ that is a
Distributed
> File System, following the white paper of GFS(Google File system). If
some
> one want to contribute please send emails with ideas.
>
> I would like to know how sqlite works.. is there a manual that explain
> every ".c" file?
>
> --
> Cesar Rodas
> http://www.phpclasses.org/grank (A PHP implementation of PageRank)




--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


RE: [sqlite] Re: File Syste

2006-12-13 Thread Pat Wibbeler
The public C API is well-documented here:
http://www.sqlite.org/capi3.html

There may be other documentation of the internals, but I'd imagine the
public API will get you pretty far.


Pat

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 13, 2006 6:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Re: File Syste

I mean.. is there a manual that explain how to use SQLite Core in low
level
API, like berkeley DB, without SQL.

Thank to all

On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote:
>
> Hello to all
>
>
>
> I starting a project http://code.google.com/p/gxdfs/ that is a
Distributed
> File System, following the white paper of GFS(Google File system). If
some
> one want to contribute please send emails with ideas.
>
> I would like to know how sqlite works.. is there a manual that explain
> every ".c" file?
>
> --
> Cesar Rodas
> http://www.phpclasses.org/grank (A PHP implementation of PageRank)




-- 
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

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



Re: [sqlite] Check for empty table

2006-12-13 Thread Rich Shepard

On Wed, 13 Dec 2006, RB Smissaert wrote:


Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.


RBS

  About a month ago, I sought a better solution than what I had devised.
Here's one of the answers for you:

You could write

select exists (select * from  where ='');

This produces a singleton result (single row, single column). The only cell
of this result contains integer 1 if there are indeed rows with
='' in table component, and 0 if there are none.

I still don't understand why you want to "look before you leap", so to
speak. Why not just run the query

select * from  where ='';

and handle the case where it produces an empty result set?


Rich

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

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



Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Michele Santucci
Maybe it is but it's hard to find the way to generate a suitable sqlite3.lib 
then.


- Original Message - 
From: "Roberto" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, December 12, 2006 1:49 PM
Subject: Re: [sqlite] sqlite3 close() run time errors



On 12/12/06, Michele Santucci <[EMAIL PROTECTED]> wrote:

this's a run-time error not a linker error.


It's a runtime error cos it is linked incorrectly!

Specify the sqlite.def file (which IIRC lists the undecorated aliases
of the sqlite functions) as an option to the command line of implib.

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.16/582 - Release Date: 
11/12/2006 16.32






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



Re: [sqlite] sqlite3 close() run time errors

2006-12-13 Thread Michele Santucci
Following your hint I create a simple project (linked sqlite3.lib and 
included sqlite3.h) where I just do this:


Edit2->Text = AnsiString( sqlite3_libversion() );

I got this at compile time:

[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::nConstraint' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aConstraint' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::nOrderBy' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aOrderBy' in class without constructors
[C++ Error] sqlite3.h(1719): E2232 Constant member 
'sqlite3_index_info::aConstraintUsage' in class without constructors


Pretty strange

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, December 12, 2006 8:48 PM
Subject: Re: [sqlite] sqlite3 close() run time errors



Michele Santucci wrote:
I used borland IMPLIB import tool with -a attribute (this add the leading 
underscore
for cdecl compliance). If I didn't use this flag I got linking error 
about missing references...

this's a run-time error not a linker error.


Michele,

This is a problem I discovered with Borland IMPLIB. The fix I found is 
described under ticket 1291 at 
http://www.sqlite.org/cvstrac/tktview?tn=1291


For some reason implib doesn't generate the correct  symbols when 
converting directly from the dll file.


HTH
Dennis Cote

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.18/585 - Release Date: 
13/12/2006 11.49






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



[sqlite] Check for empty table

2006-12-13 Thread RB Smissaert

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.

But I am not sure if a table always has the hidden field ROWID.

RBS



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



Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Martin Jenkins

[EMAIL PROTECTED] wrote:

Use "sqlite" to read an sqlite version 2 database.  Use "sqlite3"
to read a version 3 database.
Just a thought, but would it be worth detecting attempts to open v2 
databases with sqlite3 and printing "this database appears to be version 
2 and I'm version 3 so I can't open it" rather than "database corrupt"?


Martin


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



Re: [sqlite] sqllite ddb from win to linux

2006-12-13 Thread Rob Coenen

Kees,

thanks this helped me a lot. It turned out the DB was actually in sqlite V2
so I did a

sqlite3 olddatabasefile .dump >transportfile

end then transported the transportfile in a ZIP (binary safe) to the Linux
machine
where I did a
sqlite newdatabasefile  wrote:


On Tue, 12 Dec 2006 19:25:16 +0100, you wrote:

>ok - I was trying to do somthing like this, but I could not find any
sample?

sqlite3 olddatabasefile .dump >transportfile
[transfer transportfile in binary mode]
sqlite3 newdatabasefile On 12/12/06, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> "Rob Coenen" wrote,
>>
>>
>> > hello all,
>> >
>> > I have been using TRAC for Windows and I need to restore the TRAC
wiki
>> on
>> > a
>> > Linux machine.
>> >
>> > The integrated Trac WIKI is stored in a sqlite file, it seems as
sqlite
>> > version 3 (judging by opening the .db file with a HEX editor, the
header
>> > mentions sqlite 3).
>> >
>> > I can read the file on Window with sqlite but I cannot read the same
>> file
>> > when I copy it to my Linux machine. It says that the file is not a
>> > database
>> > or that it is encrypted.
>> >
>> > Any help here?
>> >
>> The first thing that comes to mind is to dump the database to text and
>> then,
>> open a new one in unix and import it.
>>
>> just thinking...
>>
>>
>>
>>
-
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>>
-
>>
>>
--
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




[sqlite] Re: File Syste

2006-12-13 Thread Cesar Rodas

I mean.. is there a manual that explain how to use SQLite Core in low level
API, like berkeley DB, without SQL.

Thank to all

On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote:


Hello to all



I starting a project http://code.google.com/p/gxdfs/ that is a Distributed
File System, following the white paper of GFS(Google File system). If some
one want to contribute please send emails with ideas.

I would like to know how sqlite works.. is there a manual that explain
every ".c" file?

--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)





--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


[sqlite] File Syste

2006-12-13 Thread Cesar Rodas

Hello to all



I starting a project http://code.google.com/p/gxdfs/ that is a Distributed
File System, following the white paper of GFS(Google File system). If some
one want to contribute please send emails with ideas.

I would like to know how sqlite works.. is there a manual that explain every
".c" file?

--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


Re: [sqlite] Usage of special characters in insert query

2006-12-13 Thread Roberto

Escape you input strings: insert into foo values ('I haven''t done that');

On 13/12/06, Kirrthana M <[EMAIL PROTECTED]> wrote:

Hi,

In my query for inserting values into the database i have a statement as
given below
insert into table_name values('I haven't done that');

table is created with this option
create table table_name(str varchar(20));

when the insert statement is executed it gives the error in haven't,It is
working fine if i remove the apostrophe,
but my input string will contain the apostrophe..
Give me a suggestion for rectifying this

Thanks,
Kirrthana







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



[sqlite] Usage of special characters in insert query

2006-12-13 Thread Kirrthana M
Hi,

In my query for inserting values into the database i have a statement as
given below
insert into table_name values('I haven't done that');

table is created with this option
create table table_name(str varchar(20));

when the insert statement is executed it gives the error in haven't,It is
working fine if i remove the apostrophe,
but my input string will contain the apostrophe..
Give me a suggestion for rectifying this

Thanks,
Kirrthana