Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB

2012-10-26 Thread Dan Kennedy 

On 10/27/2012 07:06 AM, Simon Slavin wrote:


On 26 Oct 2012, at 11:05pm, Clemens Ladisch
wrote:


Yes; sqlite3_finalize _always_ frees the statement.


And if the statement is already finalized (due to an earlier error,
perhaps) then it is a harmless noop.  So you can do it near the end
of your routine harmlessly.


That's a bit deceptive. Passing the same pointer to sqlite3_finalize()
twice is undefined behavior. You might get an SQLITE_MISUSE error, but
you also might get a segfault.


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

Re: [sqlite] light weight write barriers

2012-10-26 Thread Theodore Ts'o 
On Fri, Oct 26, 2012 at 09:54:53PM -0400, Vladislav Bolkhovitin wrote:
> What different in our positions is that you are considering storage
> as something you can connect to your desktop, while in my view
> storage is something, which stores data and serves them the best
> possible way with the best performance.

I don't get paid to make Linux storage work well for gold-plated
storage, and as far as I know, none of the purveyors of said gold
plated software systems are currently employing Linux file system
developers to make Linux file systems work well on said gold-plated
hardware.

As for what I might do on my own time, for fun, I can't afford said
gold-plated hardware, and personally I get a lot more satisfaction if
I know there will be a large number of people who benefit from my work
(it was really cool when I found out that millions and millions of
Android devices were going to be using ext4 :-), as opposed to a very
small number of people who have paid $$$ to storage vendors who don't
feel it's worthwhile to pay core Linux file system developers to
leverage their hardware.  Earlier, you were bemoaning why Linux file
system developers weren't paying attention to using said fancy SCSI
features.  Perhaps now you'll understand better it's not happening?

> Price doesn't matter here, because it's completely different topic.

It matters if you think I'm going to do it on my own time, out of my
own budget.  And if you think my employer is going to choose to use
said hardware, price definitely matters.  I consider engineering to be
the art of making tradeoffs, and price is absolutely one of the things
that we need to trade off against other goals.

It's rare that you get to design something where performance matters
above all else.  Maybe it's that way if you're paid by folks whose job
it is to destablize the world's financial markets by pushing the holes
into the right half plane (i.e., high frequency trading :-).  But for
the rest of the world, price absolutely matters.

- Ted

P.S.  All of the storage I have access to at home is SATA.  If someone
would like to change that and ship me free hardware, as long as it
doesn't require three-phase power (or require some exotic interconnect
which is ghastly expensive and which you are also not going to provide
me for free), do contact me off-line.  :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I/O error on creating index with 3.7.14

2012-10-26 Thread Jamie Norrish 
On Sat, 2012-10-27 at 02:26 +0700, Dan Kennedy wrote:

> Thanks for reporting this. Now fixed here:
> 
>http://www.sqlite.org/src/info/e24ba5bee4

Excellent - thank you for the speedy fix!

Jamie

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

Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB

2012-10-26 Thread Simon Slavin 

On 26 Oct 2012, at 11:05pm, Clemens Ladisch  wrote:

> Yes; sqlite3_finalize _always_ frees the statement.

And if the statement is already finalized (due to an earlier error, perhaps) 
then it is a harmless noop.  So you can do it near the end of your routine 
harmlessly.

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

Re: [sqlite] Union all writting on /var/tmp

2012-10-26 Thread Simon Slavin 

On 26 Oct 2012, at 10:28pm, Elefterios Stamatogiannakis  
wrote:

> create table t as
> select upper(c1), c2, lower(c3) from
> (
> select * from file('http://www.foo.com/list1.tsv.gz')
> union all
> select * from file('http://www.foo.com/list2.tsv.gz')
> )
> where c2!=c4;

Does this work instead ?

CREATE TABLE t ([[whatever columns you want]]);
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list1.tsv.gz') WHERE c2!=c4;
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list2.tsv.gz') WHERE c2!=c4;

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

Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB

2012-10-26 Thread Clemens Ladisch 
Adam DeVita wrote:
> As an error check, I've got a program opening an encrypted file.
>
> sqlite3_prepare_v2 returns SQLITE_OK
>
> and ppStmt is not null.
>
> When I run sqlite3_step(ppStmt) it returns SQLITE_NOTADB.

sqlite3_prepare_v2 does not yet start a transaction (to allow keeping
cached prepared statements around), and only inside a transaction can
the database file be locked and accessed.

