Re: [sqlite] Unable to use the .backup command

[Simon Slavin]

On 1 Jun 2012, at 4:55am, Manish Swaroop  wrote:

> I created a table in SQLite and inserted few records. However I was unable
> to use the .backup command and keep getting the following error message:
> Error: cannot open testdatabase123.db

The documentation says that the command format is

.backup ?DB? FILE  Backup DB (default "main") to FILE

I don't know whether the tool correctly understands a missing database name, so 
try specifying 'main' (or whatever it is you're using) explicitly before the 
name of the file.

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

[sqlite] Unable to use the .backup command

[Manish Swaroop]

I created a table in SQLite and inserted few records. However I was unable
to use the .backup command and keep getting the following error message:
Error: cannot open testdatabase123.db

I noticed that the table that I created (see below) was not "saved"
anywhere so when I exited out of SQLite and started it over again the table
was nowhere to be found.
CREATE TABLE TEST_USERS (USER_ID TEXT PRIMARY KEY, PASSWORD TEXT NOT NULL,
IS_ADMIN INTEGER NOT NULL);

Any idea where am I going wrong? Any help is appreciated.

I am using SQLite version 3.6.12 on Mac OS X 10.6.8.

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

Re: [sqlite] The "sessions" branch

[Richard Hipp]

On Thu, May 31, 2012 at 2:55 PM, Charles Samuels wrote:

> On Thursday, May 31, 2012 11:53:18 f.h. Richard Hipp wrote:
> > On Thu, May 31, 2012 at 2:40 PM, Charles Samuels  >wrote:
> > > So, I'd like to ask what's wrong with the session extension that it's
> > > never been rolled into the main distribution and hasn't been worked on
> > > since 2011 July?
> >
> > The sessions branch is stable and fully supported and will continue to be
> > supported.
>
>
> What's the right way to go about using it? I have a fossil tree and I can
> switch to the branch, but how do I make sure that it's merged with 3.7.12.1
> and also becomes part of the amalgamation tree?
>

If you are on the sessions branch, then you have the changes just do:

./configure; make sqlite3.c

Then compile your application with -DSQLITE_ENABLE_SESSION to get the
functionality.  Documentation on the session extensions is thin but can be
seen here:  http://www.sqlite.org/sessions/session.html



>
> Sorry about the potentially stupid questions.
>
> > But I got negative feedback from the community when I proposed
> > it, so we've kept it out of trunk.
>
> You'll get positive feedback from *this* member of the community :)
>
> Seems strange that it's not at least off by default with an optional
> #define, in
> trunk.
>
> Charles
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Nico Williams]

On Thu, May 31, 2012 at 12:11 PM, Marc L. Allen
 wrote:
> True, but an optimizer could only intelligently exclude OUTER JOINS in most 
> cases.

Right, and LEFT OUTER JOINs at that (since SQLite3 doesn't support RIGHT joins).

> For instance, if I have a convenience view that combines a series of tables 
> to provide an overall list of something or another:
>
> View ->
>        SELECT ...
>        FROM a
>        INNER JOIN b ON ...
>        INNER JOIN c ON ...
>        INNER JOIN d ON ...
>
> As the schema designer, I might know that all three joins will always, ALWAYS 
> work so that if I use that view and only look at fields in table a, then 
> don't need those joins.  However, since SQLite can't really know that, it 
> always has to perform all three joins regardless of which fields are returned.

Right, because a row from a might not be in the result set given the
INNER JOINs.

> Also, unless you do rewrite the query, how about this view?

"You" here being the application developer / user, of course, not the
query optimizer.

I should have distinguished INNER and OUTER joins, indeed.

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

Re: [sqlite] The "sessions" branch

[Charles Samuels]

On Thursday, May 31, 2012 11:53:18 f.h. Richard Hipp wrote:
> On Thu, May 31, 2012 at 2:40 PM, Charles Samuels wrote:
> > So, I'd like to ask what's wrong with the session extension that it's
> > never been rolled into the main distribution and hasn't been worked on
> > since 2011 July?
> 
> The sessions branch is stable and fully supported and will continue to be
> supported.


What's the right way to go about using it? I have a fossil tree and I can 
switch to the branch, but how do I make sure that it's merged with 3.7.12.1 
and also becomes part of the amalgamation tree?

Sorry about the potentially stupid questions.

> But I got negative feedback from the community when I proposed
> it, so we've kept it out of trunk.

You'll get positive feedback from *this* member of the community :)

Seems strange that it's not at least off by default with an optional #define, 
in 
trunk.

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

Re: [sqlite] The "sessions" branch

[Marc L. Allen]

Negative feedback?  For what sounds like an optional component?  How come?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, May 31, 2012 2:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] The "sessions" branch

