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

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

[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 <yanhong...@mpsa.com> 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] how to select " char in sqlite

[Kees Nuyt]

On Thu, 25 Oct 2012 01:20: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

As we said before "here couldn't work" is insufficient to describe your
problem.  Please show us the contents of bh1 just before the system()
call, and the error codes and error messages returned.

It is bad practice to shell out to the sqlite3 commandline tool from
within a program. As you see, you'll get all kinds of quoting issues
(probably the root cause of the failure here). 

You are supposed to use the API, either the C-API
http://sqlite.org/c3ref/intro.html or some API provided by a "wrapper"
for the computer language your application is coded in.

There is excellent sample code on the internet, like
http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c

By the way, I don't think the substr() is at the right place.

Hope this helps.


-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
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

[YAN HONG YE]

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
___
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

[Black, Michael (IS)]

If Windows get FART (find and replace text) from here:
http://blog.secaserver.com/2011/07/windows-find-and-replace-text-command-line-utility/
If Unix learn sed:
http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/

Then
sqlite3 test.db
create table engine(id,partnumber,pic);
insert into engine values(1,11,'1.jpg');
insert into engine values(2,22,'2.jpg');
insert into engine values(3,33,'3.jpg');
D:\SQLite>sqlite3 -html test.db "select id,partnumber,'XXLTimg 
src='||\"XXQUOTE\"||pic||\"XXQUOTE\"||' height=220XXGT' from eng
ine;"
fart test.html XXGT ">"
fart test.html XXLT "<"
fart test.html XXQUOTE ''"

And you end up with:
1
11


2
22


3
33




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: Wednesday, October 24, 2012 4:00 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select " char in sqlite

sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as img,pcs 
from engine where id>7;" >> n.html
here   \"abc.jpg\" couldn't work.

sqlite3 -html -header t9_engine.db "select id,partnumber,'' as img,pcs from engine where id>7; ">> n.html
Same problem.

strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
Same problem.


___
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

[Clemens Ladisch]

Igor Tandetnik wrote:
> YAN HONG YE  wrote:
>> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
>> img,pcs from engine where id>7;" >> n.html
>> here   \"abc.jpg\" couldn't work.
>>
>> sqlite3 -html -header t9_engine.db "select id,partnumber,'> '||pic||' \" height=220/>' as img,pcs from engine where
>> id>7; ">> n.html Same problem.
>
> Define "couldn't work". What outcome do you observe, what outcome do you 
> expect, and how do the two differ?

I'd guess the purpose of these queries is to generate an HTML table with images.

sqlite> .mode html
sqlite> select '';
img src=something.jpg


But the sqlite shell always escapes special characters and thus cannot
generate HTML tags.

I'd recommend to export the data in some other format and then reformat
it to HTML.


Regards,
Clemens
___
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

[Igor Tandetnik]

YAN HONG YE  wrote:
> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
> img,pcs from engine where id>7;" >> n.html
> here   \"abc.jpg\" couldn't work.
> 
> sqlite3 -html -header t9_engine.db "select id,partnumber,' '||pic||' \" height=220/>' as img,pcs from engine where
> id>7; ">> n.html Same problem.

Define "couldn't work". What outcome do you observe, what outcome do you 
expect, and how do the two differ?
-- 
Igor Tandetnik

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