> Recognizing an error at this point I'd like to clean up properly.
> sqlite3_finalize(ppStmt) returns SQLITE_NOTADB.
> At this point, has it actually cleared the prepared statement

Yes; sqlite3_finalize _always_ frees the statement.

> http://www.sqlite.org/capi3ref.html#sqlite3_finalize is a bit brief on
> the success of finalizing the statement given that it returned an
> error.

| If the most recent evaluation of the statement encountered no errors
| or if the statement is never been evaluated, then sqlite3_finalize()
| returns SQLITE_OK.  If the most recent evaluation of statement S
| failed, then sqlite3_finalize(S) returns the appropriate error code
| or extended error code.

Or in the words of the source code:
| The following routine destroys a virtual machine that is created by
| the sqlite3_compile() routine. The integer returned is an SQLITE_
| success/failure code that describes the result of executing the virtual
| machine.

In other words, _any_ error returned by this function applies not
to what sqlite3_finalize did itself, but to the result of other
functions.


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

Re: [sqlite] Union all writting on /var/tmp

2012-10-26 Thread Igor Tandetnik 

On 10/26/2012 5:28 PM, Elefterios Stamatogiannakis wrote:

The real query in madIS looks like the following:

create table t as
select upper(c1), c2, lower(c3) from
(
select * from file('http://www.foo.com/list1.tsv.gz')
union all
select * from file('http://www.foo.com/list2.tsv.gz')
)
where c2!=c4;


Perhaps something like this instead:

create table t as
select upper(c1), c2, lower(c3)
from file('http://www.foo.com/list1.tsv.gz')
where c2!=c4;

insert into t
select upper(c1), c2, lower(c3)
from file('http://www.foo.com/list2.tsv.gz')
where c2!=c4;


Could the /var/tmp position that it writes to, be changed to another directory?


http://sqlite.org/c3ref/temp_directory.html

--
Igor Tandetnik

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

Re: [sqlite] Union all writting on /var/tmp

2012-10-26 Thread Elefterios Stamatogiannakis 
Thank you for answering Mr. Hipp. The general problem doesn't have to do 
with counting the rows (it was there as a placeholder). I just want to 
merge 2 (and more) table/streams.


The real query in madIS looks like the following:

create table t as
select upper(c1), c2, lower(c3) from
(
select * from file('http://www.foo.com/list1.tsv.gz')
union all
select * from file('http://www.foo.com/list2.tsv.gz')
)
where c2!=c4;

"list1.tsv.gz" and "list2.tsv.gz" are two enormous streams which i would 
like to process and put into table "t".


I have gone to great lengths to make the virtual table "file" being 
fully streamed. So both of the network files (list1, list2) arrive from 
the network packet by packet, get decompressed without touching the disk 
and then are broken into multiple columns ('tsv' is assumed to mean tab 
separated).


I admire SQLite very much for its predictability. So for the above query 
i would expect from it to scan first over the first file (list1) and 
then over the next (list2), and row by row put them in table "t".


This assumption was so strong that i've been searching all over the 
"file" VT code for a *very* long time to find out the bug in it that 
caused it to grid to a halt my system whenever i executed above query ( 
/ partition was filled ).


I have a request. If "union all" cannot be changed to not write on the 
hard disk when scanning just once over tables/streams. Could the 
/var/tmp position that it writes to, be changed to another directory? I 
prefer to keep a small root (/) partition and right now i'm unable to do 
any "union all" on anything that is bigger than the free space on it.


Thank you again,

lefteris.

On 26/10/2012 9:23 μμ, Richard Hipp wrote:



On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis
> wrote:

I have been observing the following freaky behaviour of SQLite. When
i run:

select count(*) from (select * from huge_table union all select *
from huge_table);

Sqlite starts writting in /var/tmp/ a file like:

  /var/tmp/etilqs___gblRd6vUPcx91Hl, the root partition of fills up
and an error is raised.

Why does SQLite 3.7.14.1 need to write at all when doing union all?
It seems to me that there is no reason for doing so.


The only way SQLite knows to evaluate the query is to (1) compute the
UNION ALL into a temporary table then (2) scan the temporary table to
count the rows.  /var/tmp space is used to hold the temporary table.

Try instead:

SELECT (select count(*) from huge_table)+(select count(*) from huge_table);




Best regards,

lefteris.
_
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

[sqlite] statement prepares OK but step returns SQLITE_NOTADB

