Re: [sqlite] problem reading a row of data

[Jim Crafton]

Thanks God it's Friday. I'm an utter and complete idiot. I was using
the wrong filename. Misspelled it by a single character. I'm going to
pretend this never happened
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem reading a row of data

[Jim Crafton]

I'm trying to use a select statement to read a row of data from a DB.
I have created the db myself, using another program that uses sqlite,
and have verified (using a gui SQlite db browser) that the file does
in fact have data in it (2 tables, both about 1.3 million rows, the
file is around 150Mb in size).

The table schema (for the one I'm interested in) looks like so:

CREATE TABLE IF NOT EXISTS RFLogIndex ( idxID INTEGER PRIMARY KEY,
masterSequence INTEGER,  logFileOffset INTEGER );

After opening the db with sqlite3_open() I attempt to execute a select
statement.

The select statement I'm trying to execute is trivial:

select * from RFLogIndex where idxID = 1;

I do this using sqlite3_prepare() and that returns SQLITE_OK

I call sqlite3_step() and get a return code of SQLITE_DONE (101),
implying there's no data! Which is most definitely NOT correct,
because when I run the same query in the GUI browser, I get exactly 1
row back, as expected.

The code is running in a simple command line program, so no extra
threads. I've built sqlite myself (running on Windows XP SP 3 32bit,
Visual Studio 2008), and I'm using it successfully in other programs
and they all query just fine.

I'm doing something wrong, but I just can't see it yet. Any ideas?

Thanks

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

[sqlite] OpenVMS port

[Jim Crafton]

Does anyone here know if sqlite has been ported over to OpenVMS?  I
tried a google search but didn't find much. We are considering using
it at work, but the machines that it would run on all use OpenVMS, so
I don't know what would be involved to get the lib to build.

Thanks

Jim C

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

Re: [sqlite] deleting a single row

[Jim Crafton]

Excellent! I didn't realize that the "oid" existed! This is perfect.
Thanks for this!


Not directly. But every row has an implicit primary key that you can
refer to using (amongst other names) "oid". You can use a SELECT to
locate a single oid value and then use the oid to delete a single
row. i.e. instead of:

  DELETE FROM xxx WHERE ;

do

  DELETE FROM xxx
  WHERE oid = (SELECT oid FROM xxx WHERE  LIMIT 1)

or similar.

Dan.



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


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




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

Re: [sqlite] deleting a single row

[Jim Crafton]

:)

On 2/20/07, P Kishor <[EMAIL PROTECTED]> wrote:

On 2/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> On 2/19/07, Jim Crafton <[EMAIL PROTECTED]> wrote:
> > If I have a simple table without an index column, and have multiple
> > rows with the same data, is it possible to *only* delete one row? In
> > other words, is there anything like the LIMIT syntax that's found in
> > the SELECT command for DELETEs?
> >
>
>
> you could select all distinct records in a temp table, drop the
> original table, and recreate the table
>
> CREATE TEMP TABLE tmp AS
> SELECT DISTINCT *

ack! never mind. This does what I thought you asked, not what you
actually asked. Time to go to bed.



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




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

[sqlite] deleting a single row

[Jim Crafton]

If I have a simple table without an index column, and have multiple
rows with the same data, is it possible to *only* delete one row? In
other words, is there anything like the LIMIT syntax that's found in
the SELECT command for DELETEs?

Thanks,

Jim

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

Re: [sqlite] Update and insert questions

[Jim Crafton]

but it fails because the embedded WHERE clause is no longer catching
the row. It is still looking for

WHERE LastName='Doe' AND
  FirstName='John' AND
  Address='100 Nowhere Ave.' AND
  Age=45;

instead of

WHERE LastName='Doe' AND
  FirstName='Jane' AND
  Address='100 Nowhere Ave.' AND
  Age=45;





Yeah I think you're right. I changed the code to *not* use the bind
functions, and just dump the values directly into the SQL statement (
I think this was a case of me trying to be too clever), and that works
like a charm now.
So I guess the moral of this is to use bind cautiously :)

Cheers

Jim

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

Re: [sqlite] Update and insert questions

[Jim Crafton]

well, the first time you update the row (and, you haven't said what
values you update it with), it succeeds because your WHERE clause
successfully matches.


I'm using the sqlite3_bind functions to modify the values.




Second time, the WHERE clause doesn't match because you have changed
the values. For example, if the first time you went and changed the
value of FirstName to 'Jane', the second time around your WHERE clause
won't match.


I'm keeping this in mind. I'm retaining the old value prior to the
change, what SELECT returns back to me.



You might find thins a lot easier if you set a primary key in the
table, and use that to match the rows.

Agreed, but I'm trying to make this work for the general case, where I
can't assume anything about the design/layout of the tables.

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

Re: [sqlite] Update and insert questions

[Jim Crafton]

On 2/16/07, RB Smissaert <[EMAIL PROTECTED]> wrote:

Nearly new to SQLite as well, but shouldn't this:

UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=?

Be altered to this:

UPDATE Person SET LastName='?', FirstName='?', Address='?', Age='?'



I thought that the plain "?" character was an indicator that you were
going to modify the column value via the sqlite3_bindXXX functions.

