Re: [sqlite] Getting the no of rows using count(*)

[Bharath Booshan L]

Hello Kirrthana,

> should i use prepare and step in this case and where the result of the query
> will be stored on executing my c code.

  retValue = sqlite3_prepare( dataBaseConnection ,sqlQuery,-1, ,0);

if( retValue != SQLITE_BUSY && (retValue = sqlite3_step( ppStmt ) ==
SQLITE_ROW )
{
   numOfRows  =sqlite3_column_int64(ppStmt ,0);

}
  
sqlite3_finalize( ppStmt );


This should do it

--
Bharath

On 1/31/08 11:00 AM, "kirrthana M" <[EMAIL PROTECTED]> wrote:

> Hi all,
> 
> Im using the query Select count(*) from table to get the no of rows in the
> table in my c code.
> In the command line it will print the no of rows ,but in my c code how can i
> get the result,
> should i use prepare and step in this case and where the result of the query
> will be stored on executing my c code.
> 
> Regards
> Kirrthana
> 
> The information contained in this electronic message and any attachments to
> this message are intended for the exclusive use of the addressee(s) and may
> contain proprietary, confidential or privileged information. If you are not
> the intended recipient, you should not disseminate, distribute or copy this
> e-mail. Please notify the sender immediately and destroy all copies of this
> message and any attachments contained in it.
> 
> Contact your Administrator for further information.
> 



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.



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

[sqlite] Getting the no of rows using count(*)

[kirrthana M]

Hi all,

Im using the query Select count(*) from table to get the no of rows in the
table in my c code.
In the command line it will print the no of rows ,but in my c code how can i
get the result,
should i use prepare and step in this case and where the result of the query
will be stored on executing my c code.

Regards
Kirrthana

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.

Contact your Administrator for further information.

Re: [sqlite] CREATE VIEW or CREATE TEMP TABLE

[Alexander Batyrshin]

I got better performance when started to use TEMP tables with
temp_store = MEMORY.
But everything depends on situation.


-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

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

[sqlite] SQLite website outage & mailing list upgrade, 2008-02-02

[drh]

We are going to be upgrading the server that runs
the SQLite website this comming Saturday, if all goes
according to plan.  We are planning to changes the
operating system from Debian to Ubuntu and the
mailing list manager from ezmlm to GNU mailman.

If everything goes as planned, the outage will only
last a few minutes.  And we expect to have all mailing
list users transferred over to the new mailing list
manager automatically.  But, of course, these things
rarely go according to plan

If you desparately need to access the on-line docs
during the outage, visit the back-up site:

   http://www.hwaci.com/sw/sqlite/

If you mysteriously stop getting messages from this
mailing list on Saturday, then you might have been
dropped accidently.  Poke around on the website and
figure out how to sign up again.  (I'd tell you how
to do that in this email message, but I don't know
yet myself.)

I'm told that GNU mailman will work much better for
us than ezmlm.  We should soon add new mailing lists
like sqlite-dev and sqlite-announce, assuming everything
goes well.  Stay tuned (or visit the website) for
additional announcements.

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


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

Re: [sqlite] Number of columns in table

[Dennis Cote]

Andreas Volz wrote:

Hello,

It's simple to the the maximum number of rows in a table with 


SELECT count(*) FROM table

But how to find out the number of columns for a query like:

SELECT * FROM table

My practical problem is the read callback function:

int readCallback (void *data, int argc, char **argv, 
  char **azColName) 
{

  int i;

  for(i=0; i

Re: [sqlite] Adding data to the end of a column

[jose isaias cabrera]

"P Kishor" wrote...



On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:

Punkish,

I don't want to replace it.  I want to add to the end of field.  So, I 
did

not know that I could do,

UPDATE table SET field = field && 'data to be added' WHERE id = 55;


first, you should use || as the string concat operator, although &&
might also work. I haven't tried it though.

That said, I hate to tell you that whether you do

field = field || 'data to be added'

or given

field = 'value'

you do

field = 'value with more text'

you are replacing the value of the field, and that was what I was
trying to illustrate. I should have really said

field = field || ' with more text'


Ok, just to complete this thread, this works,

UPDATE table SET field = field || 'data to be added' WHERE id = 55

thanks.

josé



So, I think this should do it.  I will give it a try and get back to the
group.

thanks all.

josé

- Original Message -
From: "P Kishor" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, January 30, 2008 2:59 PM
Subject: Re: [sqlite] Adding data to the end of a column


> replace the field with new value.
>
> UPDATE table SET field = 'value with more data' WHERE field = 'value'
>
> On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> Greetings.
>>
>> Is there a way to add data to the end of a field?  I know I select 
>> that
>> field, import the data into a variable and then add whatever to the 
>> end.

>> What I am trying to do is exactly NOT that. :-)  So, instead, just add
>> say a
>> line or two of text to a field.
>>
>> Is this possible?
>>
>> thanks,
>>
>> josé
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>
>>
>
>



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





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

Re: [sqlite] Implementation of TPCC benchmark

[Steve VanDeBogart]

On Wed, 30 Jan 2008, Steve VanDeBogart wrote:


SQLite-ers,

OSDL-DBT is a set of TPC like benchmarks implemented by OSDL
(http://osdldbt.sourceforge.net/).  I've added SQLite support to the DBT2 
benchmark (TPCC like).  The patch is attached in case anyone is

interested in running a TPCC like benchmark on SQLite.


Sorry, the patch can be obtained from here:
http://cs.ucla.edu/~vandebo/dbt2-sqlite.patch

--
Steve

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

[sqlite] Number of columns in table

[Andreas Volz]

Hello,

It's simple to the the maximum number of rows in a table with 

SELECT count(*) FROM table

But how to find out the number of columns for a query like:

SELECT * FROM table

My practical problem is the read callback function:

int readCallback (void *data, int argc, char **argv, 
  char **azColName) 
{
  int i;

  for(i=0; i

[sqlite] Implementation of TPCC benchmark

[Steve VanDeBogart]

SQLite-ers,

OSDL-DBT is a set of TPC like benchmarks implemented by OSDL
(http://osdldbt.sourceforge.net/).  I've added SQLite support to the DBT2 
benchmark (TPCC like).  The patch is attached in case anyone is

interested in running a TPCC like benchmark on SQLite.

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

Re: [sqlite] FTS3 Unicode support

[Myk Melez]

Scott Hess wrote:

The [3] status is ... pending, sorry :-(.  But it is more along the
lines of adding stuff to ICU rather than adding ICU-less stuff to
SQLite, so it sounds like that is not relevant to what you're doing.
  

Hi Scott,

Thanks for the info.  Indeed, enhancements to ICU don't sound like the 
right approach for us.  I'll look into implementing an alternate tokenizer.


-myk


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

Re: [sqlite] How to make correct transaction use only SQL?

[Alexander Batyrshin]

Thank you =) This is good idea :)

On Jan 30, 2008 2:44 PM, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
>
> Instead of piping the sql into sqlite3.exe, use the ".read" command instead.
>
>
> C:\Temp\s>sqlite3 test.dat
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> .read test.sql
> SQL error near line 10: column id is not unique
> SQL error near line 12: cannot commit - no transaction is active
> sqlite> select * from t1;
> sqlite>
>
> .read aborts on first error, whereas piping doesn't know to do that (and
> presumably there's no way it could know).
>
> HTH,
>
> Sam
>
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building Flex
> based products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
>
> -Original Message-
> From: Alexander Batyrshin [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 29, 2008 9:37 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] How to make correct transaction use only SQL?
>
> For example i have this tabe:
>
> CREATE TABLE t1 (
>   id int unique ON CONFLICT ROLLBACK,
>   val char
> );
>
> And I have to execute this sql file:
>
> BEGIN TRANSACTION;
> INSERT INTO t1 (id, val) VALUES(1, 'val1');
> INSERT INTO t1 (id, val) VALUES(2, 'val2');
> INSERT INTO t1 (id, val) VALUES(3, 'val3');
> INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT
> INSERT INTO t1 (id, val) VALUES(4, 'val5');
> COMMIT;
>
> If we execute this sql file, only INSERT before CONFLICT case will be
> rollback-ed, but last one still will be executed and remains in
> database...
>
> cat test.sql | sqlite3 test.db
> SQL error near line 11: column id is not unique
> SQL error near line 13: cannot commit - no transaction is active
> $ sqlite3 test.db
> SQLite version 3.4.0
> sqlite> select * from t1;
> 4|val5
>
>
> I what that on conflict _whole_ transaction will ROLLBACK and state of
> database will be exactly like at moment of execution "BEGIN
> TRANSACTION". How it is possible using only SQL?
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

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

Re: [sqlite] Adding data to the end of a column

[P Kishor]

On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
> Punkish,
>
> I don't want to replace it.  I want to add to the end of field.  So, I did
> not know that I could do,
>
> UPDATE table SET field = field && 'data to be added' WHERE id = 55;

first, you should use || as the string concat operator, although &&
might also work. I haven't tried it though.

That said, I hate to tell you that whether you do

field = field || 'data to be added'

or given

field = 'value'

you do

field = 'value with more text'

you are replacing the value of the field, and that was what I was
trying to illustrate. I should have really said

field = field || ' with more text'



>
> So, I think this should do it.  I will give it a try and get back to the
> group.
>
> thanks all.
>
> josé
>
> - Original Message -
> From: "P Kishor" <[EMAIL PROTECTED]>
> To: 
> Sent: Wednesday, January 30, 2008 2:59 PM
> Subject: Re: [sqlite] Adding data to the end of a column
>
>
> > replace the field with new value.
> >
> > UPDATE table SET field = 'value with more data' WHERE field = 'value'
> >
> > On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
> >>
> >> Greetings.
> >>
> >> Is there a way to add data to the end of a field?  I know I select that
> >> field, import the data into a variable and then add whatever to the end.
> >> What I am trying to do is exactly NOT that. :-)  So, instead, just add
> >> say a
> >> line or two of text to a field.
> >>
> >> Is this possible?
> >>
> >> thanks,
> >>
> >> josé
> >>
> >>
> >> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >> -
> >>
> >>
> >
> >
>

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

Re: [sqlite] csv files with unquoted data containing comma's

[Dennis Cote]

Ken wrote:


Maybe your runtime library id different than the source code?



Yep, that's it. I used standard 3.5.4 binary of sqlite3.exe for the 
test, but I was looking at the current CVS source files.


I'm sorry if I created any confusion.

Dennis Cote


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

Re: [sqlite] Adding data to the end of a column

[jose isaias cabrera]

Punkish,

I don't want to replace it.  I want to add to the end of field.  So, I did 
not know that I could do,


UPDATE table SET field = field && 'data to be added' WHERE id = 55;

So, I think this should do it.  I will give it a try and get back to the 
group.


thanks all.

josé

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

To: 
Sent: Wednesday, January 30, 2008 2:59 PM
Subject: Re: [sqlite] Adding data to the end of a column



replace the field with new value.

UPDATE table SET field = 'value with more data' WHERE field = 'value'

On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:


Greetings.

Is there a way to add data to the end of a field?  I know I select that
field, import the data into a variable and then add whatever to the end.
What I am trying to do is exactly NOT that. :-)  So, instead, just add 
say a

line or two of text to a field.

Is this possible?

thanks,

josé


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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/

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





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

Re: [sqlite] Odd issue when SELECT querying

[David Hautbois]

Thanks all !

I will receive a C book tomorrow.
I need it.

David.

James Dennett wrote:

David Hautbois wrote:

  

Hi
I have an odd issue.


My function :

char * get_config_value (sqlite3 * db, char * config_name) {



[...]
 
  

configvalue = (char *) sqlite3_column_text(stmt, 0);



[...]

  

return configvalue;
}
Why the variable content changes ??

Why the variable configvalue has not the same content ??



The variable has the same content (a pointer), but the pointer is
invalid by the time your function returns it.  You need to copy the
*string*, not just a pointer to it.

This is essentially the same issue that Igor described when he wrote:

  
Strings passed to the callback are valid only within the callback. As 
soon as the callback returns, the memory may be deallocated or reused 
for other purposes. If the callback wants to keep some strings around 
beyond a single call, it should allocate its own memory and copy the 
value over.



It's vitally important when using C libraries that you read the
documentation and avoid making any assumptions about the lifetimes of
objects referenced by pointers.

C++ wrappers can return std::string objects and avoid this issue (though
even in C++ it's important to consider validity/lifetime issues for both
pointers and iterators).

-- James


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


  


--
Web site : http://david.hautbois.free.fr
Tablet users map : http://david.hautbois.free.fr/maps/index.php



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

RE: [sqlite] Odd issue when SELECT querying

[James Dennett]

David Hautbois [mailto:[EMAIL PROTECTED] wrote:
> 
> I found the solution :
> I replaced this line :
> configvalue = (char *) sqlite3_column_text(stmt, 0);
> by
> configvalue = g_strdup((gchar *) sqlite3_column_text(stmt, 0));
> 
> and the configvalue type : gchar
> 
> Now it works !!
> 
> A newbie error...

Now you just need to watch out for memory leaks.

-- James


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

Re: [sqlite] Odd issue when SELECT querying

[David Hautbois]

I found the solution :
I replaced this line :
configvalue = (char *) sqlite3_column_text(stmt, 0);
by
configvalue = g_strdup((gchar *) sqlite3_column_text(stmt, 0));

and the configvalue type : gchar

Now it works !!

A newbie error...

David.

David Hautbois wrote:

Hi
I have an odd issue.

My database schema :
sqlite> .schema
CREATE TABLE config  (id INTEGER PRIMARY KEY,  config_name TEXT ,  
config_value TEXT);
CREATE TABLE waypoints (id INTEGER PRIMARY KEY, wp_date INTEGER, 
wp_longitude REAL, wp_latitude REAL, wp_speed REAL);


The config table content :
sqlite> SELECT * from config;
1|version|1
2|ftpserver|A
3|ftp_remotedir|
4|ftp_login|
5|ftp_password|

My function :

char * get_config_value (sqlite3 * db, char * config_name) {

   int ncols;
   sqlite3_stmt *stmt;
   char *sql;
   const char *tail;
   char * configvalue = 0;

   sql = g_strdup_printf("SELECT config_value FROM config WHERE 
config_name='%s'\n", config_name);

   printf (sql);
   sqlite3_prepare(db , sql, (int)strlen(sql), , );

   ncols = sqlite3_column_count(stmt);

   while(sqlite3_step(stmt) == SQLITE_ROW) {
   printf ("gpstracer-cfg.c - get_config_value : Getting column 
content\n");

   configvalue = (char *) sqlite3_column_text(stmt, 0);
   printf ("gpstracer-cfg.c - get_config_value : content=%s\n", 
configvalue);  > note this line

   }
   printf ("gpstracer-cfg.c - get_config_value : %s=%s\n", 
config_name, configvalue);  >and this one

   sqlite3_finalize(stmt);

   return configvalue;
}
***

When I call this function : get_config_value (db, "version")

I get :
SELECT config_value FROM config WHERE config_name='version'
gpstracer-cfg.c - get_config_value : Getting column content
gpstracer-cfg.c - get_config_value : 
content=1   > ok
gpstracer-cfg.c - get_config_value : version=ftp_password  
> Why the variable content changes ??


Why the variable configvalue has not the same content ??

It's the same issue than my previous post (Callback issue - using 
pointer as argument), when I used a callback.


Thanks.

David.




--
Web site : http://david.hautbois.free.fr
Tablet users map : http://david.hautbois.free.fr/maps/index.php



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

RE: [sqlite] Odd issue when SELECT querying

[James Dennett]

David Hautbois wrote:

> Hi
> I have an odd issue.
> 
> 
> My function :
> 
> char * get_config_value (sqlite3 * db, char * config_name) {

[...]
 
> configvalue = (char *) sqlite3_column_text(stmt, 0);

[...]

> return configvalue;
> }
> Why the variable content changes ??
> 
> Why the variable configvalue has not the same content ??

The variable has the same content (a pointer), but the pointer is
invalid by the time your function returns it.  You need to copy the
*string*, not just a pointer to it.

This is essentially the same issue that Igor described when he wrote:

> Strings passed to the callback are valid only within the callback. As 
> soon as the callback returns, the memory may be deallocated or reused 
> for other purposes. If the callback wants to keep some strings around 
> beyond a single call, it should allocate its own memory and copy the 
> value over.

It's vitally important when using C libraries that you read the
documentation and avoid making any assumptions about the lifetimes of
objects referenced by pointers.

C++ wrappers can return std::string objects and avoid this issue (though
even in C++ it's important to consider validity/lifetime issues for both
pointers and iterators).

-- James


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

Re: [sqlite] Adding data to the end of a column

[P Kishor]

replace the field with new value.

UPDATE table SET field = 'value with more data' WHERE field = 'value'

On 1/30/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>
> Greetings.
>
> Is there a way to add data to the end of a field?  I know I select that
> field, import the data into a variable and then add whatever to the end.
> What I am trying to do is exactly NOT that. :-)  So, instead, just add say a
> line or two of text to a field.
>
> Is this possible?
>
> thanks,
>
> josé
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/

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

[sqlite] Re: Adding data to the end of a column

[Igor Tandetnik]

jose isaias cabrera <[EMAIL PROTECTED]>
wrote: 

Is there a way to add data to the end of a field?  I know I select
that 
field, import the data into a variable and then add whatever to the
end. 
What I am trying to do is exactly NOT that. :-)  So, instead, just
add say a 
line or two of text to a field.


Perhaps something along the lines of

update mytable set myfield = myfield || ? where id = ?;

Igor Tandetnik

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

Re: [sqlite] csv files with unquoted data containing comma's

[Frank van Vugt]

Ken / Dennis,

> My version of output_Csv (3.5.4)  is missing conditional check.
> static void output_csv(struct callback_data *p, const char *z, int bSep){
>   FILE *out = p->out;
>   if( z==0 ){
> fprintf(out,"%s",p->nullvalue);
>   }else{
> int i;
> for(i=0; z[i]; i++){
>   if( needCsvQuote[((unsigned char*)z)[i]] ){
> i = 0;
> break;
>   }
> }

Yep, as stated earlier, that's the same version I'm using here and I'm also 
looking at:

for(i=0; z[i]; i++){
  if( needCsvQuote[((unsigned char*)z)[i]] ){
i = 0;
break;
  }

> Maybe your runtime library id different than the source code?


It seems this got fixed here:
http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/shell.c=1.170=1.171


Looks like a done deal, will try this patch instead.




-- 
Best,




Frank.

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

Re: [sqlite] Adding data to the end of a column

[Shawn Milochik]

-Original Message-
From: jose isaias cabrera <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: [sqlite] Adding data to the end of a column
Date: Wed, 30 Jan 2008 14:34:17 -0500

Greetings.

Is there a way to add data to the end of a field?  I know I select that 
field, import the data into a variable and then add whatever to the end. 
What I am trying to do is exactly NOT that. :-)  So, instead, just add say a 
line or two of text to a field.

Is this possible?

thanks,

josé 





Try:

UPDATE tablename SET something = something + " addition";

Not sure of the exact syntax for contacenation -- could be + or . or something 
else.

Shawn




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

[sqlite] Odd issue when SELECT querying

[David Hautbois]

Hi
I have an odd issue.

My database schema :
sqlite> .schema
CREATE TABLE config  (id INTEGER PRIMARY KEY,  config_name TEXT ,  
config_value TEXT);
CREATE TABLE waypoints (id INTEGER PRIMARY KEY, wp_date INTEGER, 
wp_longitude REAL, wp_latitude REAL, wp_speed REAL);


The config table content :
sqlite> SELECT * from config;
1|version|1
2|ftpserver|A
3|ftp_remotedir|
4|ftp_login|
5|ftp_password|

My function :

char * get_config_value (sqlite3 * db, char * config_name) {

   int ncols;
   sqlite3_stmt *stmt;
   char *sql;
   const char *tail;
   char * configvalue = 0;

   sql = g_strdup_printf("SELECT config_value FROM config WHERE 
config_name='%s'\n", config_name);

   printf (sql);
   sqlite3_prepare(db , sql, (int)strlen(sql), , );

   ncols = sqlite3_column_count(stmt);

   while(sqlite3_step(stmt) == SQLITE_ROW) {
   printf ("gpstracer-cfg.c - get_config_value : Getting column 
content\n");

   configvalue = (char *) sqlite3_column_text(stmt, 0);
   printf ("gpstracer-cfg.c - get_config_value : content=%s\n", 
configvalue);  > note this line

   }
   printf ("gpstracer-cfg.c - get_config_value : %s=%s\n", config_name, 
configvalue);  >and this one

   sqlite3_finalize(stmt);

   return configvalue;
}
***

When I call this function : get_config_value (db, "version")

I get :
SELECT config_value FROM config WHERE config_name='version'
gpstracer-cfg.c - get_config_value : Getting column content
gpstracer-cfg.c - get_config_value : 
content=1   > ok
gpstracer-cfg.c - get_config_value : version=ftp_password  > 
Why the variable content changes ??


Why the variable configvalue has not the same content ??

It's the same issue than my previous post (Callback issue - using 
pointer as argument), when I used a callback.


Thanks.

David.


--
Web site : http://david.hautbois.free.fr
Tablet users map : http://david.hautbois.free.fr/maps/index.php



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

[sqlite] Adding data to the end of a column

[jose isaias cabrera]

Greetings.

Is there a way to add data to the end of a field?  I know I select that 
field, import the data into a variable and then add whatever to the end. 
What I am trying to do is exactly NOT that. :-)  So, instead, just add say a 
line or two of text to a field.


Is this possible?

thanks,

josé 



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

Re: [sqlite] csv files with unquoted data containing comma's

[Ken]

My version of output_Csv (3.5.4)  is missing conditional check.
static void output_csv(struct callback_data *p, const char *z, int bSep){
  FILE *out = p->out;
  if( z==0 ){
fprintf(out,"%s",p->nullvalue);
  }else{
int i;
for(i=0; z[i]; i++){
  if( needCsvQuote[((unsigned char*)z)[i]] ){
i = 0;
break;
  }
}


Maybe your runtime library id different than the source code?

HTH,
Ken




Dennis Cote <[EMAIL PROTECTED]> wrote: Frank van Vugt wrote:
> 
> I'll look into it tomorrow, but was under the impression that:
> 
> * when using .mode csv, the separator _always_ is a comma
> 
> * when one wants a different separator, one has to use .separator ... in 
> combination with .mode list
> 
> 
> needCsvQuote() is called from output_csv(), which is called when in csv-mode 
> only
> 

Frank,

You are on to something here.

The following trace from sqlite shows the problem.

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table t(a, b, c);
sqlite> insert into t values(1, 2, 3);
sqlite> insert into t values('10', '200', '3000');
sqlite> insert into t values('1,000', '2,000', '3,000');
sqlite> insert into t values(1.0, '20 0' , '3|0');
sqlite> insert into t values( 'one_with,a_comma', 'long "quoted" bit''s 
& pieces
', 'one with a
...>  CR');
sqlite> .mode csv
sqlite> select * from t;
1,2,3
10,200,3000
1,000,2,000,3,000
1.0,"20 0",3|0
one_with,a_comma,"long ""quoted"" bit's & pieces","one with a
  CR"
sqlite> select typeof(a), typeof(b), typeof(c) from t;
integer,integer,integer
text,text,text
text,text,text
real,text,text
text,text,text

The third and fifth rows contains strings with embedded commas that are 
not quoted in the output even though they should be.

SQLite does indeed seem to be quoting only fields that contain 
characters in the needCsvQuote array (as in the fourth and fifth rows), 
even though the code in output_csv() does seem to check for the 
separator correctly.

static void output_csv(struct callback_data *p, const char *z, int bSep){
   FILE *out = p->out;
   if( z==0 ){
 fprintf(out,"%s",p->nullvalue);
   }else{
 int i;
 int nSep = strlen(p->separator);
 for(i=0; z[i]; i++){
   if( needCsvQuote[((unsigned char*)z)[i]]
  || (z[i]==p->separator[0] &&
  (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){
 i = 0;
 break;
   }
 }
 if( i==0 ){
   putc('"', out);
   for(i=0; z[i]; i++){
 if( z[i]=='"' ) putc('"', out);
 putc(z[i], out);
   }
   putc('"', out);
 }else{
   fprintf(out, "%s", z);
 }
   }
   if( bSep ){
 fprintf(p->out, p->separator);
   }
}

It is using the correct separator string, a single comma, since the 
commas in the output are produced by the last fprintf() call at the end 
of output_csv(). I can't see where the logic is wrong. It should be 
caught by the combination of the z[i]==p->separator[0] test and the 
nSep==1 test, but the output is definitely wrong.

Can someone else spot the error?

During a bad call p->Separator is "," and z is "1,000", but the output 
appears to come from the fprintf(out, "%s", z) line.

BTW, as a minor style issue, I think the last fprintf() call should use 
out rather than p->out for the first argument for consistency reasons if 
nothing else.

Dennis Cote

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

Re: [sqlite] FTS3 Unicode support

[Scott Hess]

The [3] status is ... pending, sorry :-(.  But it is more along the
lines of adding stuff to ICU rather than adding ICU-less stuff to
SQLite, so it sounds like that is not relevant to what you're doing.

As Dan mentioned, there's stuff in there for supporting alternate
tokenizers, including an ICU-based tokenizer.  Even if you aren't
using the ICU-based tokenizer, the scheme for loading tokenizers in
README.tokenizers is probably the way to go.  Otherwise, if you're
compiling your own SQLite code, it's not very hard at all to introduce
a custom tokenizer.

Note that if you redefine how your tokenizer tokenizes, it can leave
your existing fts index broken.  Basically, if something tokenizes to
"X" now, and later changes cause it to tokenize to "XY", then you will
no longer be able to match on "X" because it's baked into the index
that way.  The only real solution is to expose this as a new tokenizer
and rebuild the table.  [Indeed, I'm still making up my story in this
area, too.  It's similar to how changing the implementation of a
custom collator can mess with your regular SQLite indices.]

At this time, the fts index is internally ordered using memcmp()
ordering.  This may make the results of prefix queries incorrect in
certain cases.  I am not knowledgeable enough about
internationalization issues to know if this is a real problem, or just
a theoretical problem, and if it's a real problem, is it a problem
which is at all reasonable to solve?

I believe that the existing fts MATCH code makes certain assumptions
about how the tokenizer works.  Specifically, if the tokenizer returns
more than one variant at a position, I don't think the MATCH code is
going to deal with that very well.  For instance, if you want to
tokenize an accented word both with and without the accent, things
might go awry when you run a query with the accented word.  I've
currently got nothing planned for resolving this, but suggestions (or
prospective solutions) are welcome.

-scott


On Thu, Jan 24, 2008 at 4:26 PM, Myk Melez <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>  I'm working to enable FTS3 in the next version of Firefox [1] so that
>  extenders can take advantage of it, although Firefox itself isn't using
>  it for the next release.
>
>  Given Firefox's international audience, it would be useful for FTS3 to
>  support Unicode.  We currently do this for upper(), lower(), and LIKE by
>  redefining them with sqlite3_create_function [2].
>
>  For FTS3 it seems like we'd have to redefine the tokenizer and MATCH.
>  Can that be done using sqlite3_create_function, and what's the status of
>  the international support mentioned in a previous message on this list [3]?
>
>  -myk
>
>
>  [1] https://bugzilla.mozilla.org/show_bug.cgi?id=413589
>  [2]
>  
> http://lxr.mozilla.org/mozilla/source/storage/src/mozStorageUnicodeFunctions.cpp
>  [3] http://www.mail-archive.com/sqlite-users@sqlite.org/msg27238.html
>
>  -
>  To unsubscribe, send email to [EMAIL PROTECTED]
>  -
>
>

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

Re: [sqlite] csv files with unquoted data containing comma's

[Dennis Cote]

Frank van Vugt wrote:


I'll look into it tomorrow, but was under the impression that:

* when using .mode csv, the separator _always_ is a comma

* when one wants a different separator, one has to use .separator ... in 
combination with .mode list



needCsvQuote() is called from output_csv(), which is called when in csv-mode 
only




Frank,

You are on to something here.

The following trace from sqlite shows the problem.

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table t(a, b, c);
sqlite> insert into t values(1, 2, 3);
sqlite> insert into t values('10', '200', '3000');
sqlite> insert into t values('1,000', '2,000', '3,000');
sqlite> insert into t values(1.0, '20 0' , '3|0');
sqlite> insert into t values( 'one_with,a_comma', 'long "quoted" bit''s 
& pieces

', 'one with a
   ...>  CR');
sqlite> .mode csv
sqlite> select * from t;
1,2,3
10,200,3000
1,000,2,000,3,000
1.0,"20 0",3|0
one_with,a_comma,"long ""quoted"" bit's & pieces","one with a
 CR"
sqlite> select typeof(a), typeof(b), typeof(c) from t;
integer,integer,integer
text,text,text
text,text,text
real,text,text
text,text,text

The third and fifth rows contains strings with embedded commas that are 
not quoted in the output even though they should be.


SQLite does indeed seem to be quoting only fields that contain 
characters in the needCsvQuote array (as in the fourth and fifth rows), 
even though the code in output_csv() does seem to check for the 
separator correctly.


static void output_csv(struct callback_data *p, const char *z, int bSep){
  FILE *out = p->out;
  if( z==0 ){
fprintf(out,"%s",p->nullvalue);
  }else{
int i;
int nSep = strlen(p->separator);
for(i=0; z[i]; i++){
  if( needCsvQuote[((unsigned char*)z)[i]]
 || (z[i]==p->separator[0] &&
 (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){
i = 0;
break;
  }
}
if( i==0 ){
  putc('"', out);
  for(i=0; z[i]; i++){
if( z[i]=='"' ) putc('"', out);
putc(z[i], out);
  }
  putc('"', out);
}else{
  fprintf(out, "%s", z);
}
  }
  if( bSep ){
fprintf(p->out, p->separator);
  }
}

It is using the correct separator string, a single comma, since the 
commas in the output are produced by the last fprintf() call at the end 
of output_csv(). I can't see where the logic is wrong. It should be 
caught by the combination of the z[i]==p->separator[0] test and the 
nSep==1 test, but the output is definitely wrong.


Can someone else spot the error?

During a bad call p->Separator is "," and z is "1,000", but the output 
appears to come from the fprintf(out, "%s", z) line.


BTW, as a minor style issue, I think the last fprintf() call should use 
out rather than p->out for the first argument for consistency reasons if 
nothing else.


Dennis Cote

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

Re: [sqlite] csv files with unquoted data containing comma's

[Frank van Vugt]

Dennis,

> Your change assumes the separator is always a comma. SQLite does not
> make that assumption.

I'll look into it tomorrow, but was under the impression that:

* when using .mode csv, the separator _always_ is a comma

* when one wants a different separator, one has to use .separator ... in 
combination with .mode list


needCsvQuote() is called from output_csv(), which is called when in csv-mode 
only


-- 
Best,




Frank.

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

Re: [sqlite] csv files with unquoted data containing comma's

[Dennis Cote]

Frank van Vugt wrote:

L.S.

Comma seperated files tend to become a bit too seperated when the field data 
contains the character used for seperation while not being quoted ;)


Sqlite 3.5.4 uses shell.c::needsCsvQuote() to determine whether or not to 
quote the field contents, but it doesn't check for the comma.. the 
following patch changes that:


--- shell.c.orig2007-11-30 02:28:11.0 +0100
+++ shell.c 2008-01-30 11:29:29.0 +0100
@@ -441,7 +441,7 @@
 static const char needCsvQuote[] = {
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
-  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0,
+  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 1, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,


I understand a number of open tickets exist related to csv-behaviour, so the 
above might be combined with those (the particular problem didn't seem to be 
mentioned earlier, though).







Frank,

Your change assumes the separator is always a comma. SQLite does not 
make that assumption. It uses the following code to check the array and 
the currently defined separator string before quoting a field.


  if( needCsvQuote[((unsigned char*)z)[i]]
 || (z[i]==p->separator[0] &&
 (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){

The usual case for CSV files is that the separator is a single comma, 
and this code will quote such field correctly.


Dennis Cote



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

RE: [sqlite] How to make correct transaction use only SQL?

[Samuel R. Neff]

Instead of piping the sql into sqlite3.exe, use the ".read" command instead.


C:\Temp\s>sqlite3 test.dat
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .read test.sql
SQL error near line 10: column id is not unique
SQL error near line 12: cannot commit - no transaction is active
sqlite> select * from t1;
sqlite>

.read aborts on first error, whereas piping doesn't know to do that (and
presumably there's no way it could know).

HTH,

Sam 


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Alexander Batyrshin [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 29, 2008 9:37 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to make correct transaction use only SQL?

For example i have this tabe:

CREATE TABLE t1 (
  id int unique ON CONFLICT ROLLBACK,
  val char
);

And I have to execute this sql file:

BEGIN TRANSACTION;
INSERT INTO t1 (id, val) VALUES(1, 'val1');
INSERT INTO t1 (id, val) VALUES(2, 'val2');
INSERT INTO t1 (id, val) VALUES(3, 'val3');
INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT
INSERT INTO t1 (id, val) VALUES(4, 'val5');
COMMIT;

If we execute this sql file, only INSERT before CONFLICT case will be
rollback-ed, but last one still will be executed and remains in
database...

cat test.sql | sqlite3 test.db
SQL error near line 11: column id is not unique
SQL error near line 13: cannot commit - no transaction is active
$ sqlite3 test.db
SQLite version 3.4.0
sqlite> select * from t1;
4|val5


I what that on conflict _whole_ transaction will ROLLBACK and state of
database will be exactly like at moment of execution "BEGIN
TRANSACTION". How it is possible using only SQL?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Re: Callback issue - using pointer as argument

[David HAUTBOIS]

It works better.

Thanks.

David.

Igor Tandetnik wrote:

David Hautbois <[EMAIL PROTECTED]> wrote:


char * get_config_value (sqlite3 * db, char * config_name) {

   TabResult res;

   rc= sqlite3_exec( db, query, exec_get_config_value_cb , ,
);


Strings passed to the callback are valid only within the callback. As 
soon as the callback returns, the memory may be deallocated or reused 
for other purposes. If the callback wants to keep some strings around 
beyond a single call, it should allocate its own memory and copy the 
value over.


Better still, stop using sqlite3_exec and switch over to 
sqlite3_prepare / sqlite3_step / sqlite3_finalize interface.


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





--
http://david.hautbois.free.fr
http://slugplayer.free.fr


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

[sqlite] csv files with unquoted data containing comma's

[Frank van Vugt]

L.S.

Comma seperated files tend to become a bit too seperated when the field data 
contains the character used for seperation while not being quoted ;)

Sqlite 3.5.4 uses shell.c::needsCsvQuote() to determine whether or not to 
quote the field contents, but it doesn't check for the comma.. the 
following patch changes that:

--- shell.c.orig2007-11-30 02:28:11.0 +0100
+++ shell.c 2008-01-30 11:29:29.0 +0100
@@ -441,7 +441,7 @@
 static const char needCsvQuote[] = {
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
-  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0,
+  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 1, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,


I understand a number of open tickets exist related to csv-behaviour, so the 
above might be combined with those (the particular problem didn't seem to be 
mentioned earlier, though).




-- 
Best,




Frank.

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