2012-10-26 Thread Adam DeVita 
Good day,

As an error check, I've got a program opening an encrypted file.

sqlite3_prepare_v2 returns SQLITE_OK

and ppStmt is not null.

When I run sqlite3_step(ppStmt) it returns SQLITE_NOTADB.

Recognizing an error at this point I'd like to clean up properly.
sqlite3_finalize(ppStmt) returns SQLITE_NOTADB.
At this point, has it actually cleared the prepared statement, so I
can set ppStmt = NULL and carry on to close the db and tell the user
they shouldn't have opened that file?

http://www.sqlite.org/capi3ref.html#sqlite3_finalize is a bit brief on
the success of finalizing the statement given that it returned an
error.


I'm using the basic amalgamation c file  SQLite version 3.7.4.

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

Re: [sqlite] I/O error on creating index with 3.7.14

2012-10-26 Thread Dan Kennedy 

On 10/26/2012 12:14 PM, Jamie Norrish wrote:

Using 3.7.14, when creating an index on a 27G database (on the table
that contains almost all of the data), I consistently (on Windows XP and
Debian GNU/Linux, on three different machines) get a disk I/O error.
This does not happen using 3.7.13 (only tested on Debian GNU/Linux), nor
does it happen when creating the same index on a smaller (~2G) version
of the database.

Is there more information I should provide as part of a proper bug
report, or is this a known issue, or have I missed a trick somewhere?


Thanks for reporting this. Now fixed here:

  http://www.sqlite.org/src/info/e24ba5bee4

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

Re: [sqlite] Union all writting on /var/tmp

2012-10-26 Thread Richard Hipp 
On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis <
est...@gmail.com> wrote:

> I have been observing the following freaky behaviour of SQLite. When i run:
>
> select count(*) from (select * from huge_table union all select * from
> huge_table);
>
> Sqlite starts writting in /var/tmp/ a file like:
>
>  /var/tmp/etilqs_**gblRd6vUPcx91Hl, the root partition of fills up and an
> error is raised.
>
> Why does SQLite 3.7.14.1 need to write at all when doing union all? It
> seems to me that there is no reason for doing so.
>

The only way SQLite knows to evaluate the query is to (1) compute the UNION
ALL into a temporary table then (2) scan the temporary table to count the
rows.  /var/tmp space is used to hold the temporary table.

Try instead:

SELECT (select count(*) from huge_table)+(select count(*) from huge_table);





>
> Best regards,
>
> lefteris.
> __**_
> 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

[sqlite] Union all writting on /var/tmp

2012-10-26 Thread Eleytherios Stamatogiannakis 

I have been observing the following freaky behaviour of SQLite. When i run:

select count(*) from (select * from huge_table union all select * from 
huge_table);


Sqlite starts writting in /var/tmp/ a file like:

 /var/tmp/etilqs_gblRd6vUPcx91Hl, the root partition of fills up and an 
error is raised.


Why does SQLite 3.7.14.1 need to write at all when doing union all? It 
seems to me that there is no reason for doing so.


Best regards,

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

Re: [sqlite] how to select " char in sqlite

2012-10-26 Thread Black, Michael (IS) 
You appear to be programming in C so that's what this is...
Here's a complete example where you can control the table formatting yourself.
This is using sqlite3 calls and I made it produce a simple, complete HTML page.
This is, of course, tied to your database due to the specific column names.  
It's more work to make it generic.
But this should get you a lot closer to what you really want I hope.

Compile and run like this:

myhtml t9_engine.db "select id,partnumber,pic from engine where id>7" > n.html



#include 
#include 
#include 
#include "sqlite3.h"

void checkrc(int rc,int check,sqlite3 *db)
{
  if (rc != check) {
fprintf(stderr,"%s\n",sqlite3_errmsg(db));
exit(1);
  }
}

void doMySQL(char *dbname, char *sql)
{
  sqlite3 *db;
  int rc;
  sqlite3_stmt *stmt;
  rc=sqlite3_open(dbname,);
  checkrc(rc,SQLITE_OK,db);
  rc = sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
  checkrc(rc,SQLITE_OK,db);
  printf("http://www.w3.org/TR/REC-html40/strict.dtd\;>\n");
  printf("\n\nParts List");
  printf("\n");
  printf("\n");
  printf("IDPart#Picture\n");
  while((rc=sqlite3_step(stmt))==SQLITE_ROW) {
int id=sqlite3_column_int(stmt,0);
printf("\n%d\n",id);
char *partnumber = sqlite3_column_text(stmt,1);
printf("%s\n",partnumber);
char *pic = sqlite3_column_text(stmt,2);
printf("\n",pic,pic);
printf("\n");
  }
  checkrc(rc,SQLITE_DONE,db);
  rc=sqlite3_finalize(stmt);
  checkrc(rc,SQLITE_OK,db);
  printf("\n\n");
  rc = sqlite3_close(db);
  checkrc(rc,SQLITE_OK,db);
}

