Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

[Simon Slavin]

On 4 Oct 2018, at 10:57am, ldl  wrote:

> Just the same application started in multiple processes doing the same 
> things. The accesses to the db were all readonly.
> 
> 
> I print the error from sqlite3_get_table(). The error is "database is locked".

Set a timeout of 60,000ms and see whether this makes the errors go away:



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

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

[ldl]

Hi Clemens,


Just the same application started in multiple processes doing the same things. 
The accesses to the db were all readonly.


I print the error from sqlite3_get_table(). The error is "database is locked".


Thanks,
Liu






At 2018-09-24 18:27:24, "Clemens Ladisch"  wrote:
>ldl wrote:
>> Multiple applications called sqlite3_get_table() to read data in a same db 
>> file at the same time, got error "database is locked"
>
>Somebody has a write lock.
>
>> Why read-only access is locked?
>
>Because one access is not read only.
>
>> How to resolve the issue?
>
>Find out who locked it.
>Do you know which processess could access the DB file?
>
>
>Regards,
>Clemens
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

[Clemens Ladisch]

ldl wrote:
> Multiple applications called sqlite3_get_table() to read data in a same db 
> file at the same time, got error "database is locked"

Somebody has a write lock.

> Why read-only access is locked?

Because one access is not read only.

> How to resolve the issue?

Find out who locked it.
Do you know which processess could access the DB file?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_get_table() failed with error "database is locked"

[ldl]

Hi,


Multiple applications called sqlite3_get_table() to read data in a same db file 
at the same time, got error "database is locked"? Why read-only access is 
locked? How to resolve the issue?


Thanks,
Liu
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_table() vs sqlite3_step

[Tito Ciuro]

Hi Igor,

No, no... I really meant a roundtrips to disk. So, while sqlite3_get_table() 
seemed like a faster but memory hungry mechanism to retrieve the result set, 
sqlite3_step() seemed that it would take longer, especially in disk-based 
systems, but more memory-efficient.

Thanks for pointing to me that sqlite3_get_table() is not faster than 
sqlite3_step().

-- Tito

On 21 Nov 2010, at 14:14, Igor Tandetnik  wrote:

> Tito Ciuro  wrote:
>> Let me start by saying that I'm aware that sqlite3_get_table() should not be 
>> used (as per the documentation). I'm curious about
>> one thing though: if the computer/device has sufficient memory to hold the 
>> result set returned by sqlite3_get_table(), wouldn't
>> it be more optimized performance-wise than calling sqlite3_step N times to 
>> obtain the same result set?  
> 
> sqlite3_get_table is just a wrapper around sqlite3_prepare/sqlite3_step.
> 
>> Sounds like sqlite3_get_table() would take less time to access the storage 
>> subsystem as opposed to sqlite_step() with multiple
>> roundtrips
> 
> You say "roundtrip" as if it's a network request to some remote machine or 
> something. Here, a "roundtrip" is a function call within the same process. In 
> this sense, a call to malloc is a roundtrip to memory allocation subsystem - 
> you want to avoid those at all costs, right?
> -- 
> Igor Tandetnik
> 
> ___
> 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] sqlite3_get_table() vs sqlite3_step

[Tito Ciuro]

Thanks a lot Max!

-- Tito

On 21 Nov 2010, at 14:04, Max Vlasov  wrote:

> On Sun, Nov 21, 2010 at 6:31 PM, Tito Ciuro  wrote:
> 
>> Hello,
>> 
>> Sounds like sqlite3_get_table() would take less time to access the storage
>> subsystem as opposed to sqlite_step() with multiple roundtrips, at the
>> expense of using lots more RAM, of course. So assuming RAM wasn't an issue,
>> why not use sqlite3_get_table()? Why is its usage being discouraged?
>> 
>> 
>> Tito, looking at the sources... sqlite3_get_table seems to be sqlite3_exec
> call with a callback and sqlite3_exec is sqlite3_prepare/sqlite3_step
> wrapper. So, probably no special access or performance boost
> 
> Max Vlasov
> ___
> 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] sqlite3_get_table() vs sqlite3_step