On Thu, May 31, 2012 at 2:40 PM, Charles Samuels wrote:

>
> So, I'd like to ask what's wrong with the session extension that it's 
> never been rolled into the main distribution and hasn't been worked on 
> since 2011 July?
>

The sessions branch is stable and fully supported and will continue to be 
supported.  But I got negative feedback from the community when I proposed it, 
so we've kept it out of trunk.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The "sessions" branch

[Richard Hipp]

On Thu, May 31, 2012 at 2:40 PM, Charles Samuels wrote:

>
> So, I'd like to ask what's wrong with the session extension that it's never
> been rolled into the main distribution and hasn't been worked on since 2011
> July?
>

The sessions branch is stable and fully supported and will continue to be
supported.  But I got negative feedback from the community when I proposed
it, so we've kept it out of trunk.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The "sessions" branch

[Charles Samuels]

Hi,

I couldn't help but notice that in the sqlite fossil repository, there's a 
branch named "sessions" which has this: 
http://www.sqlite.org/cgi/src/dir?name=ext/session

This is a very useful feature for me because it would allow me to rollback a 
change after it's been committed (if somehow I've discovered that it's wrong).

So, I'd like to ask what's wrong with the session extension that it's never 
been rolled into the main distribution and hasn't been worked on since 2011 
July?

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

Re: [sqlite] performance difficulty across multiple queries

[Brannon King]

>I recommend you execute the SQL command ANALYZE then try various of those
SELECTs again.  This can cause the SQLite optimizer to make different
choices about which indexes to use.

Thanks for the suggestion. I am familiar with the Analyze command and have
been using it. However, it doesn't seem to help in this scenario where the
choice is whether or not to use the index for the ORDER BY or the JOIN. I
find it a little frustrating that a single query can't use two indexes from
the same table as other database engines support that ability.


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

Re: [sqlite] performance difficulty across multiple queries

[Simon Slavin]

On 31 May 2012, at 6:18pm, Brannon King  wrote:

> The Services and Telemetry tables have hundreds of rows, but the Events
> table has a million. I can't seem to figure out the right combination of
> indexes to get the following queries to all execute quickly. Some indexes
> help some queries but hurt others. When I have indexes on TelemetryId and
> TimestampTicks the former seems to be favored even when the TimestampTicks
> index provides quicker results. When I use the '+' to disable the
> TelemetryId index I get some fast and some slow results. Help?

I recommend you execute the SQL command ANALYZE then try various of those 
SELECTs again.  This can cause the SQLite optimizer to make different choices 
about which indexes to use.

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

[sqlite] performance difficulty across multiple queries

[Brannon King]

I've got this (simplified) table schema:

 

  CREATE TABLE [Services] ([Id] INTEGER PRIMARY KEY, [AssetId] INTEGER NULL,
[Name] TEXT NOT NULL);

  CREATE TABLE [Telemetry] ([Id] INTEGER PRIMARY KEY, [ServiceId] INTEGER
NULL, [Name] TEXT NOT NULL)

  CREATE TABLE [Events] ([Id] INTEGER PRIMARY KEY, [TelemetryId] INTEGER NOT
NULL, [TimestampTicks] INTEGER NOT NULL, [Value] TEXT NOT NULL)

 

The Services and Telemetry tables have hundreds of rows, but the Events
table has a million. I can't seem to figure out the right combination of
indexes to get the following queries to all execute quickly. Some indexes
help some queries but hurt others. When I have indexes on TelemetryId and
TimestampTicks the former seems to be favored even when the TimestampTicks
index provides quicker results. When I use the '+' to disable the
TelemetryId index I get some fast and some slow results. Help?

 

SELECT MIN/MAX(e.TimestampTicks) FROM Events e INNER JOIN Telemetry t ON
t.ID = e.TelemetryID INNER JOIN Services s ON s.ID = t.ServiceID WHERE
s.AssetID = @AssetId;

 