int main(int argc, char *argv[])
{
  if (argc !=3) {
fprintf(stderr,"Usage: %s database \"sql\"",argv[0]);
exit(1);
  }
  doMySQL(argv[1],argv[2]);
  return 0;
}



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Thursday, October 25, 2012 8:25 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select " char in sqlite

char bh1[320];
memset(bh1,0,320);
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here couldn't work

error:
sqlite3 -html -header t9_engine.db "select id,partnumber,substr('',1,180) as img,pcs from engine where id>7;" >> n.htmlError: n
ear "'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'' as img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here could work
the result is:
8
AA34841687 000 INSONO-SOUS-MOTEUR--
img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
1


and the best way is change
to  <
to  >

___
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] Mac development question

2012-10-26 Thread Igor Korot 
List,
Apologies for moving off-topic.
The discussion is now moved to be on PM.

Thank you.

On Wed, Oct 24, 2012 at 3:39 AM, Simon Slavin  wrote:
>
> On 24 Oct 2012, at 5:29am, Igor Korot  wrote:
>
>> It looks like you are "Apple person" that I got suggeted to talk to. ;-)
>> The situation is as follows: I am developing an application that will
>> utilize not just SQLite but some other library.
>> According to the "development standards" on *nix-like systems programs
>> should use dynamic linking (use .so), and
>> not static linking (not use .a). Since Apple Mac OS X follows *nix,
>> this will be the approach.
>> According to the same guidelines all those libraries should go to
>> /usr/lib (or /usr/local/lib).
>
> Well, for SQLite the recommendation is that you just compile the .h and .c 
> SQLite files into your application.  Doesn't need a library.  And it ensures 
> that you know which version of SQLite your app is using.
>
>> Now how else I can place everything in the proper place without
>> installer/distribution package? On Mac they have
>> dmg extension IIRC.
>
> For other libraries you want to install, do what I previously described and 
> include a copy of the library in your application.  When the application is 
> run, it looks for a copy of the library in the right place, and if it doesn't 
> find one it copies its own copy from its bundle into there.  In other words, 
> your application does its own installation of any support files (including 
> libraries) that it needs, if they don't already exist.  Then, if your 
> programming language makes it necessary, it restarts itself so it can use 
> them.
>
> My /usr/lib folder does have files in.  They have '.dylib' extensions, not 
> .so, except for the PAM stuff which is '.so.2'.  I don't know what that means.
>
>> Now AFAICS, those distribution packages (or dmg) files can be called
>> installation packages.
>
> dmg is a disk image.  They can be compressed which makes for smaller 
> downloads, which is why they're popular.  You might distribute your 
> application, documentation, sample files, etc. all in one disk image.  That's 
> standard.  But application installers as separate apps or packages are 
> frowned on in the Mac community.  They're used only when you have a large 
> suite of applications all of which share common components, e.g. Microsoft 
> Office, where building support installation logic into each individual 
> application would be wasteful.
>
>> This will be one copy for all users.
>> The database will be populated originally by me and will be supplied
>> alone with the application bundle and those additional library.
>
> You explained it clearly now.  Your application can include a copy of that 
> file with starter information inside its own bundle.  The writable version 
> belongs in the shared application support folder, which on an English-based 
> standalone locally-mounted Mac is
>
> /Library/Application Support/
>
> However, there is a system call which use should use to get that path, and 
> the system call takes into account what your user has named your application, 
> what language they are using, and whether they have a non-standard setup 
> which keeps their support files on a network drive or something.  So rather 
> than hardwire this path into your app you should use the system call
>
> URLsForDirectory:inDomains:
>
> and feed it
>
> NSApplicationSupportDirectory
>
> as described in the 'Locating Items in the Standard Directories' section of
>
> 
>
> Listing 2-1 on that page is a pre-written function you can use to find 
> exactly that directory we've been discussing.
>
> Hope this helps.  We have drifted far away from matters to do with SQLite, so 
> if you want to pursue the more Maccish side of this, you might email me 
> directly.
>
> 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] I/O error on creating index with 3.7.14