[Igor Tandetnik]

Tito Ciuro  wrote:
> Let me start by saying that I'm aware that sqlite3_get_table() should not be 
> used (as per the documentation). I'm curious about
> one thing though: if the computer/device has sufficient memory to hold the 
> result set returned by sqlite3_get_table(), wouldn't
> it be more optimized performance-wise than calling sqlite3_step N times to 
> obtain the same result set?  

sqlite3_get_table is just a wrapper around sqlite3_prepare/sqlite3_step.

> Sounds like sqlite3_get_table() would take less time to access the storage 
> subsystem as opposed to sqlite_step() with multiple
> roundtrips

You say "roundtrip" as if it's a network request to some remote machine or 
something. Here, a "roundtrip" is a function call within the same process. In 
this sense, a call to malloc is a roundtrip to memory allocation subsystem - 
you want to avoid those at all costs, right?
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3_get_table() vs sqlite3_step

[Max Vlasov]

On Sun, Nov 21, 2010 at 6:31 PM, Tito Ciuro  wrote:

> Hello,
>
> Sounds like sqlite3_get_table() would take less time to access the storage
> subsystem as opposed to sqlite_step() with multiple roundtrips, at the
> expense of using lots more RAM, of course. So assuming RAM wasn't an issue,
> why not use sqlite3_get_table()? Why is its usage being discouraged?
>
>
> Tito, looking at the sources... sqlite3_get_table seems to be sqlite3_exec
call with a callback and sqlite3_exec is sqlite3_prepare/sqlite3_step
wrapper. So, probably no special access or performance boost

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

[sqlite] sqlite3_get_table() vs sqlite3_step

[Tito Ciuro]

Hello,

Let me start by saying that I'm aware that sqlite3_get_table() should not be 
used (as per the documentation). I'm curious about one thing though: if the 
computer/device has sufficient memory to hold the result set returned by 
sqlite3_get_table(), wouldn't it be more optimized performance-wise than 
calling sqlite3_step N times to obtain the same result set?

Sounds like sqlite3_get_table() would take less time to access the storage 
subsystem as opposed to sqlite_step() with multiple roundtrips, at the expense 
of using lots more RAM, of course. So assuming RAM wasn't an issue, why not use 
sqlite3_get_table()? Why is its usage being discouraged?

Thanks,

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

Re: [sqlite] sqlite3_get_table and probable memory leak

[Maciej Miszczak]

Thanks, Pavel, for your all-embracing answer. I'll dig in this topic.

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

Re: [sqlite] sqlite3_get_table and probable memory leak

[Pavel Ivanov]

> Is lowering the PRAGMA cache_size a good way to fix this?

Yes, it's the only way to fix this. Though IIRC you cannot lower it
below 10, so 10 pages will be always stored in memory. OTOH you can
implement your own version of page cache that will not leave pages in
memory at all (for more information see
http://www.sqlite.org/c3ref/pcache_methods.html).

> I see now I have probably missed some basic rules
> of using SQLite. I just created a Database class with constructor
> opening a database connection and storing a sqlite3* pointer and
> destructor closing this connection.

I think you've implemented it correctly. I doubt you want to open and
close connection for executing of each statement because it's some
serious overhead.

Pavel

On Thu, Jul 30, 2009 at 8:25 AM, Maciej
Miszczak wrote:
> Pavel, you're great! There's nothing better than to just ask the right
> people. Calling sqlite3_close after the mentioned function solved the
> problem. By the way I got something like this:
>> - 0x0005e688 Free 28 was never alloc'd 0x400f3ec4
>> - 0x0005e750 Free 29 was never alloc'd 0x400f3ec4
>> - 0x0005e768 Free 30 was never alloc'd 0x400f3ec4
>> - 0x0005e788 Free 31 was never alloc'd 0x400f3ec4
> and so on, but I think there's nothing to worry about. My database has
> the page_size of 1024. I see now I have probably missed some basic rules
> of using SQLite. I just created a Database class with constructor
> opening a database connection and storing a sqlite3* pointer and
> destructor closing this connection. In this situation every
> Database::execute call produced, as you said, not freed cache. It seems
> to be the cause of all my problems with "leaks". Is lowering the PRAGMA
> cache_size a good way to fix this? My database works on embedded
> platform with 64MB of RAM.
>
> Thanks again, cheers,
> Maciej
> ___
> 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] sqlite3_get_table and probable memory leak