SELECT e1.* FROM Events e1 INNER JOIN Telemetry t1 ON t1.Id = e1.TelemetryId
INNER JOIN Services s1 ON s1.Id = t1.ServiceId WHERE t1.Name =
@TelemetryName AND s1.Name = @ServiceName;

 

SELECT * FROM Events e INNER JOIN Telemetry t ON t.Id = e.TelemetryId INNER
JOIN Services s ON s.Id = t.ServiceId WHERE s.AssetId = @AssetId AND
e.TimestampTicks >= @StartTimeTicks ORDER BY e.TimestampTicks LIMIT 1000;

 

SELECT e.Id, e.TelemetryId, e.TimestampTicks, e.Value FROM (

SELECT e2.Id AS [Id], MAX(e2.TimestampTicks) as
[TimestampTicks]

FROM Events e2 INNER JOIN Telemetry t ON
t.Id = e2.TelemetryId INNER JOIN Services s ON s.Id = t.ServiceId

WHERE s.AssetId = @AssetId AND
e2.TimestampTicks <= @StartTimeTicks 

GROUP BY e2.TelemetryId) AS grp

INNER JOIN Events e ON grp.Id = e.Id;

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

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Marc L. Allen]

>> Let's say I only request columns from tables a and c.  As it turns out, 
>> since I'm using the same linkage columns (c1), the query can be optimized to:
>>
>>  SELECT ...
>>  FROM a
>>  LEFT OUTER JOIN c on a.c1 = c.c1

And that's even wrong, since the original query required that a record for 
table b exist.  This makes optimizations even less likely in complex queries.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Thursday, May 31, 2012 1:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins?

True, but an optimizer could only intelligently exclude OUTER JOINS in most 
cases.

For instance, if I have a convenience view that combines a series of tables to 
provide an overall list of something or another:

View ->
SELECT ...
FROM a
INNER JOIN b ON ...
INNER JOIN c ON ...
INNER JOIN d ON ...

As the schema designer, I might know that all three joins will always, ALWAYS 
work so that if I use that view and only look at fields in table a, then don't 
need those joins.  However, since SQLite can't really know that, it always has 
to perform all three joins regardless of which fields are returned.

Also, unless you do rewrite the query, how about this view?

SELECT ...
FROM a
LEFT OUTER JOIN b ON a.c1 = b.c1
LEFT OUTER JOIN c ON b.c1 = c.c1

Let's say I only request columns from tables a and c.  As it turns out, since 
I'm using the same linkage columns (c1), the query can be optimized to:

SELECT ...
FROM a
LEFT OUTER JOIN c on a.c1 = c.c1

This was just a simple example.  I can only imagine how much work it would take 
to handle all the possible variations.

As this is a lite database, I would much prefer optimization to worry about 
making the joins I specify as fast as possible and let me worry about the joins 
in the query.  If you don't control how your views are used (as in the Ops 
example), then the view is truly a convenience function and, as with most 
conveniences, has a cost.

I could, however, imagine a module that compiled precompile views (or even 
queries) and generate multiple plans based on combinations of desired output 
fields.  Those could then be stored or something for quick lookup.  Sort of 
like preparing and storing the result for later use.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Nico Williams
Sent: Thursday, May 31, 2012 12:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman  wrote:
>> Actually, anytime you have VIEWs that join table sources you can 
>> benefit from this optimization.
>
> No, there is only a benefit if the optimization actually finds a table 
> that can be removed from the join.

When you create a VIEW from a JOIN you have no idea if queries on that VIEW 
will need the JOIN.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Marc L. Allen]

True, but an optimizer could only intelligently exclude OUTER JOINS in most 
cases.

For instance, if I have a convenience view that combines a series of tables to 
provide an overall list of something or another:

View ->
SELECT ...
FROM a
INNER JOIN b ON ...
INNER JOIN c ON ...
INNER JOIN d ON ...

As the schema designer, I might know that all three joins will always, ALWAYS 
work so that if I use that view and only look at fields in table a, then don't 
need those joins.  However, since SQLite can't really know that, it always has 
to perform all three joins regardless of which fields are returned.

Also, unless you do rewrite the query, how about this view?

SELECT ...
FROM a
LEFT OUTER JOIN b ON a.c1 = b.c1
LEFT OUTER JOIN c ON b.c1 = c.c1

Let's say I only request columns from tables a and c.  As it turns out, since 
I'm using the same linkage columns (c1), the query can be optimized to:

