RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[epankoke]

1. When the application first starts up, click on the Servers icon in the 
middle of the window.  Alternatively, select Tools | Server | Server Manager.

2. For Server Engine, select SQLite (of course).  If you're still using 2.x, go 
to step 4.

3. Select the options tab, put 3 in the first box for Version, then switch back 
to the Parameters tab.

4. Put some sort of description in for Server Name.  This is simply for you to 
identify the database when looking through the treeview list on the left hand 
side.

5. Click the ... button next to Database Filename and browse for your database. 
 Note, if the version you specified and the version of the database that you 
selected do not match (ex: you put 3 in the options tab and are only using a 
2.x database), DBTools will "create" the connection, but you'll never see 
anything listed in the server explorer.

6. Click OK.  The server explorer should be refreshed, and under Default 
Servers | Default Group, you should see a new node with the Server Name you 
specified.  When you expand that you will see a Databases folder, and expanding 
that will reveal the a node with the name of your database, assuming all went 
correctly.
--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Rob Richardson" <[EMAIL PROTECTED]>
> For the life of me, I can't figure out how to open a database in
> DBManager.  I looked at it before, tried for an hour, and erased it.  I
> just tried again, with the same result.  If I can't figure out how to do
> such an easy thing, I can't trust the program.
> 
> RobR
> 
> 
> 
> -Original Message-
> From: COS [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 07, 2007 12:26 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 
> Hi Rob,
> 
> You can try DBManager Standard or Enterprise Editions. The first one is
> free
> for personal use, the second is a commercial product. The complete list
> of
> features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You
> can
> edit your tables data and also manipulate images in blob fields.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Nemanja Corlija,

>>PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
>>available at http://www.yunqa.de/delphi/sqlitespy/
>Ralf, thanks SQLiteSpy. Download link is not working though.

Thanks for reporting the problem. The link is now fixed and working all right.

Regards,

Ralf 


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Kees Nuyt]

On Wed, 7 Feb 2007 13:03:09 -0500, you wrote:

>I can't get SQLiteExplorer to work with my databases.  It always gives
>me an "unknown file format" error.  I believe it's been quite a while
>since it was updated.  
>
>RobR

Did you download sqlite3explorer or sqliteexplorer? The latter
is meant for sqlite v2.x.x databases, and requires v2.x.x of
sqlite.dll and has not been maintained for a long time.

Make sure you use sqlite3explorer.exe v2.0 (which is built for
sqlite 3.x.x) and put your sqlite3.dll in the same directory.
If it fails to work on an existing database, try to create a new
one from sqlite3explorer.

I use it in combination with sqlite3.dll, version 3.3.12,
without any problem. I downloaded it 2006-03-13 and it still
works well.
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Nemanja Corlija]

On 2/7/07, Ralf Junker <[EMAIL PROTECTED]> wrote:

PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
available at http://www.yunqa.de/delphi/sqlitespy/


Ralf, thanks SQLiteSpy. Download link is not working though.

--
Nemanja Corlija <[EMAIL PROTECTED]>

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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Robert Simpson,

>For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
>engine to help me retrieve key information.  My requirements were a little
>more complex than just getting rowid, but here's what I did to get me that
>far at least:
>
>Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
>  1.  For each table, fetch the internal cursor for that table in the
>statement
>  2.  For each cursor, fetch the rowid
>
>That was the API modification part.  Caveats are that this information is
>not always available.  So if its not, I just return a failure indicator.

Thanks for backing me up on my problem. However, unlike you I am very reluctant 
to hack the SQLite because of the concerns you mentioned. I would love to see 
something similar to what you have done officially supported by SQLite.

>The next part involved querying the schema of each table, looking up indexes 
>and finding the most logical primary key definitions for the table(s) involved 
>in the select, and compiling a side-by-side SELECT clause returning the user's 
>defined primary keys using a WHERE clause with the above fetched rowid's.

For cell updates, SQLiteSpy would be happy to use the RowID index, which is 
auto-generated and always available. Hence a function like 
sqlite3_column_rowid() would just fill the gap perfectly.

Let's see what the future brings ...

Regards,

Ralf

PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
available at http://www.yunqa.de/delphi/sqlitespy/ 


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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

For the life of me, I can't figure out how to open a database in
DBManager.  I looked at it before, tried for an hour, and erased it.  I
just tried again, with the same result.  If I can't figure out how to do
such an easy thing, I can't trust the program.

RobR



-Original Message-
From: COS [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 12:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Hi Rob,

You can try DBManager Standard or Enterprise Editions. The first one is
free
for personal use, the second is a commercial product. The complete list
of
features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You
can
edit your tables data and also manipulate images in blob fields.

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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

I can't get SQLiteExplorer to work with my databases.  It always gives
me an "unknown file format" error.  I believe it's been quite a while
since it was updated.  

RobR


-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 3:14 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Regarding:  "If I want to change data ."

Sqlite3Explorer is free software, and works as you describe.  I imagine
there are several others.

http://www.singular.gr/sqlite/  (Not sure if there will be future
releases of this or not) 


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[COS]