[Maciej Miszczak]

Pavel, you're great! There's nothing better than to just ask the right 
people. Calling sqlite3_close after the mentioned function solved the 
problem. By the way I got something like this:
> - 0x0005e688 Free 28 was never alloc'd 0x400f3ec4
> - 0x0005e750 Free 29 was never alloc'd 0x400f3ec4
> - 0x0005e768 Free 30 was never alloc'd 0x400f3ec4
> - 0x0005e788 Free 31 was never alloc'd 0x400f3ec4
and so on, but I think there's nothing to worry about. My database has 
the page_size of 1024. I see now I have probably missed some basic rules 
of using SQLite. I just created a Database class with constructor 
opening a database connection and storing a sqlite3* pointer and 
destructor closing this connection. In this situation every 
Database::execute call produced, as you said, not freed cache. It seems 
to be the cause of all my problems with "leaks". Is lowering the PRAGMA 
cache_size a good way to fix this? My database works on embedded 
platform with 64MB of RAM.

Thanks again, cheers,
Maciej
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_get_table and probable memory leak

[Maciej Miszczak]

Hi,

I have a simple function (used to get only one value from a database), 
as shown below, and I've struggled with it for a long time, without 
success. I use libsqlite3.6.13.
> int Database::execute(std::string query, std::string* result, 
> std::string* errMsg)
> {
> int rc, tmpRowsCount, tmpColsCount;
> char* zErrMsg;
> char** tmpRes;
> ostringstream tmp;
>
> OS.logger->log(string("Executing query: ") + query, LOG);
>
> rc = sqlite3_get_table(db, query.c_str(), , , 
> , );
> if( rc!=SQLITE_OK )
> {
> tmp << "SQL error: " << zErrMsg;
> if (errMsg != NULL)
> {
> errMsg->assign(zErrMsg);
> sqlite3_free(zErrMsg);
> }
> OS.logger->log(tmp.str(), ERR);
> tmp.str("");
> sqlite3_free_table(tmpRes);
> return -1;
> }
>
> if ((tmpRowsCount != 1) || (tmpColsCount != 1)) // TODO: check row 
> count
> {
> tmp << "Invalid number of rows (" << tmpRowsCount << ") or 
> columns (" << tmpColsCount << ")";
> OS.logger->log(tmp.str(), ERR);
> tmp.str("");
> sqlite3_free_table(tmpRes);
> return -2; // TODO: define error
> }
>
> if (result != NULL)
> result->assign(tmpRes[1]);
>
> sqlite3_free_table(tmpRes);
>
> return 0;
> }
The problem is that after calling above function i get from mtrace:
> Memory not freed:
> -
>Address Size Caller
> 0x0005f9d0  0xf  at 0x401e8ef8
> 0x000628280x490  at 0x4012c920
Not freed 0xf at 0x0005f9d0 is OK, because it's caused by 
result->assign(tmpRes[1]). What I'm confused with is the 0x490 of not 
freed memory.