SELECT ...
FROM a
LEFT OUTER JOIN c on a.c1 = c.c1

This was just a simple example.  I can only imagine how much work it would take 
to handle all the possible variations.

As this is a lite database, I would much prefer optimization to worry about 
making the joins I specify as fast as possible and let me worry about the joins 
in the query.  If you don't control how your views are used (as in the Ops 
example), then the view is truly a convenience function and, as with most 
conveniences, has a cost.

I could, however, imagine a module that compiled precompile views (or even 
queries) and generate multiple plans based on combinations of desired output 
fields.  Those could then be stored or something for quick lookup.  Sort of 
like preparing and storing the result for later use.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Nico Williams
Sent: Thursday, May 31, 2012 12:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins?

On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman  wrote:
>> Actually, anytime you have VIEWs that join table sources you can 
>> benefit from this optimization.
>
> No, there is only a benefit if the optimization actually finds a table 
> that can be removed from the join.

When you create a VIEW from a JOIN you have no idea if queries on that VIEW 
will need the JOIN.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Nico Williams]

On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman  wrote:
>> Actually, anytime you have VIEWs that join table sources you
>> can benefit from this optimization.
>
> No, there is only a benefit if the optimization actually finds a table that
> can be removed from the join.

When you create a VIEW from a JOIN you have no idea if queries on that
VIEW will need the JOIN.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault when calling sqlite3_finalize

[Stephen Wood]

That is actually how I originally wrote the function, but when I started
having the segmentation faults I thought maybe I was mistaken on that count
and rewrote it with the call to the step function. Thanks for the tip!

On Thu, May 31, 2012 at 11:31 AM, Dan Kennedy  wrote:

> On 05/31/2012 10:46 PM, Stephen Wood wrote:
>
>> Hello all,
>> I'm having some memory allocation difficulties while using the SQLite C
>> API. The goal of the function that is currently giving me issues is just
>> trying to read the column names from a table.
>>
>
> Tip: Column names are available after sqlite3_prepare_v2() has
> returned. You don't need the sqlite3_step() call in your function.
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Stephen Wood
RMCI, INC.
1525 Perimeter Parkway
Suite 430
Huntsville, AL  35806**
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault when calling sqlite3_finalize

[Dan Kennedy]

On 05/31/2012 10:46 PM, Stephen Wood wrote:

Hello all,
I'm having some memory allocation difficulties while using the SQLite C
API. The goal of the function that is currently giving me issues is just
trying to read the column names from a table.


Tip: Column names are available after sqlite3_prepare_v2() has
returned. You don't need the sqlite3_step() call in your function.

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

Re: [sqlite] Segmentation Fault when calling sqlite3_finalize

[Stephen Wood]

Thank you! I can't believe I missed that!

On Thu, May 31, 2012 at 10:50 AM, Marc L. Allen  wrote:

