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

Black, Michael (IS) Fri, 26 Oct 2012 07:20:35 -0700

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 <stdio.h>
#include <stdlib.h>
#include <string.h>

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,'<img src=\\\"'||pic||'\\\" height=220/>' from engine where 
id>7;\" >> n.html";
    FILE *fp;
    char buf[65535];
    char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'<img src=#quot;'||pic||'#quot; height=222/>' 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,"&lt;","<");
        if (s) {strcpy(buf,s);free(s);}
        s=str_replace(buf,"&gt;","<");
        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 +0000,
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(\'<img src=\"\'||pic||\'\" height=220/>\',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('<img 
>src="'||pi
>c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> n.htmlError: 
>n
>ear "'<img src='": syntax error
>'pic' is not recognized as an internal or external command,
>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(\'<img src=\"\'||pic||\'\" height=220/>\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'<img 
>src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>system(bh1);                          //here could work
>the result is:
><TR><TD>8</TD>
><TD>AA34841687 000 INSONO-SOUS-MOTEUR--</TD>
><TD>&lt;img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/&gt;</TD>   
>//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
><TD>1</TD>
></TR>
>
>and the best way is change
>&lt;   to      <
>&gt;   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 +0000, 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

Reply via email to