Here is raw mtrace log:
> = Start
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61310 0x56
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61370 0x1e
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61398 0x67
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61398
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61370
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fbe8 0x58
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x658
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61370 0xe0
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62e88 0x408
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x230
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62828
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x160
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x490
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fe00 0x20
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5f9b8 0x10
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62e88
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61370
> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] < 0x5fbe8
> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] > 0x5fbe8 0x18
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x5f9d0 0xf
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fe00
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5f9b8
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fbe8
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61310
> = End
sqlite3_free_table is called for sure, because if I comment it, I get:
> Memory not freed:
> -
>Address Size Caller
> 0x0005f9b8 0x10  at 0x4012c920
> 0x0005f9d0  0xf  at 0x401e8ef8
> 0x000601a8 0x18  at 0x4012c8ec
> 0x00060208 0x20  at 0x4012c920
> 0x000628280x490  at 0x4012c920
Thanks in advance for any hints.

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

Re: [sqlite] sqlite3_get_table question

[schachtobi]

Hi,

thanks Dan. This was certainly the problem, since there are a lot of
zeros in the data.

In the meahnwhile I tried out something different. I heard from the
incremental blob API. And it worked!!!

Here is how I solved it:

if(sqlite3_prepare_v2(db, cmd, strlen(cmd), , 0) != SQLITE_OK)
{
  printf("Could not prepare statement.\n");
  return;
}

rc = sqlite3_step(pStmt);
if( rc==SQLITE_ROW )
{
  newBlobSize = sqlite3_column_bytes(pStmt, 2);
  if(newBlobSize > blobSize || blobData == NULL)
  {
printf("(re)allocating buffer\n");
if(blobData != NULL)
  free(blobData);

blobData = malloc(newBlobSize);
blobSize = newBlobSize; 
  }
  memcpy(blobData, sqlite3_column_blob(pStmt, 2), newBlobSize);
}

Now I only need to solve a speed problem, but this I will first try out
by myself:-)

Thanks for your help

Tobias

>>> Date: Mon, 29 Dec 2008 11:20:58 +0700
>>> From: Dan <danielk1...@gmail.com>
>>> Subject: Re: [sqlite] sqlite3_get_table question
>>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>>> Message-ID: <65fa0cc6-4248-4675-8100-f069423e0...@gmail.com
>>
>>> Why (ncol+2)?
>>
>> I make the following select statement:
>>
>> SELECT fname, dsize, data, ABS(red-%d)+ABS(green-%d)+ABS(blue-%d) err
>>  FROM ch ORDER BY err LIMIT 1;
>>
>> If I want to have the data then this would be 2 + the number of  
>> heading
>> columns (ncol)
> 
> Right. Obviously.
> 
> Note that sqlite3_get_table() won't work for data with embedded 0x00  
> bytes.
> It will truncate each value at the first one encountered. That could be
> the problem.

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

Re: [sqlite] sqlite3_get_table question

[Dan]

On Dec 30, 2008, at 1:29 AM, schachtobi wrote:

>
>> Date: Mon, 29 Dec 2008 11:20:58 +0700
>> From: Dan <danielk1...@gmail.com>
>> Subject: Re: [sqlite] sqlite3_get_table question
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Message-ID: <65fa0cc6-4248-4675-8100-f069423e0...@gmail.com
>
>
>> Why (ncol+2)?
>
>
> I make the following select statement:
>
> SELECT fname, dsize, data, ABS(red-%d)+ABS(green-%d)+ABS(blue-%d) err
>  FROM ch ORDER BY err LIMIT 1;
>
> If I want to have the data then this would be 2 + the number of  
> heading
> columns (ncol)

Right. Obviously.

Note that sqlite3_get_table() won't work for data with embedded 0x00  
bytes.
It will truncate each value at the first one encountered. That could be
the problem.

>
>
> The database structure is like the following:
>
> CREATE TABLE IF NOT EXISTS ch(
>   fname TEXT UNIQUE,
>   red INTEGER,
>   green INTEGER,
>   blue INTEGER,
>   dsize INTEGER,
>   data BLOB);
>
> Does anybody sees a possible solution for accessing the data (blob)  
> for
> indexes (rgb) higher than 4k Pixels?
>
> Regards,
> Tobias
>
> ___
> 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] sqlite3_get_table question