2012-10-26 Thread Black, Michael (IS) 
Hmmm...looks a lot like 32-bit overflow into a 64-bit number.
2^64
18446744073709551616
Your read offset
18446744071873782392



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jamie Norrish [ja...@artefact.org.nz]
Sent: Friday, October 26, 2012 12:14 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] I/O error on creating index with 3.7.14

Using 3.7.14, when creating an index on a 27G database (on the table
that contains almost all of the data), I consistently (on Windows XP and
Debian GNU/Linux, on three different machines) get a disk I/O error.
This does not happen using 3.7.13 (only tested on Debian GNU/Linux), nor
does it happen when creating the same index on a smaller (~2G) version
of the database.

I ran the process under strace; the final relevant lines (as far as I
can judge, knowing nothing of this) are:

lseek(5, 23934032896, SEEK_SET) = 23934032896
write(5, "\231\216\344\271\213\351\235\240\345\261\261\n+\367K\340*\5\2I
\1\4\v\3
01\351\276\215\345\202\276\346\271"..., 1024) = 1024
lseek(5, 23934033920, SEEK_SET) = 23934033920
write(5, "\207\345\210\245\347\267\207\347\264\240\346\211\200\350\254
\202\346\2
11\223\351\274\223\345\274\204\n+\367u\334*\5"..., 632) = 632
lseek(5, 0, SEEK_SET)   = 0
read(5, 0x7f5462cb06b8, 18446744071873782392) = -1 EFAULT (Bad address)
close(5)= 0

The command that causes the error is "CREATE INDEX IF NOT EXISTS
TextNGramIndex ON TextNGram (text, ngram, size)". The database schema is
(without the failing index):

CREATE TABLE Text (
   id INTEGER PRIMARY KEY ASC,
   filename TEXT UNIQUE NOT NULL,
   checksum TEXT NOT NULL,
   label TEXT NOT NULL
   );
CREATE TABLE TextHasNGram (
   text INTEGER NOT NULL REFERENCES Text (id),
   size INTEGER NOT NULL
   );
CREATE TABLE TextNGram (
text INTEGER NOT NULL REFERENCES Text (id),
ngram TEXT NOT NULL,
size INTEGER NOT NULL,
count INTEGER NOT NULL
);
CREATE UNIQUE INDEX TextHasNGramIndex
   ON TextHasNGram (text, size);
CREATE INDEX TextIndexLabel ON Text (label);


Is there more information I should provide as part of a proper bug
report, or is this a known issue, or have I missed a trick somewhere?

Jamie

___
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] how to select " char in sqlite

2012-10-26 Thread Black, Michael (IS) 
Here it is with your desire to use system().

The table output you get probably is not going to be formatted the way you like.
You can extend the logic here to put special sequences in the string to then 
replace with formatting.
It would really be easier oveall to do this yourself by using the sqlite calls 
instead of system().

#include 
#include 
#include 

char *str_replace(char *orig, char *rep, char *with) {
char *result; // the return string
char *ins;// the next insert point
char *tmp;// varies
int len_rep;  // length of rep
int len_with; // length of with
int len_front; // distance between rep and end of last rep
int count;// number of replacements

if (!orig)
return NULL;
if (!rep || !(len_rep = strlen(rep)))
return NULL;
if ((ins = strstr(orig, rep)) == NULL)
return NULL;
if (!with)
with = "";
len_with = strlen(with);

for (count = 0; (tmp = strstr(ins, rep)); ++count) {
ins = tmp + len_rep;
}

// first time through the loop, all the variable are set correctly
// from here on,
//tmp points to the end of the result string
//ins points to the next occurrence of rep in orig
//orig points to the remainder of orig after "end of rep"
tmp = result = malloc(strlen(orig) + (len_with - len_rep) * count + 1);

if (!result)
return NULL;

while (count--) {
ins = strstr(orig, rep);
len_front = ins - orig;
tmp = strncpy(tmp, orig, len_front) + len_front;
tmp = strcpy(tmp, with) + len_with;
orig += len_front + len_rep; // move to next "end of rep"
}
strcpy(tmp, orig);
return result;
}

int main() {
//char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'' from engine where 
id>7;\" >> n.html";
FILE *fp;
char buf[65535];
char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'' from engine where 
id>7;\" >> n.html";
system(sqlcmd);
fp = fopen("n.html","r");
while(fgets(buf,sizeof(buf),fp)) {
char *s=str_replace(buf,"#quot;","\"");
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,"","<");
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,"","<");
if (s) {strcpy(buf,s);free(s);}
printf("%s",buf);
}
fclose(fp);
return 0;
}


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Kees Nuyt [k.n...@zonnet.nl]
Sent: Friday, October 26, 2012 5:08 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] FW: how to select " char in sqlite