>columnNames[i] = malloc(strlen(buffer) + 1);
>
> Need to deal with that pesky '\0'!
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Wood
> Sent: Thursday, May 31, 2012 11:47 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Segmentation Fault when calling sqlite3_finalize
>
> Hello all,
> I'm having some memory allocation difficulties while using the SQLite C
> API. The goal of the function that is currently giving me issues is just
> trying to read the column names from a table. I have been having other
> memory allocation faults elsewhere, but I currently have it isolated to
> this particular section of code (I say this because I suppose the root
> problem could be elsewhere in my code). Also strangely enough, if I do not
> store the data returned from sqlite3_column_name, but instead just print it
> to stdout, I do not get a segmentation fault, which also leads to me
> thinking my error may be more fundamental... This function is also run
> multiple times and only produces segmentation faults on the third table I
> run it on. I am using the database here<
> http://download.codeplex.com/Download/Release?ProjectName=chinookdatabase=167067=12934269926367=18924
> >for
> testing.
>
> Please let me know if I can provide any more relevant information.
>
> Any help would be greatly appreciated. Below is the relevant code segment.
> Thanks!
>
> char **getColumnNames(char **columnNames, sqlite3 *database, char
> *tableName) {
>sqlite3_stmt *statement = NULL;
>char sqlStatementString[100];
>char *sqlErrorMessage = 0;
>char buffer[100];
>int status = 0;
>int i = 0;
>int numOfCols = 0;
>
>// Prepare a statement to get the tables from the database
> sprintf(sqlStatementString, "SELECT * FROM '%s';", tableName);
>status = sqlite3_prepare_v2(database, sqlStatementString,
> strlen(sqlStatementString), , NULL);
>if (status != SQLITE_OK) {
>fprintf(stderr, "Error occured when attempting to prepare an SQL
> statement\n"
>"Error:%s\n", sqlErrorMessage);
>// Prevents memory leaking from error message:
>sqlite3_free(sqlErrorMessage);
>exit(1);
>} // end error check
>
>// I only want the first row, because I am only getting the names
>// for the columns. This is why I only run the step function once
>if (sqlite3_step(statement) != SQLITE_ROW) {
>fprintf(stderr, "Either an error occured or table \"%s\" has no
> data!\n", tableName);
>exit(1);
>}
>
>numOfCols = sqlite3_column_count(statement);
>if (numOfCols < 1) {
>perror("No columns in that table!\n");
>exit(1);
>}
>
>columnNames = (char **) malloc(numOfCols * sizeof(char *));
>if (columnNames == NULL) {
>perror("Allocation error: ");
>exit(1);
>} // end error check for malloc
>
>for (i = 0; i < numOfCols; i++) {
>sprintf(buffer,  "%s", (char *) sqlite3_column_name(statement, i));
>
>columnNames[i] = malloc(strlen(buffer));
>if (columnNames[i] == NULL) {
>perror("Failed to allocate memory for a column name\n");
>exit(1);
>} // end malloc error check
>
>strcpy(columnNames[i], buffer);
>} // end column loop
>
>// Destroy the statement
>//  THIS IS WHERE I GET THE SEGMENTATION FAULT **
>status = sqlite3_finalize(statement);
>if (status != SQLITE_OK) {
>fprintf(stderr, "Error occured when attempting to destroy a SQL
> statement\n"
>"Error:%s\n", sqlErrorMessage);
>// Prevents memory leaking from error message:
>sqlite3_free(sqlErrorMessage);
>exit(1);
>} // end error check
>
>return columnNames;
>} // end of function
>
>
> --
> Stephen Wood
> RMCI, INC.
> 1525 Perimeter Parkway
> Suite 430
> Huntsville, AL  35806**
> **
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephen Wood
RMCI, INC.
1525 Perimeter Parkway
Suite 430
Huntsville, AL  35806**
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault when calling sqlite3_finalize

[Marc L. Allen]

columnNames[i] = malloc(strlen(buffer) + 1);

Need to deal with that pesky '\0'!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Stephen Wood
Sent: Thursday, May 31, 2012 11:47 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Segmentation Fault when calling sqlite3_finalize

Hello all,
I'm having some memory allocation difficulties while using the SQLite C API. 
The goal of the function that is currently giving me issues is just trying to 
read the column names from a table. I have been having other memory allocation 
faults elsewhere, but I currently have it isolated to this particular section 
of code (I say this because I suppose the root problem could be elsewhere in my 
code). Also strangely enough, if I do not store the data returned from 
sqlite3_column_name, but instead just print it to stdout, I do not get a 
segmentation fault, which also leads to me thinking my error may be more 
fundamental... This function is also run multiple times and only produces 
segmentation faults on the third table I run it on. I am using the database 
herefor
testing.

Please let me know if I can provide any more relevant information.

Any help would be greatly appreciated. Below is the relevant code segment.
Thanks!

char **getColumnNames(char **columnNames, sqlite3 *database, char
*tableName) {
sqlite3_stmt *statement = NULL;
char sqlStatementString[100];
char *sqlErrorMessage = 0;
char buffer[100];
int status = 0;
int i = 0;
int numOfCols = 0;

// Prepare a statement to get the tables from the database 
sprintf(sqlStatementString, "SELECT * FROM '%s';", tableName);
status = sqlite3_prepare_v2(database, sqlStatementString, 
strlen(sqlStatementString), , NULL);
if (status != SQLITE_OK) {
fprintf(stderr, "Error occured when attempting to prepare an SQL 
statement\n"
"Error:%s\n", sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check

// I only want the first row, because I am only getting the names
// for the columns. This is why I only run the step function once
if (sqlite3_step(statement) != SQLITE_ROW) {
fprintf(stderr, "Either an error occured or table \"%s\" has no 
data!\n", tableName);
exit(1);
}

numOfCols = sqlite3_column_count(statement);
if (numOfCols < 1) {
perror("No columns in that table!\n");
exit(1);
}

columnNames = (char **) malloc(numOfCols * sizeof(char *));
if (columnNames == NULL) {
perror("Allocation error: ");
exit(1);
} // end error check for malloc

for (i = 0; i < numOfCols; i++) {
sprintf(buffer,  "%s", (char *) sqlite3_column_name(statement, i));

columnNames[i] = malloc(strlen(buffer));
if (columnNames[i] == NULL) {
perror("Failed to allocate memory for a column name\n");
exit(1);
} // end malloc error check

strcpy(columnNames[i], buffer);
} // end column loop

// Destroy the statement
//  THIS IS WHERE I GET THE SEGMENTATION FAULT **
status = sqlite3_finalize(statement);
if (status != SQLITE_OK) {
fprintf(stderr, "Error occured when attempting to destroy a SQL 
statement\n"
"Error:%s\n", sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check

return columnNames;
} // end of function


--
Stephen Wood
RMCI, INC.
1525 Perimeter Parkway
Suite 430
Huntsville, AL  35806**
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segmentation Fault when calling sqlite3_finalize

[Stephen Wood]

Hello all,
I'm having some memory allocation difficulties while using the SQLite C
API. The goal of the function that is currently giving me issues is just
trying to read the column names from a table. I have been having other
memory allocation faults elsewhere, but I currently have it isolated to
this particular section of code (I say this because I suppose the root
problem could be elsewhere in my code). Also strangely enough, if I do not
store the data returned from sqlite3_column_name, but instead just print it
to stdout, I do not get a segmentation fault, which also leads to me
thinking my error may be more fundamental... This function is also run
multiple times and only produces segmentation faults on the third table I
run it on. I am using the database
herefor
testing.

Please let me know if I can provide any more relevant information.

Any help would be greatly appreciated. Below is the relevant code segment.
Thanks!

char **getColumnNames(char **columnNames, sqlite3 *database, char
*tableName) {
sqlite3_stmt *statement = NULL;
char sqlStatementString[100];
char *sqlErrorMessage = 0;
char buffer[100];
int status = 0;
int i = 0;
int numOfCols = 0;

// Prepare a statement to get the tables from the database
sprintf(sqlStatementString, "SELECT * FROM '%s';", tableName);
status = sqlite3_prepare_v2(database, sqlStatementString,
strlen(sqlStatementString), , NULL);
if (status != SQLITE_OK) {
fprintf(stderr, "Error occured when attempting to prepare an SQL
statement\n"
"Error:%s\n", sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check

// I only want the first row, because I am only getting the names
// for the columns. This is why I only run the step function once
if (sqlite3_step(statement) != SQLITE_ROW) {
fprintf(stderr, "Either an error occured or table \"%s\" has no
data!\n", tableName);
exit(1);
}

numOfCols = sqlite3_column_count(statement);
if (numOfCols < 1) {
perror("No columns in that table!\n");
exit(1);
}

columnNames = (char **) malloc(numOfCols * sizeof(char *));
if (columnNames == NULL) {
perror("Allocation error: ");
exit(1);
} // end error check for malloc

for (i = 0; i < numOfCols; i++) {
sprintf(buffer,  "%s", (char *) sqlite3_column_name(statement, i));

columnNames[i] = malloc(strlen(buffer));
if (columnNames[i] == NULL) {
perror("Failed to allocate memory for a column name\n");
exit(1);
} // end malloc error check

strcpy(columnNames[i], buffer);
} // end column loop

// Destroy the statement
//  THIS IS WHERE I GET THE SEGMENTATION FAULT **
status = sqlite3_finalize(statement);
if (status != SQLITE_OK) {
fprintf(stderr, "Error occured when attempting to destroy a SQL
statement\n"
"Error:%s\n", sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check

return columnNames;
} // end of function


-- 
Stephen Wood
RMCI, INC.
1525 Perimeter Parkway
Suite 430
Huntsville, AL  35806**
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Nico Williams]

On Tue, May 29, 2012 at 7:33 PM, Igor Tandetnik  wrote:
> On 5/29/2012 8:21 PM, Charles Samuels wrote:
>> Is this a planned feature?
>
>
> Well, it's not a feature, it's a lack thereof. The query planner is not
> specifically looking for this particular optimization opportunity - probably
> because such situations are uncommon and are not worth the time looking for.
> Your question seems to suggest that optimizations magically happen by
> themselves unless someone specifically writes code to block them; in
> reality, it's the other way round.

Actually, anytime you have VIEWs that join table sources you can
benefit from this optimization.

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

Re: [sqlite] Recovering data form an apparently corrupt .db

[Peter Blair]

Thank you all for the help!

On Wed, May 30, 2012 at 1:22 PM, Simon Slavin  wrote:

>
> On 30 May 2012, at 8:11pm, Peter Blair  wrote:
>
> > I need help finding resources to help me recover data from a .db file.
> >
> > The file would be disposable but it contains some memorable and hilarious
> > quotes from my 3 year old daughter that would break my heart to lose.
> >
> > It's a notes.db file from the iPhone Notes application.
> >
> > My apologies if this is entirely the wrong forum to ask such a question.
> >
> > Opening the file in a text editor, I can see much (maybe all?) of the
> > fragmented contents of the note, but opening the file in sqliteman (or
> > other applications), doesn't show any records for the note or associated
> > data.
> >
> > I'm assuming it's perhaps a broken index, missing a record in a
> relational
> > table, or something is just slightly off so that it cannot pull a valid
> > record for the data in the file?
>
> The file may be a SQLite database file which contains only rows which have
> been deleted.  The file still contains lots of text, but no SQLite
> application will be able to read the rows.  It might be simplest to simply
> run strings on the file.  If you have a Mac or a Unix computer type
>
> strings notes.db
>
> or use 'man strings' to find out how the tool works.  There's probably a
> Windows equivalent but I don't know Windows that well.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use with asp.net

[Mike]

On 5/31/12 12:40 AM, Larry Brasfield wrote:

On May 30, Mike wrote:

On 5/29/12 12:02 PM, Mike wrote:
> I'd like to use a SQLite db as a back end for asp.net web pages. Is
> there information somewhere on how to deploy it and set it up?
>
> I gather I need to use system.data.sqlite.dll. That's the part I'm
> most interested in.
>
> ___
> sqlite-users mailing list
> sqlite-users at sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
No one has ever done this?



Thanks, Larry.

Good start on using SQLite on the .NET platform:
   http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki


That seems to be geared toward using .net to develop a desktop 
application. I'm just attempting my first ASP.net web page so I'm not at 
all sure how the reference relates.


It did raise the question of whether system.data.sqlite.dll needs to be 
put on the web server, and if not, what does?


Good start on using a DB with ASP.NET:
https://www.google.com/search?q=asp.net+database+tutorial
This one will take some more study, but it seems like everything I read 
assumes that I'm using Visual Studio, which I'm not. I'm using 
Expression Web. Maybe not the best tool


People have done things close enough to "this" that you should be able 
to take it from here.  Help with programming that is not 
SQLite-specific is kind of off-topic here.  At the abstraction level 
exposed by System.Data.SQLite, you will not have many SQLite-specific 
issues.  For those that do arise, you are likely to find willing help 
here.
If I can get the "for Dummies" version of how to set up the webserver, I 
think I'll be OK. 

Regards,



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

Re: [sqlite] Are update, commit callbacks executed in separate threads ?

[Richard Hipp]

On Thu, May 31, 2012 at 8:36 AM,  wrote:

>
>
> Question:
>
> Are update, commit callbacks executed in separate threads ?
>


Callbacks run in the same thread that provoked the callback.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Are update, commit callbacks executed in separate threads ?

[ext-Esko.Hujanen]

Hi all,

 

We have a memory based database in linux environment.

So there is only one connection to db.

 

Question:

Are update, commit callbacks executed in separate threads ?

 

If so then we must handle the concurrency issues, because we must access
vector variables in callback functions.

 

br Esko

-

Esko Hujanen




Please note: This e-mail may contain confidential information
intended solely for the addressee. If you have received this
e-mail in error, please do not disclose it to anyone, notify
the sender promptly, and delete the message from your system.
Thank you.

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

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Petite Abeille]

On May 31, 2012, at 3:33 AM, Keith Medcalf wrote:

[skip good primer on query rewrite]

> SQL is supposed to be declarative and therefore independent of the skill (or 
> lack thereof) of the query writer.

"In theory there is no difference between theory and practice. In practice 
there is."
-- Yogi Berra, attributed

Double-thinking in SQL
http://explainextended.com/2009/07/12/double-thinking-in-sql/

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