[schachtobi]

> Date: Mon, 29 Dec 2008 11:20:58 +0700
> From: Dan <danielk1...@gmail.com>
> Subject: Re: [sqlite] sqlite3_get_table question
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Message-ID: <65fa0cc6-4248-4675-8100-f069423e0...@gmail.com


> Why (ncol+2)?


I make the following select statement:

SELECT fname, dsize, data, ABS(red-%d)+ABS(green-%d)+ABS(blue-%d) err
  FROM ch ORDER BY err LIMIT 1;

If I want to have the data then this would be 2 + the number of heading
columns (ncol)

The database structure is like the following:

CREATE TABLE IF NOT EXISTS ch(
fname TEXT UNIQUE,
red INTEGER,
green INTEGER,
blue INTEGER,
dsize INTEGER,
data BLOB);

Does anybody sees a possible solution for accessing the data (blob) for
indexes (rgb) higher than 4k Pixels?

Regards,
Tobias

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

Re: [sqlite] sqlite3_get_table question

[Dan]

On Dec 29, 2008, at 7:57 AM, schachtobi wrote:

> Hi,
>
> I retrieve data (1 row only) via the handy getTable function. Inside
> this row is one blob object with pixeldata.
>
> The size is:
> 256*256 Pixels with 3 bytes each
> -> 256*256*3 = 196608 Bytes
>
> select length(data) from ch;
>
> gives me exactly this number.
>
> No I try this:
>
> typedef struct
> {
>   unsigned char red;
>   unsigned char green;
>   unsigned char blue;
> }Pixel;
>
> Pixel *dbData;
> Pixel *pSrcP;
>
> rc = sqlite3_get_table(db, cmd, ,
>   ,
>   ,
>   
>   );
>
> if(rc != SQLITE_OK || nrow!= 1 || zErrMsg!=NULL)
> {
>   printf("Error in getting data from the db\n");
>   return;
> }
> dbData = (Pixel *) result[ncol+2];

Why (ncol+2)?

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

[sqlite] sqlite3_get_table question

[schachtobi]

Hi,

I retrieve data (1 row only) via the handy getTable function. Inside
this row is one blob object with pixeldata.

The size is:
256*256 Pixels with 3 bytes each
-> 256*256*3 = 196608 Bytes

select length(data) from ch;

gives me exactly this number.

No I try this:

typedef struct
{
unsigned char red;
unsigned char green;
unsigned char blue;
}Pixel;

Pixel *dbData;
Pixel *pSrcP;

rc = sqlite3_get_table(db, cmd, ,
,
,

);

if(rc != SQLITE_OK || nrow!= 1 || zErrMsg!=NULL)
{
printf("Error in getting data from the db\n");
return;
}
dbData = (Pixel *) result[ncol+2];

pSrcP = &(dbData[4096]);

This should be well below the limit of 256*256=65536 pixels, but i get
an segmentation fault now:
r = pSrcP->red;


So now this limit (4096) is probaly the limit for near pointers on
win32, right? What way would be the best to access the whole data inside
the table, is there a way to get the data in chunks?

My compiler  is mingw32

Thank you for any hint,
Tobias
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_table only get 16 rows

[James]

Sorry, I have already solved!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of James
Sent: Tuesday, March 11, 2008 4:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3_get_table only get 16 rows

Hi,

  I use command-line to query the table:

# sqlite3 listtable

 sqlite> select Name,Value from TblDeviceInfo;

 AdditionalHardwareVersion

AdditionalSoftwareVersion

Description

DeviceLog

DeviceStatus

EnabledOptions

FirstUseDate

HardwareVersion01B

Manufacturer   III

ManufacturerOUI001A2A

ModelName

ProductClass   Speedstrea