Hi Rob,

You can try DBManager Standard or Enterprise Editions. The first one is free
for personal use, the second is a commercial product. The complete list of
features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You can
edit your tables data and also manipulate images in blob fields.

Another application from the same company is QueryIT. THis one is intended
to help people navigate through table records, which also includes master
detail records, generate charts, work with blob fields (show images).
Although this is a commercial product is very cheap in its category. For
more details see http://www.dbtools.com.br/EN/queryit.

Best Regards,

COS


- Original Message - 
From: "Rob Richardson" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 06, 2007 4:49 PM
Subject: [sqlite] Is there a SQLiteSpy-like thing that will let me change
data from a grid?


Greetings!

The only tool I have for examining and manipulating SQLite databases is
SQLiteSpy.  If I want to change data in that program, I have to write an
SQL statement.  Every other database editor I've seen lets a use do
simple things from a grid.  Open a table and data is loaded into a grid,
click on a cell and type in a value and it gets written to the table,
select a row and click a Delete button and the row is deleted from the
table, and so on.  There must be a tool somewhere that will do that for
a SQLite database, or there's some feature of SQLiteSpy I don't know
about.  Can somebody please point me to one or the other?

Thank you very much.

Rob Richardson


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

> -Original Message-
> From: Robert Simpson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 8:25 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 

> __declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt
> *pstmt,
> int cursor, sqlite_int64 *prowid)

Oops I forgot to de-decorate this call ... just pretend.

And I also neglected to mention that the input statement must have already
been started with a previous call to sqlite3_step() -- as these functions
will return the rowid information for the row you've just stepped into.

Robert




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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
engine to help me retrieve key information.  My requirements were a little
more complex than just getting rowid, but here's what I did to get me that
far at least:

Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
  1.  For each table, fetch the internal cursor for that table in the
statement
  2.  For each cursor, fetch the rowid

That was the API modification part.  Caveats are that this information is
not always available.  So if its not, I just return a failure indicator.

The next part involved querying the schema of each table, looking up indexes
and finding the most logical primary key definitions for the table(s)
involved in the select, and compiling a side-by-side SELECT clause returning
the user's defined primary keys using a WHERE clause with the above fetched
rowid's.

So given these table definitions:
CREATE TABLE Foo (MyID INTEGER, BarID INTEGER, MyValue, CONSTRAINT pk
PRIMARY KEY(MyId, MyKey));
CREATE TABLE Bar (BarID INTEGER PRIMARY KEY, MyBarValue);

And this SELECT clause:
SELECT MyValue, MyBarValue FROM Foo INNER JOIN Bar ON Foo.BarID = Bar.BarID;

I could then construct a hidden side-by-side query, looking like:
SELECT MyID, BarID FROM Foo WHERE ROWID = ?
(I already had the rowid from the Bar table without a sub-query using the
API calls I made earlier)

All this is wrapped up inside the SQLiteDataReader when you specify a
KeyInfo flag to open the reader on the command.

As for the API extensions ...

The only reason I never posted it and wasn't sure of its suitability is that
I've only ever tested it the way I use it and dunno how stable it is for
general purpose usage.  Also, the initial function call to
sqlite_table_cursor requires the database index and root page number which
can only be obtained by previous fetches into the database schema.

Here's the code at any rate:

/*
[in] pstmt -- The statement to fetch a cursor on
[in] iDb   -- The database index (id from a previous call to
PRAMGA database_list)
[in] tableRootPage -- Root page of the table, (fetched from
sqlite_master table)

   For a given row-returning statement which may contain multiple cursors,
return the index of
   the cursor responsible for iterating the specified table.  You must
specify the root page of that
   table and the database it's in to get a match.

   RETURNS
   -1 on failure, or a 0-based index of the cursor for the given table
*/
int sqlite3_table_cursor(sqlite3_stmt *pstmt, int iDb, Pgno tableRootPage)
{
  Vdbe *p = (Vdbe *)pstmt;
  int n;

  for (n = 0; n < p->nCursor && p->apCsr[n] != NULL; n++)
  {
if (p->apCsr[n]->isTable == FALSE) continue;
if (p->apCsr[n]->iDb != iDb) continue;
if (p->apCsr[n]->pCursor->pgnoRoot == tableRootPage)
  return n;
  }
  return -1;
}