Cheers

Jim

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

[sqlite] Update and insert questions

[Jim Crafton]

OK, please bear with me here, as I'm very much of an SQL newbie.

I'm writing a wrapper around sqlite.
I want the to code to be able to modify a given value (column) of a
specific row. To do this, as I understand it, I need to use the SQL
UPDATE statement coupled with a WHERE clause. So assuming the
following table :
CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
);


With a single row of:
Doe   John 100 Nowhere Ave.  45

I want to change "John" to "Bob".

If I want to update this, I would write
UPDATE Person SET LastName=?, FirstName=?, Address=?, Age=?
WHERE LastName='Doe' AND FirstName='John' AND Address='100 Nowhere
Ave.' AND Age=45;

I then use the sqlite bind APIs to change values accordingly.

The first time I execute this in sqlite, the sql execution succeeds.
The second time I execute this there is no change to the DB, but the
API calls don't return any error code!
If I change the "=" operator in the WHERE clause to "like" then the
operation makes the change to the DB.

Is there something strange going on? I notice that if I then run some
external tool (like sqlite3Explorer) and run SQL statements directly,
the update statement with the "=" fails, and I get weird errors from
the tool if I use the GUI to edit the row.

Is there something that is being corrupted in the db file?

Thanks

Jim C

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

[sqlite] sqlite update question

[Jim Crafton]

I seem to be having a problem with updates to a table.

I am using SQLITE_VERSION "3.3.8", statically compiled on Win32 with VC++ 6.

I have two sqlite3_stmt* instances in the same process.
The first is allocated by a call to sqlite3_prepare() with a SELECT statement.
The second is allocated with the intention of updating a column in the
table by a call to sqlite3_prepare() using an UPDATE statement and the
"?" syntax for the actual values.
I bind the update values on the second sqlite3_stmt by a call to
sqlite3_bind_XXX.
I then call sqlite3_step on the second sqlite3_stmt, and then
sqlite3_finalize. All of this succeeds.
I then reset the first sqlite3_stmt (the SELECT) by calling
sqlite3_finalize() with the first sqlite3_stmt and then reopen it
sqlite3_prepare() (using the same SQL SELECT statement).

However, when I examine the results I do *not* see the changed column!
Instead it has the old value in it. I am at a loss for what I am doing
wrong, and while I'm sure it's something obvious and stupid, I'm not
seeing it so far.

Any help would be most appreciated!

Thanks

Jim

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

[sqlite] cursor question

[Jim Crafton]

Does the latest version of sqlite have cursors? If so, where might I
find docs on using them?

Thanks

Jim C

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

[sqlite] querying hierarchy

[Jim Crafton]

In my table(s) I need to model a class hierarchy, as well as a
class/var/function belonging to a namespace, and/or a function/var
belonging to a class. In other words a parent/child relationship. If I
simply add a new column to my primary symbols  table, call it
"Parent", how would I query for a given row plus any children (and any
of their children, and so on)? If this is too general an SQL question
and it's not appropriate to the list, my apologies.

Cheers

Jim C

Re: [sqlite] sqlite problem

[Jim Crafton]

OK I figured that out. Wow, that makes an amazing difference, from 20
seconds and 100% CPU usage to instaneous. I take that you want to do
this on any of the tables you want to join together?

Thanks!!


Jim

Re: [sqlite] sqlite problem

[Jim Crafton]

> try creating an index on functions.symbolid

OK, at the risk of sounding stupid, how do I do that? Is that just
some new syntax in creating the table?

Cheers

Jim

[sqlite] sqlite problem

[Jim Crafton]

I'm a newbie to using SQL in general, so my apologies if this has been
answered before.

I have a series of tables I'd like to create to represents the output
that comes from parsing a bunch of source code files. Basically ctags
output in DB format.

I've created the following tables using sqlite3

CREATE TABLE Types (
   TypeID INTEGER PRIMARY KEY
 , TypeInfo CHAR(125)
);

CREATE TABLE Templates (
   TemplateID INTEGER PRIMARY KEY
 , TemplateSignature CHAR(125)
);

CREATE TABLE Files (
   FileID INTEGER PRIMARY KEY
 , Path CHAR(255)
);

CREATE TABLE Symbols (
   Id INTEGER PRIMARY KEY
 , Kind INTEGER
 , Name CHAR(125)
 , FileID INTEGER
 , TypeID INTEGER
 , Offset INTEGER
 , LineNumber INTEGER
 , TemplateID INTEGER DEFAULT 0 NOT NULL
 , Parent INTEGER DEFAULT 0
);

CREATE TABLE Functions (
   FuncID INTEGER PRIMARY KEY
 , Signature CHAR(125)
 , SymbolID INTEGER
 , ReturnTypeID INTEGER NOT NULL
);


I can then populate the tables fine. In my test, the symbols table had
over 11,000 entries and the functions table over 7,200 entries.

When I run the following query:
"select symbols.name, functions.signature from symbols, functions
where functions.symbolid = symbols.id;"

This takes a long time (over 20 secs) on a P4 3 Ghz with 1 Gb RAM.
Should it take this long? Is it slow because my table is setup
incorrectly?

Thanks so much!

Jim Crafton