ProvisioningCode   000.000.00

SerialNumber   A1

SoftwareVersion1.09.000

UpTime



  I write a program:

 
===

  #include 

#include 

 

int main(int argc, char **argv){

  sqlite3 *db;

  char *zErrMsg = 0;

  int rc;

  char **result;

  int nrow, ncol, i;

 

  rc = sqlite3_open("listtable", );

  if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

  }

 

  rc = sqlite3_get_table(db, select Name,Value from TblDeviceInfo", ,
, , );

  if( rc!=SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

sqlite3_free_table(result);

return -1;

  }

 

  fprintf(stderr, "Row = %d, Col = %d\n", nrow, ncol);

  for (i = 0 ; i < nrow ; ++i)

fprintf(stderr,"%s\n", result[i]);

 

  sqlite3_free_table(result);

  sqlite3_close(db);

 

  return 0;

===

The output is

Row = 16, Col = 2

Name

Value

AdditionalHardwareVersion



AdditionalSoftwareVersion

 

Description

 

DeviceLog

 

DeviceStatus

 

EnabledOptions

 

FirstUseDate

 

I don't know why I just get the 16 rows of data. I expect to get the 34 rows
of data.

What should I do?

Thank you.

 

___
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] sqlite3_get_table only get 16 rows

[James]

Hi,

  I use command-line to query the table:

# sqlite3 listtable

 sqlite> select Name,Value from TblDeviceInfo;

 AdditionalHardwareVersion

AdditionalSoftwareVersion

Description

DeviceLog

DeviceStatus

EnabledOptions

FirstUseDate

HardwareVersion01B

Manufacturer   III

ManufacturerOUI001A2A

ModelName

ProductClass   Speedstrea

ProvisioningCode   000.000.00

SerialNumber   A1

SoftwareVersion1.09.000

UpTime



  I write a program:

 
===

  #include 

#include 

 

int main(int argc, char **argv){

  sqlite3 *db;

  char *zErrMsg = 0;

  int rc;

  char **result;

  int nrow, ncol, i;

 

  rc = sqlite3_open("listtable", );

  if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

  }

 

  rc = sqlite3_get_table(db, select Name,Value from TblDeviceInfo", ,
, , );

  if( rc!=SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

sqlite3_free_table(result);

return -1;

  }

 

  fprintf(stderr, "Row = %d, Col = %d\n", nrow, ncol);

  for (i = 0 ; i < nrow ; ++i)

fprintf(stderr,"%s\n", result[i]);

 

  sqlite3_free_table(result);

  sqlite3_close(db);

 

  return 0;

===

The output is

Row = 16, Col = 2

Name

Value

AdditionalHardwareVersion



AdditionalSoftwareVersion

 

Description

 

DeviceLog

 

DeviceStatus

 

EnabledOptions

 

FirstUseDate

 

I don't know why I just get the 16 rows of data. I expect to get the 34 rows
of data.

What should I do?

Thank you.

 

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

Re: [sqlite] sqlite3_get_table

[Dan]

On Mar 6, 2008, at 10:57 AM, Kenneth LO wrote:

> In previous versions (<=3.4.10) I use to do if (errmsg) { free(errmsg)
> ; } without problem.
>
> In 3.5.6 the free() will segfault (yes, even after if) with a "invalid
> pointer" message.
>
> It's my old understanding that sqlite3_get_table (previously
> sqlite_get_table) will malloc for *errmsg and I'll need to do the
> free().  Has anything changed?  I'm just recompiling old codes with
> the new lib.

Technically you're supposed to use sqlite3_free(). They might be
different, depending on compilation options.

Dan.





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

[sqlite] sqlite3_get_table

[Kenneth LO]

In previous versions (<=3.4.10) I use to do if (errmsg) { free(errmsg)
; } without problem.

In 3.5.6 the free() will segfault (yes, even after if) with a "invalid
pointer" message.