/*
[in] pstmt   -- A row-returning SQLite statement
[in] cursor  -- A cursor previously returned from sqlite3_table_cursor()
[out] prowid -- The rowid for the specified cursor

Fetches (if possible) a rowid for a given cursor in a row-returning
statement.

RETURNS
0 on success, or one of the SQLite error codes otherwise
*/
__declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
  Vdbe *p = (Vdbe *)pstmt;
  int rc = 0;
  Cursor *pC;

  if (cursor < 0 || cursor >= p->nCursor) return SQLITE_ERROR;
  if (p->apCsr[cursor] == NULL) return SQLITE_ERROR;
  pC = p->apCsr[cursor];

  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) return rc;

  if( pC->rowidIsValid )
  {
*prowid = pC->lastRowid;
  }
  else if(pC->pseudoTable )
  {
*prowid = keyToInt(pC->iKey);
  }
  else if(pC->nullRow || pC->pCursor==0)
  {
return SQLITE_ERROR;
  }
  else
  {
if (pC->pCursor == NULL) return SQLITE_ERROR;
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = keyToInt(*prowid);
  }
  return 0;
}





> -Original Message-
> From: Ralf Junker [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 4:23 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 
> 
> >> The SQLite library lacks certain API required to implement cell
> >> editing: Most notably, there is no functionality to retrieve
> >> the exact origin (table, row and column) of a cell, which must
> >> be known to store back the modified value.
> >
> >See:
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name
> 
> I know, and these functions were much appreciated when they entered
> SQLite. However, they do not cover the table row (RowID). Here is the
> discussion about what's still missing. The thread also points out a few
> inconsistencies in the implementation of the above functions, IIRC:
> 
>   

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

>> The SQLite library lacks certain API required to implement cell
>> editing: Most notably, there is no functionality to retrieve 
>> the exact origin (table, row and column) of a cell, which must
>> be known to store back the modified value.
>
>See:
>
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name

I know, and these functions were much appreciated when they entered SQLite. 
However, they do not cover the table row (RowID). Here is the discussion about 
what's still missing. The thread also points out a few inconsistencies in the 
implementation of the above functions, IIRC:

  http://thread.gmane.org/gmane.comp.db.sqlite.general/19323

Ralf 


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> 
> The SQLite library lacks certain API required to implement cell
> editing: Most notably, there is no functionality to retrieve 
> the exact origin (table, row and column) of a cell, which must
> be known to store back the modified value.

See:

   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name

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


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Rob Richardson,

>The only tool I have for examining and manipulating SQLite databases is
>SQLiteSpy.  If I want to change data in that program, I have to write an
>SQL statement.  Every other database editor I've seen lets a use do
>simple things from a grid.

As the author of SQLiteSpy: There are indeed DB managers which _allow_ cell 
editing for all SQL SELECTs. Some are able to commit changes to single table 
SELECTs. However, I have experienced frequent failure updating multiple table, 
nested, and aggregate SELECTs.

There are reason why such updates must indeed fail: The SQLite library lacks 
certain API required to implement cell editing: Most notably, there is no 
functionality to retrieve the exact origin (table, row and column) of a cell, 
which must be known to store back the modified value. These issues have been 
discussed and acknowledged on this list. I am hopeful that we will eventually 
see them implemented.

Until then, however, there are theoretical limits to grid editing. They could 
be worked around by parsing the SQL, but this would be overkill for a simple DB 
editor like SQLiteSpy. I therefore decided to disable cell editing altogether 
in the first version of SQLiteSpy.

>  Open a table and data is loaded into a grid,
>click on a cell and type in a value and it gets written to the table,
>select a row and click a Delete button and the row is deleted from the
>table, and so on.  There must be a tool somewhere that will do that for
>a SQLite database, or there's some feature of SQLiteSpy I don't know
>about.  Can somebody please point me to one or the other?

The upcoming version of SQLiteSpy will see support for table cell editing: The 
grid will internally work in table mode for tables selected from the schema 
treeview. Such table SELECTs will be editable, use an improved buffer 
mechanism, execute faster, and use less memory. Result sets from queries 
executed from the SQL editor will be read-only, even if they are just simple 
table selects. I hope to release within the next few days.

Regards,

Ralf 


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

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Kees Nuyt]

On Tue, 6 Feb 2007 15:13:55 -0500, Donald Griggs wrote:

>Regarding:  "If I want to change data ."
>
>Sqlite3Explorer is free software, and works as you describe.
>I imagine there are several others.

I second this wholeheartedly.

>http://www.singular.gr/sqlite/   (Not sure if there
>will be future releases of this or not) 
-- 
  (  Kees Nuyt
  )
c[_]

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

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Griggs, Donald]

Regarding:  "If I want to change data ."

Sqlite3Explorer is free software, and works as you describe.  I imagine
there are several others.

http://www.singular.gr/sqlite/  (Not sure if there will be future
releases of this or not) 



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

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