On Fri, 26 Oct 2012 01:25:24 +,
YAN HONG YE  wrote:

>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here couldn't work
>
>error:
>sqlite3 -html -header t9_engine.db "select id,partnumber,substr('src="'||pi
>c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> n.htmlError: 
>n
>ear "'operable program or batch file.
>The system cannot find the path specified.




>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here could work
>the result is:
>8
>AA34841687 000 INSONO-SOUS-MOTEUR--
>img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
>1
>
>
>and the best way is change
>   to  <
>   to  >

You will never get that right. Quoting will always stay a problem.
Forking out from C to a shell is bad practice. Forking out to a DOS
shell is a headache. It's not SQLite related and off topic in this list.

Nevertheless, Michael Black did provide a working solution on Wed, 24
Oct 2012 15:09:24 +, did you read it?

Please have a look at the sample C code I linked to before.
There are more examples there.
http://icculus.org/~chunky/stuff/sqlite3_example/

Good luck!

--
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
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] FW: how to select " char in sqlite

2012-10-26 Thread Patrik Nilsson 
There is always the possibility of converting a string into hex.

http://www.sqlite.org/lang_expr.html "Literal Values"

Example:

X'53514C697465'

/Patrik

On 10/26/2012 12:08 PM, Kees Nuyt wrote:
> On Fri, 26 Oct 2012 01:25:24 +, 
> YAN HONG YE  wrote:
> 
>> char bh1[320];
>> memset(bh1,0,320);
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>> id,partnumber,substr(\'\',1,180) as 
>> img,pcs from engine where id>7;\" >> n.html");
>> system(bh1);  //here couldn't work
>>
>> error:
>> sqlite3 -html -header t9_engine.db "select id,partnumber,substr('> src="'||pi
>> c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> 
>> n.htmlError: n
>> ear "'> operable program or batch file.
>> The system cannot find the path specified.
> 
> 
> 
> 
>> char bh1[320];
>> memset(bh1,0,320);
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>> id,partnumber,substr(\'\',1,180) as 
>> img,pcs from engine where id>7;\" >> n.html");
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'> src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>> system(bh1);  //here could work
>> the result is:
>> 8
>> AA34841687 000 INSONO-SOUS-MOTEUR--
>> img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>> //here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'  
>> 1
>> 
>>
>> and the best way is change 
>>  to  <
>>  to  >
> 
> You will never get that right. Quoting will always stay a problem.
> Forking out from C to a shell is bad practice. Forking out to a DOS
> shell is a headache. It's not SQLite related and off topic in this list.
> 
> Nevertheless, Michael Black did provide a working solution on Wed, 24
> Oct 2012 15:09:24 +, did you read it?
> 
> Please have a look at the sample C code I linked to before.
> There are more examples there.
> http://icculus.org/~chunky/stuff/sqlite3_example/
> 
> Good luck!
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: how to select " char in sqlite

2012-10-26 Thread Kees Nuyt 
On Fri, 26 Oct 2012 01:25:24 +, 
YAN HONG YE  wrote:

>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here couldn't work
>
>error:
>sqlite3 -html -header t9_engine.db "select id,partnumber,substr('src="'||pi
>c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> n.htmlError: 
>n
>ear "'operable program or batch file.
>The system cannot find the path specified.




>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here could work
>the result is:
>8
>AA34841687 000 INSONO-SOUS-MOTEUR--
>img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'  
>1
>
>
>and the best way is change 
>   to  <
>   to  >

You will never get that right. Quoting will always stay a problem.
Forking out from C to a shell is bad practice. Forking out to a DOS
shell is a headache. It's not SQLite related and off topic in this list.

Nevertheless, Michael Black did provide a working solution on Wed, 24
Oct 2012 15:09:24 +, did you read it?

Please have a look at the sample C code I linked to before.
There are more examples there.
http://icculus.org/~chunky/stuff/sqlite3_example/

Good luck!

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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