It's my old understanding that sqlite3_get_table (previously
sqlite_get_table) will malloc for *errmsg and I'll need to do the
free().  Has anything changed?  I'm just recompiling old codes with
the new lib.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Dennis Cote]

Jerry Krinock wrote:
Is there any way to get a list of all column names in a table via the 
C API, which works even when there are no data rows in the table?




Jerry,

You can get the column names for any query, including a "select * from 
table", using the prepared statement APIs.


After you prepare a query using sqlite3_prepare(), you can call 
sqlite3_column_count() to get the number of columns that will be 
returned, and then loop calling sqlite3_column_name() to get the columns 
names. Note, you do *not* need to execute the query to get this 
information, so it doesn't matter if the table is empty or not. After 
you have the column names you can simply finalize the prepared statement.


See http://www.sqlite.org/c3ref/prepare.html, 
http://www.sqlite.org/c3ref/column_count.html,  and 
http://www.sqlite.org/c3ref/column_name.html for more details.


HTH
Dennis cote




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

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

On 2008 Jan, 02, at 15:52, Jay Sprenkle wrote:


Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..


Thank you, Jay.  I kept getting syntax errors when I tried that;  
apparently I don't know how to query the sqlite_master.  But while  
thinking about it I realized that pragma SQL can be executed by the C  
API.  So I did this, and it worked.


Jerry

(It's Objective-C code but anyone who's interested should be able to  
get the idea.)


- (NSArray*)allColumnNamesInTableNamed:(NSString*)tableName {
// Will return nil if fails, empty array if no columns
void* db = [self db] ;  // database, a class instance variable
char* errMsg = NULL ;
int result ;

NSString* statement ;
statement = [[NSString alloc] initWithFormat:@"pragma  
table_info(%@)", tableName] ;

char** results ;
int nRows ;
int nColumns ;
result = sqlite3_get_table(
   db,/* An open database */
   [statement UTF8String], /* SQL to be  
executed */
   ,  /* Result is in char *[]  
that this points to */
   ,/* Number of result rows  
written here */
   , /* Number of result columns  
written here */

   /* Error msg written here */
) ;

[statement release] ;

NSMutableArray* columnNames = nil ;
if (!(result == SQLITE_OK)) {
// Invoke the error handler for this class
[self showError:errMsg from:16 code:result] ;
sqlite3_free(errMsg) ;
}
else {
int j ;
for (j=0; j

Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

[Jay Sprenkle]

Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..

On Jan 2, 2008 4:52 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote:
> Is there any way to get a list of all column names in a table via the
> C API, which works even when there are no data rows in the table?
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

[sqlite] sqlite3_get_table(); How to get all column names in C?

[Jerry Krinock]

Is there any way to get a list of all column names in a table via the  
C API, which works even when there are no data rows in the table?


The example given for sqlite3_get_table() indicates that the column  
names are returned as the "zeroth" row.  Indeed, it states:


"In general, the number of values inserted into azResult will be  
((*nrow) + 1)*(*ncolumn)."


However, I when the table has no data rows, the returned ncolumn=0, so  
the expression evaluates to 0 and indeed I get 0 values.


Thanks,

Jerry Krinock

(Using sqlite 3.4.0, as shipped in Mac OS X version 10.5)

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

[sqlite] sqlite3_get_table question

[Martin Pelletier]

Hello,

I am using an SQLite to host data for various projects, some of which 
take up significant place with some large monolithic tables (around 750 
MB). Querying goes through the DLL using a call to SQLite3_get_table. 
The function allocates memory space to record the table in chars, even 
if the original field is numeric. Is there a similar function that would 
place the result of a query in memory just like SQL3_get_table but their 
stored type (integer, float, etc), with perhaps a header to indicate 
what type the data should be read as? Also, could this data be arrayed? 
It would seem that reading an array from memory of length n would be 
faster than reading separately n pointers.


This could have an impact as the language I am using that calls the 
SQLite3.dll is an interpreted language (www.jsoftware.com), which has to 
re-evaluate all the strings to put them back in integer or float type, 
an operation which could be considered a loss in performance.


Regards,

Martin

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

[sqlite] sqlite3_get_table and non-strings

[Mark Richards]

Although it does not appear to be mentioned in the documentation, is it 
correct to assume that sqlite3_get_table can only handle string datatypes?


In a particular problem I am working a query is made to return an 
integer and three string fields.


The issue may have more to do with the wrapper I am using (e_sqlite.c) 
which defines a structure to hold the data with a char fieldtype for 
each column.


It does this in a loop following the call:
recordset[i][j] = (char *) malloc( (strlen(result[count]) + 1) );
strcpy(recordset[i][j], result[count]);

So the result data is assumed to always be a string type.

Is sqlite3_get_table intended to work properly if it is I passed a 
structure that represents the data I want it to return?  It would be 
great if someone might point me to an example of sqlite3_get_table that 
works with different field types.



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

Re: [sqlite] sqlite3_get_table fails in SQL Lite version 3.0.7, sqlite_get_table works in SLQ Lite Version 2.8.15 -

[Paolo Vernazza]

Steve Frierdich wrote:
In the Sqlite version 2.8.15 I used the sqlite_get_table function that 
works with a return value of SQLITE_OK.

In the Sqlite version 3.0.7 I used the sqlite3_get_table function that 
fails with a return value of  26, which is:
"File opened that is not a database file ".

Both version used the same database. Why does one function work in the 
one version and the same type of function function fail in another? Do 
I now have to go through and recreate all the databases used in sqlite 
version  2.x.xx in a new sqlite explorer?

Thanks
Steve
SQLite2 and SQLite3 use a different file format,
so SQLite3 cannot read SQLite2 files
and SQLite2 cannot read SQLite3 files.
Use the SQLite2 command line to dump your table:
sqlite2 yourdbfile .dump >dump.sql
then import your dump.sql in a new sqlite3db
sqlite3 yournewdb .read "dump.sql"

[sqlite] sqlite3_get_table fails in SQL Lite version 3.0.7, sqlite_get_table works in SLQ Lite Version 2.8.15 -

[Steve Frierdich]

In the Sqlite version 2.8.15 I used the sqlite_get_table function that 
works with a return value of SQLITE_OK.

In the Sqlite version 3.0.7 I used the sqlite3_get_table function that 
fails with a return value of  26, which is:
"File opened that is not a database file ".

Both version used the same database. Why does one function work in the 
one version and the same type of function function fail in another? Do I 
now have to go through and recreate all the databases used in sqlite 
version  2.x.xx in a new sqlite explorer?

Thanks
Steve
Steve Frierdich wrote:
The same databases that are recognized and work in Sqlite version 
2.8.15 are not recognized in Sqlite version 3.0.7.  When the sqlite 
function sqlite3_get_table is called using version 3.0.7 the result is 
26 which is:

File opened that is not a database file
When the same function is used in version 2.8.15 the result is 0 which 
is:
SQLITE_OK

Can some one tell me why the function work sin one version and not the 
other.

Sqlite is a Win32 DLL and the calling application is a Win32 
application. Both the DLL and the Win32 application was created using 
Visual Studio.

Steve
MacLeod, Kenneth wrote:
Changing a process wide variable like that seems a bit drastic. I'd be
concerned about what effect that might have on all the other things 
running
under iis.  For example, some of them might be running as different 
users
who might not have access to the path.

Thanks.
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: 07 October 2004 13:56
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] sqlite3_temp_directory
MacLeod, Kenneth wrote:
 

Is there any way to change the sqlite3_temp_directory global?  I'm 
running
sqlite under iis and the user doesn't have write access to their temp
directory.

  

On windows you can leave sqlite3_temp_directory alone (leave it as NULL)
and set the TMP environment variable to the directory you want to use.