[sqlite] any sql command to synchronize two table

[YAN HONG YE]

Hi,
I have two table with same field:

Id int,
Content text,
Inserttime datatime,
Updatetime datatime

I want to do this:
If id not in the other table, then insert it,
if id is same, and inserttime diffirent, then insert it to the other table ;
if id is same, and updatetime diffirent, then update it to the other table ;
I don't know if exist this tool, the target is synchronize two table to the 
same except ID field.

[sqlite] how to realize md5 function

[YAN HONG YE]

sqlite> select md5('mm1234');
Error: no such function: md5

[sqlite] How to insert a pointer data to sqlite?

[YAN HONG YE]

I have a data:
Id  pid namemark
1   0   f1  sample
2   1   f2  sample
3   1   f3  sample
4   2   f4  sample
5   2   *id(2).name *id(2).mark

These means that under id(2) and id(5) have same node, if change one of the 
node, the other update auto,
How to realize this function?
Thank you!

[sqlite] excel vba use sqlite

[YAN HONG YE]

I don't know how to use sqlite in EXCEL vba?  Need I install sqlite connect 
driver?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About database structure

[YAN HONG YE]

I don't know how to create this table:

There 4 objects:

Aa, bb,cc, res

Aa is name of a1,a2,a3,a4,a5 type
Bb is name of b1,b2,b3,b4,b5 type
Cc is only 1,0 type
Res is result value

If a1 and b1 and cc=1,then the value is 10;
If a1 and b1 and cc=0,then the value is 5;
If a2 and b1 and cc=1,then the value is 12;
If a2 and b1 and cc=0,then the value is 6;
If a3 and b1 and cc=1,then the value is 8;
If a3 and b1 and cc=0,then the value is 3;
If a4 and b1 and cc=1,then the value is 10;
If a4 and b1 and cc=0,then the value is 9;
If a5 and b1 and cc=1,then the value is 20;
If a5 and b1 and cc=0,then the value is 4;
If a2 and b2 and cc=1,then the value is 30;
If a2 and b2 and cc=0,then the value is 50;
...

I want do a select cmd:
Select res from mytable where aa=a4 and bb=b3;

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

[sqlite] sqlite login password

[YAN HONG YE]

I want to know how to setting a loging sqlite passwd?
When I input:
Sqlite mtdb
Command, the console will prompt like this:
Pls input your password:

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

[sqlite] use sqlite3 in ios

[YAN HONG YE]

When I  add sqlite3.c and sqlite3.h to xcode ios cocoa project, and compiled, 
the error msg is:
Sqlite3 class is not a objective class,  who have any cocoa sqlite source, I 
don't know how to do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to use sqlite in COCOA

[YAN HONG YE]

Who can tell me how to use Sqlite in COCOA  in my MAC system?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] de...@ukr.net

[YAN HONG YE]

Hi,
Sorry, I use mysql, not in sqlite, in mysql ,I run this command, the console 
show this error: do you know why?  
Thank you very much!

mysql> UPDATE adlbk20140520a
-> SET pflopf = (
-> SELECT pflopf
-> FROM adl
-> WHERE adl.ref = adlbk20140520a.ref)
-> WHERE (
-> SELECT COUNT(*)
-> FROM (
-> SELECT 1 FROM adl
-> WHERE adl.ref = adlbk20140520a.ref
-> LIMIT 2
-> )
-> ) = 1;
ERROR 1248 (42000): Every derived table must have its own alias
mysql>

>   Message: 8
>   Date: Mon, 19 May 2014 09:55:25 +0300
>   From: Paul 
>   To: sqlite-users@sqlite.org
>   Cc: sqlite-users@sqlite.org
>   Subject: Re: [sqlite] how to write this commands?
>   Message-ID: <1400482276.625069613.b8rdb...@frv38.fwdcdn.com>
>   Content-Type: text/plain; charset="UTF-8"
>   
>   
>   > UPDATE adla1 
>   > SET pflopf = (
>   > SELECT pflopf 
>   > FROM adl 
>   > WHERE adl.ref = adla1.ref) 
>   > WHERE (
>   > SELECT COUNT(*) 
>   > FROM (
>   > SELECT 1 FROM adl 
>   > WHERE adl.ref = adla1.ref 
>   > LIMIT 2
>   > )
>   > ) = 1;
>   > 
>   > Not all sure what LIMIT 2 does there. I think a SQL-92 version
>   > would be
>   > 
>   
>   Limit, limits number of rows selected by ref. If we have found 2, no need 
> to lookup further.
>   That's why it was said to be a slightly optimized version. Generally this 
> saves some disk reads.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to write this commands?

[YAN HONG YE]

 update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) where 
select count(adl.ref) from adl=1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about merge rows

[YAN HONG YE]

I have a sqlite table like this:
id  namemnote   
7   DDD_POSTE_DE_COND   
2;AA35469850.1;DDD;ECRHEMBNETM6X100AC8Z;000

2;7903233029.1;;AA35469850.1;DDD;ECRHEMBNETM6X100AC8Z;000

3;9807260277.1;;AA36766417.1;DDD;COMMU-REGU-LIMI-VITE;001

4;9665586177.1;;AA36284384.1;DDD;COMMU-REGU-LIMI-VITE;001

5;9675673177.1;;AA36049484.1;DDD;BLOC-COMMU-COMM-BVMP;003

6;9663123380.1;;AA35651299.1;DDD;ANTENN-ELECTR-TRANSP;001DDD_POSTE_DE_COND


8   ASSISE-BASSEAA34695081  
14;9807054180.1;;AA36305186.1;DDD;BOITIE-SERVIT-INTELL;002

15;9801195980.1;;AA37380334.1;DDD;SUPPOR-BOITIE-SERVIT;000

16;9805077080.1;;AA36701967.1;DDD;BOITIE-COMMAN-CLIMAT;003

17;9804790880.1;;AA36696339.1;DDD;CAPT-DET-CHO-MOY-RET;001

18;9804790880.1;;AA36696338.1;DDD;CAPT-DET-CHO-MOY-RET;001


9   FAISCEAUX-ELEC-PDC  
1;9807698080.1;;AA35538582.1;DDD;GOUT-TAB-INF-FAI-HAB;000

2;9803453480.1;;AA35574764.1;DDD;GOUTT-TUNN-FAIS-HABI;000

3;9803453480.1;;AA35538573.1;DDD;GOUTT-TUNN-FAIS-HABI;000

4;9681595580.1;;AA36168981.1;DDD;ECR-HXEMBBOR5X160PLA;002

5;9681595580.2;;AA36168981.1;DDD;ECR-HXEMBBOR5X160PLA;002

6;967352338A.1;;AA35568067.1;DDD;MODU-LOC-ALA-ANT-VOL;004

I want to merge all the mnote, how to do this?

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

[sqlite] if possible point another table

[YAN HONG YE]

I have a table named aa like this:
id  pid namenotetablename   
1   0   s12 bbc k1
2   1   sss vac k2

another table named k1:
id  pid namenote
11  1   f2  aaa
12  1   fs  bbc

another table named k2:
id  pid namenote
31  2   f2  aaa
32  2   fs  bbc

my question is: in table aa, if possible k1 point to another table "k1"?
select tablename from aa; 
I want result is:
id  pid namenote
11  1   f2  aaa
12  1   fs  bbc
31  2   f2  aaa
32  2   fs  bbc


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

[sqlite] how to use sqlite in excel vba?

[YAN HONG YE]

I wanna use sqlite in EXCEL vba, but I don't know how to compile the sqlite ocx 
file in vc6.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] anyone know how to use ta-lib api to the sqlite database?

[YAN HONG YE]

the ta-lib.org, the ta-lib library could use in EXCEL, I wanna use it in the 
sqlite database, but I don't know how to write the c or c++ code to load the 
ta-lib api in sqlite database, anyone know it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] use sqlite to count macd value

[YAN HONG YE]

DIFF : EMA(CLOSE,12) - EMA(CLOSE,26);
DEA  : EMA(DIFF,9);
MACD : 2*(DIFF-DEA);

I wanna use sqlite to count macd value, have any idea?
thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] use sqlite to count macd value

[YAN HONG YE]

DIFF : EMA(CLOSE,SHORT) - EMA(CLOSE,LONG);
DEA  : EMA(DIFF,M);
MACD : 2*(DIFF-DEA), COLORSTICK;

I wanna use sqlite to count macd value, have any idea?
thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 66, Issue 3

[YAN HONG YE]

if i use :
sqlite3 D:\yeyh\sqlite\mmaa.db " select 
datetime(CURRENT_TIMESTAMP,'localtime');" >mdate.html
in dos command, it could be get the result file: mdate.html

but when I use c++ to run the createprocess function, It could not create the 
mdate.html file, I don't know why?


STARTUPINFO si;
PROCESS_INFORMATION pi;
si.dwFlags = STARTF_USESHOWWINDOW;
si.wShowWindow = SW_HIDE;
si.cb = sizeof(si);
ZeroMemory(&si, sizeof(si)); 
CreateProcess("sqlite3.exe", " D:\\yeyh\\sqlite\\mmaa.db  \" select 
datetime(CURRENT_TIMESTAMP,'localtime');\" >mdate.html 
",NULL,NULL,FALSE,CREATE_NO_WINDOW,NULL,NULL,&si,&pi);
WaitForSingleObject(pi.hProcess, INFINITE);

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

[sqlite] search string in the db question

[YAN HONG YE]

my table has a table like this:
c5  c6  c7
9806062380  9806062380  9806062380
9806062480  9806062680  9806062680
9806063280  9806068980  9806068980
9806062980  9806067980  9806067980
9806062280  9806067880  9806067880
9806734080  9806068280  9806068380
9806069180  9806068380  9806068680
9806063180  9806068680  9806635280

I wanna to find in the column c5,c6,c7 include 9806067880 like this:

select * from mytable where * like '9806067880';

it's right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update to limits infomation

[YAN HONG YE]

I hava a table like this:
id,name,score,rank
1,anna,80,0
2,qera,65,0
6,kero,90,0
10,rosa,95,0

what I would like to do is to update the rank position.  I have this,

update mytable set rank= 1 where max(score);
update mytable set rank= 2 where max(score-1) ;


the result should be like this:
id,name,score,rank
1,anna,80,3
2,qera,65,4
6,kero,90,2
10,rosa,95,1

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

[sqlite] about delete problem

[YAN HONG YE]

create table mytable( ID integer primary key autoincrement, name nvarchar(32) );
insert into mytable(name) values("aa1");
insert into mytable(name) values("aa2");
insert into mytable(name) values("aa3");
insert into mytable(name) values("aa4");
insert into mytable(name) values("aa5");
insert into mytable(name) values("aa6");
delete from mytable where id>3;
insert into mytable(name) values("aa7");
insert into mytable(name) values("aa8");
select * from mytable;
1|aa1
2|aa2
3|aa3
7|aa7
8|aa8

I wish after I  delete any rows from mytable, and then when I insert into any 
row into mytable, The Id should follow the last exists in mytable.
in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
then when I add any column into mytable, the ID should from 3 to 
the next column, how to do this? maybe have any rubbish in the database when 
delete from mytable, how to clean that rubbish?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update value from another tow tables

[YAN HONG YE]

updat table1 set name=selet name from table2 where table2.id=table1.id;

this command couldn't work, how to update this status?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] what diffrent with this two sql command?

[YAN HONG YE]

  SELECT c FROM mytable AS m1 WHERE NOT EXISTS (SELECT b FROM mytable AS m2 
WHERE m2.b = m1.c);

  SELECT c FROM mytable WHERE NOT EXISTS (SELECT b FROM mytable  WHERE b = c);




Sincerely,
Cordialement / Best Regards / Mit freundlichen Grüßen / Cordiali saluti !
___
---
YE YANHONG/???- DASI/DRDS/ITVC/APFS/AVPM
PSA PEUGEOT CITROEN(CHINA) AUTOMOTIVE TRADE CO,. LTD SHANGHAI BRANCH
Tel: (+86) 21-2419 5488  Mobile:(+86) 13816808338  Fax: (+86) 21-2419 5004
PSA internal call: 49 5488
Email: yanhong...@mpsa.com
Address: 6th Floor, Building 1, No. 1528, Gumei Road,
Xuhui District, Shanghai
Post Code:200233
??: ?1528?A1?6?

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

[sqlite] get diffrent column C from table

[YAN HONG YE]

b   c
854809  868763
854808  854809
854980  854808
854974  854980
854973  854974
854780  854973
854778  854780
854769  854778
854768  854769
854738  854768
854691  854738
854690  854691
853709  854690
856707  853709
856697  856707
856695  856697
927681  856695
927696  927681
927695  927696
927694  927695
927693  927694
927692  927693
927691  927692
927690  927691
927690
910986
910985
I have a table like this, how to get result  not exists:b in c

my command is :

select c from mytable where b not in (select c from mytable);

but not correct.
how to get correct result?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 28

[YAN HONG YE]

b   c
897313  897313
854809  868763
854808  854809
854980  854808
854974  854980
854973  854974
854780  854973
854778  854780
854769  854778
854768  854769
854738  854768
854691  854738
854690  854691
853709  854690
856707  853709
856697  856707
856695  856697
927681  856695
927696  927681
927695  927696
927694  927695
927693  927694
927692  927693
927691  927692
927690  927691
927690
910986
910985
I have a table like this, how to get result  not exists:b=c

my command is :
select c from mytable where not exists( b=c);
but not correct.
how to get correct result?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about date question

[YAN HONG YE]

sqlite> select strftime(importdate) from t93c_adl3 limit 5;
2012-9-13
2012-9-5
2012-10-9
2012-10-9
2012-10-9
sqlite> select strftime('%y-%m-%d',importdate) from t93c_adl3 limit 5;

 
//--why have noting in the result?




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

[sqlite] about date question

[YAN HONG YE]

The result let crazy.

sqlite> select distinct importdate from t93c_adl3 order by importdate desc limit
 12;
2012-9-7
2012-9-6
2012-9-5
2012-9-4
2012-9-3
2012-9-29 //---here ,  why?
2012-9-28
2012-9-27
2012-9-26
2012-9-25
2012-9-24
2012-9-20
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about date question

[YAN HONG YE]

sqlite> select fmn,DATE('NOW'),supplierDate,date('now'),strftime(supplierDate),s
trftime(supplierDate)-date('now') from t93c_adl where strftime(supplierDate)

why use supplierDate,date('now'),strftime(supplierDate) the result is not same 
result format?
the one is 2012-11-09
another is 12/01/2012
?
and how to get the subtraction of two column?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to use julianday function

[YAN HONG YE]

sqlite> select 
fmn,DATE('NOW'),supplierDate,julianday('now'),julianday(supplierDate),julianday('supplierDate')
 from t93c_adl where supplierDate>julianday('now') limit 5 ;
854954|2012-11-09|12/01/2012|2456240.61186149||
854952|2012-11-09|03/01/2013|2456240.61186149||
635604|2012-11-09|05/27/2011|2456240.61186149||
635605|2012-11-09|03/16/2012|2456240.61186149||
635607|2012-11-09|09/15/2012|2456240.61186149||
sqlite>


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

Re: [sqlite] couldn't use is null function

[YAN HONG YE]

sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s not null and fmn like '85495%';
854954|R
854952|  
//-here is 
NULL, but I don't know why it shows in the not null result
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s null and fmn like '85495%';
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE fmn like '85495%';
854954|R
854952|
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite>



Message: 6
Date: Wed, 7 Nov 2012 08:44:01 +
From: Simon Davies 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] couldn't use is null function
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On 7 November 2012 08:27, YAN HONG YE  wrote:
> SELECT * FROM ADL WHERE Project_grading is null;
> can't select anything.

sqlite> create table ADL( id integer primary key, data text,
Project_grading integer );
sqlite> insert into ADL( data, Project_grading ) values( '1', 1 );
sqlite> insert into ADL( data, Project_grading ) values( '2', 2 );
sqlite> insert into ADL( data ) values( '3' );
sqlite> insert into ADL( data, Project_grading ) values( '4', 4 );
sqlite> insert into ADL( data ) values( '5' );
sqlite> insert into ADL( data, Project_grading ) values( '6', 6 );
sqlite>
sqlite> select * from ADL;
1|1|1
2|2|2
3|3|
4|4|4
5|5|
6|6|6
sqlite>
sqlite> select * from ADL where Project_grading is null;
3|3|
5|5|

Regards,
Simon

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

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 7

[YAN HONG YE]

select date( supplierDate, '+2 days' ) ;
use this cmd couldn't solve the problem;
will have nothing on this column:

sqlite> select fmn,'84 day'+supplierDate from t93c_adl where fmn like '85493%';
854939|95
854938|95
854937|95
sqlite> select fmn,date('84 day'+supplierDate) from t93c_adl where fmn like '854
93%';
854939|-4712-02-27
854938|-4712-02-27
854937|-4712-02-27
sqlite> select fmn,date(supplierDate) from t93c_adl where fmn like '85493%';
854939|
854938|
854937|
sqlite> select fmn,supplierDate from t93c_adl where fmn like '85493%';
854939|11/15/2012
854938|11/30/2012
854937|11/30/2012
sqlite> select fmn,date(supplierDate,'+2 days') from t93c_adl where fmn like '85
493%';
854939|
854938|
854937|
sqlite>



Message: 10
Date: Wed, 7 Nov 2012 10:48:24 +
From: Simon Davies 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] FW: about date question
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On 7 November 2012 09:42, YAN HONG YE  wrote:
> the table had a column: SupplierDate Date
> I wanna add 84 days :
> SELECT SupplierDate+84 as date1  FROM ADL;
> BUT result have noting
> when use:
> select date(supplierdate+'2 day') from t93c_adl limit 3;
> the result is:
> -4713-12-08
> -4713-11-29
> -4713-12-01
>

select date( supplierDate, '+2 days' )

http://www.sqlite.org/lang_datefunc.html

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

[sqlite] FW: about date question

[YAN HONG YE]

the table had a column: SupplierDate Date
I wanna add 84 days :
SELECT SupplierDate+84 as date1  FROM ADL;
BUT result have noting
when use:
select date(supplierdate+'2 day') from t93c_adl limit 3;
the result is:
-4713-12-08
-4713-11-29
-4713-12-01

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

[sqlite] about date question

[YAN HONG YE]

the table had a column: SupplierDate Date
I wanna add 84 days :
SELECT SupplierDate+84 as date1  FROM ADL;
BUT result have noting
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] couldn't use is null function

[YAN HONG YE]

SELECT * FROM ADL WHERE Project_grading is null;
can't select anything.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite on Nokia symbian

[YAN HONG YE]

I wanna use sqlite on my Nokia C7 symbian, but I don't know how to compile 
sqlite c api or sqlite source on the QT symbian.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: 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

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--
   
//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'  
1


and the best way is change 
to  >

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

[sqlite] FW: 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

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--
   
//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'  
1


___
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

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

[YAN HONG YE]

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] how to select "

[YAN HONG YE]

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert if in SQL cmd

[YAN HONG YE]

I need a function to run step 2 in sql cmd, but I don't know how to write in 
the sql environment.

1. BEGIN TRANSACTION;
2. if today is mondy to fridy,then run 3-6
ELSE run 6.
3. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not 
finalized or reset;
4. execute stmt B (DELETE FROM foo WHERE rowid=1), B is prepared, executed and 
finalized;
5. access column data via stmt A
6. COMMIT
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write sql cmd for this situation

[YAN HONG YE]

"Igor Tandetnik"  reply:

YAN HONG YE  wrote:
> mysql sql cmd is:
> 
> select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. 
> max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2
> where tb2.bb in (select bb from tb1 where bb group by bb  having count(bb)) ;

With all due respect, this query doesn't make any sense. Could you explain in 
your own words exactly what data do you want to calculate?
-- 
Igor Tandetnik

I have a 2 table:
1.  tb1:

aa bb cc   mdate
1   d1 2.5 2012-08-07
2   d2 3.3 2012-08-07
3   d1 6.4 2012-08-09

2.  tb2:
aa bb cc
1.  d1 5.3
2.  d2 7.6


I wanna the result is:
bb ccmmd mmc
d1 5.3  23.9

the result 3.9 mean is 6.4-2.5  ,  mdate 2012-08-09 - 2012-08-07 =2, and 6.4 - 
2.5=3.9

how to get the result by sqm cmd?
thanks.

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

[sqlite] how to write sql cmd for this situation:

[YAN HONG YE]

I have a 2 table:
1.  tb1:

aa bb cc   mdate
1   d1 2.5 2012-08-07
2   d2 3.3 2012-08-07
3   d1 6.4 2012-08-09

2.  tb2:
aa bb cc
1.  d1 5.3
2.  d2 7.6

mysql sql cmd is:

select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. 
max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2 where tb2.bb in
(select bb from tb1 where bb group by bb  having count(bb)) ;

I wanna the result is:
bb ccmmd mmc
d1 5.3  23.9

the result 3.9 mean is 6.4-2.5  ,  mdate 2012-08-09 - 2012-08-07 =2, and 6.4 - 
2.5=3.9

how to get the result by sqm cmd?
thanks.

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

[sqlite] export html error

[YAN HONG YE]

C:\t9>sqlite3 -html -header t9_engine.db "select id,partnumber,'',pcs from engine where id>7 " >> n.html

Error: near "&": syntax error
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select ... where problem

[YAN HONG YE]

Message: 25
Date: Thu, 20 Sep 2012 07:44:00 -0600
From: "Keith Medcalf" 
To: "General Discussion of SQLite Database" 
Subject: Re: [sqlite] select ... where problem
Message-ID: 
Content-Type: text/plain; charset="utf-8"


>>> >>> (select CURDATE()),
>>> What is a CURDATE()?  Is that a custom scalar function of your own?  Did 
>>> you remember to compile it into the engine or load the module containing 
>>> the function?
>>> >>> where datetime('now', 'localtime') > 15:00
>>> 15:00 is unparseable.  Perhaps you mean 
>>> >>> where datetime('now', 'localtime') > '15:00'
>>> But if so you can elimate the where clause entirely because '2' is always 
>>> greter than '1' ...
>>> I am surprised the "Error Message" did not point out these errors.

---

 
I means the sql cmd had no "from ", only "select",  "where":


insert into history (mdwkup,macdup,mdate,mdwkmd) SELECT 
(select count(*) FROM fxj where mdwkup=1),
 (select CURDATE()),
(select round(avg(mdwkmd),2) from fxj) where datetime('now',
'localtime')>"15:00";


if run this cmd:

insert into history (mdwkmd) 
select round(avg(mdwkmd),2) from fxj where datetime('now',
'localtime')>15:00

will successful.

!
Sincerely,
Cordialement / Best Regards / Mit freundlichen Grüßen / Cordiali saluti !
___
---

YE YANHONG - DASI/DRDS/ITVC/APFS/AVPM
PSA PEUGEOT CITROEN(CHINA) AUTOMOTIVE TRADE CO,. LTD SHANGHAI BRANCH 
Mobile : (+86) 138 1680 8338
Office : (+8621) 2419 5488
PSA internal call: 49 5488
Email: yanhong...@mpsa.com
Address: 5th – 8th floor, A1 Bldg., 1528 Gumei Road,
Xuhui District, Shanghai

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

[sqlite] problem about output html in c++

[YAN HONG YE]

sqlite3 -html -header t9_engine.db "select id,partnumber,pic,pcs from engine 
where id>7;" >> mm.html

the pic result is "c:\abc.jpg"...
 but I wanna the result is 
""

how to do? maybe use sqlite3_step, but I don't know how to realize.
thank you!


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

[sqlite] how to re_update the id item

[YAN HONG YE]

my database have a column named id auto_increament inter primary key,  when I 
delete any rows , the id item may be indiscriminate , how to update it and 
reorder the 
column from 1 to max(id) order by id asc?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about sqlite3_create_function

[YAN HONG YE]

   who give me a sample about function sqlite3_create_function,I  can't search 
by google.
thank you!
 sqlite3_create_function(db, "hello_newman", 0, SQLITE_UTF8, 0,
  hello_newman, 0, 0);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update moving avg value

[YAN HONG YE]

select round(((select avg(close) from test2 b where b.tkid between a.tkid-12 
and a.tkid) -
(select avg(close) from test2 b where b.tkid between a.tkid-26 and a.tkid)),2) 
from test2 a

could be select the result,but when update use this sql script:

update test2 set diff =(select round(((select avg(close) from test2 b where 
b.tkid between a.tkid-12 and a.tkid) -
(select avg(close) from test2 b where b.tkid between a.tkid-26 and a.tkid)),2) 
from test2 a) 

all of the result is 0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] datetime question

[YAN HONG YE]

create table test( Date datetime);
insert test now();
select date+2 as bbb,date-12 as cc from test;
the result is 
2014,2000
I wanna know how to add any day use sql command
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to update the Moving average value

[YAN HONG YE]

select dexin, code,new, (select avg(new) from tb1 where dexin between dexin-3 
and dexin)as mavg from tb1;

the result is only one value, 
and table only one:  tb1
I don't know why have tbl b and tbl a





>From: "Keith Medcalf" 
>To: "General Discussion of SQLite Database" 
>Subject: Re: [sqlite] how to update the Moving average value
>Message-ID: <545265ad8b7cfb45b4f7f9160b8cc...@mail.dessus.com>
>Content-Type: text/plain; charset="us-ascii"
>
>
>select id, sales_vol, (select avg(sales_vol) as mavg 
> from tbl b
>where b.id between a.id - 3 and a.id)
>  from tbl a;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert two select count(*) problem

[YAN HONG YE]

The sqlcmd is:
insert into mydb (co1,co2) select count(*) from db1 where db1.sco1>80 ,select 
count(*) from db2 where db2.sco2>90;
It doesn't work.  If one column, it can do,but when many column count(*), I 
don't know how to write the sql command.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 56, Issue 5

[YAN HONG YE]

I wanna insert into a value to my sqlte db:

insert into mydb (number1) select count(number1) from db2 where number1>80;

but it doesn't work 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query function release problem

[YAN HONG YE]

You could download the origin zip source file by the link:

www.yycred.com/sqlte_crypto1.zip

when I use command 
" jii select ccc" 
it shows a problem:

"jii.exe has encountered a problem and needs to close.  We are sorry for the 
inconvenience."

I couldn't resolve it.


void query_table(sqlite3 *l_db) 
{
char **result;
char buffer2[324]="0";
int row,column;
char *sql;

char *error;
int i;

sprintf(sql,"select * from  myprivate;");
sqlite3_get_table(l_db,sql,&result,&row,&column,&error);


for(i=0;i<(row+1)*column;i++)
{
if(i<2 || i%3==0)
{
printf("%-24s|",result[i]);

continue;
}
if((i+1)%3 == 0)
{
//printf("hello\n");
printf("%-8s;\n",result[i]);

continue;
}

//printf("%s\n",result[i]);
char *ppu=ldll(sqlkey,result[i],2);
printf("%-24s|",ppu,2);
free(ppu);

}
sqlite3_free_table(result); 
printf("d\n");
free(sql);
free(result);


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

Re: [sqlite] sqlite3_stat problem (YAN HONG YE)

[YAN HONG YE]

sqlite3_bind_text(stat,1,uu2,140,NULL); 
change to 
sqlite3_bind_blob(stat,1,uu2,140,NULL);
it's ok! thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_stat problem

[YAN HONG YE]

sprintf(sql,"insert into student select 1,?,22;");


sqlite3_prepare(l_db,sql,-1,&stat,0);
char *uu1="bb";
char *uu2="Öйú";


HINSTANCE mod; 
Connect foo;
mod = LoadLibrary("crypto.dll");
printf("1successful load dll1\n");
printf("2successful load dll2\n");
printf("3successful load dll3\n");
printf("4successful load dll4\n");
if((foo = (Connect)GetProcAddress(mod, "CryptMe")) != NULL) 
{
printf("5successful load dll5\n");
foo(uu1,uu2,1);   //here to 
encode the uu2 to " Íð¦·"
}


printf("%s\n",uu2);   //the result:" Íð¦·"

printf("7successful load dll7\n");
sqlite3_bind_text(stat,1,uu2,140,NULL);
printf("8successful load dll8\n");
sqlite3_step(stat);
printf("9successful load dll9\n"); //here not 
print, shou the error msg:  jii.exe has encountered a problem and needs to  
  //close.  We are sorry 
for the inconvenience.
sqlite3_finalize(stat);
printf("10successful load dll10\n");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] no such function problem

[YAN HONG YE]

I write a function in my program:

char *ldll(char *s1)
{
return s1;
}

and use it to modify my sqlite db:

sprintf(sql,"insert into student select 3,ldll('bb'),22;");

but the error msg is "no such function: ldll"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] read sql script file

[YAN HONG YE]

in the shell.c source file ,have a function .read file, and I wanna use it to 
my c++ code, when I hava a sql script file,such as :

create table test (id integer primary key, value text);
insert into test (id, value) values(1, 'eenie');
insert into test (id, value) values(2, 'meenie');
insert into test (value) values('miny');
insert into test (value) values('mo');

now I wanna use the shell.c function to run the script, but I don't know how to 
use c++ code to  achieve the target.
I only found on line in shell.c:

".read FILENAME Execute SQL in FILENAME\n" in 
"static char zHelp[]"

I suggest it use callback.

static int _is_complete(char *zSql, int nSql){
  int rc;
  if( zSql==0 ) return 1;
  zSql[nSql] = ';';
  zSql[nSql+1] = 0;
  rc = sqlite3_complete(zSql);
  zSql[nSql] = 0;
  return rc;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to write this sql command?

[YAN HONG YE]

INSERT INTO myTable (Rd, en, ue) VALUES ("aa","bb",select ue from othertable);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: datatype mismatch (Yanhong Ye)

[YAN HONG YE]

I know the reason: 
if I change the file encode to latin, it will suecess import the data;
but when I change the txt file to utf8 encode, It will get the error:
Error: datatype mismatch
I wanna know how to import utf8 encode file to my database?

>Date: Mon, 16 Jul 2012 02:19:12 -0600
>From: "Keith Medcalf" 
>To: "General Discussion of SQLite Database" 
>Subject: Re: [sqlite] Error: datatype mismatch
>Message-ID: 
>Content-Type: text/plain;  charset="us-ascii"
>
>
>sqlite> CREATE TABLE webinfo (inx INTEGER PRIMARY KEY ,website varchar(50) 
>UNIQUE,username varchar(50),password varchar(50));
>sqlite> .mode csv
>sqlite> .import kk.txt webinfo
>sqlite> select * from webinfo;
>0,nytime,gf,a1..7
>1,bbc,1982,tbth432
>2,oknic,y...@sohu.com,bbaa1122
>sqlite>
>
>Did you forget to say ".mode csv"?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: datatype mismatch

[YAN HONG YE]

the table is:
CREATE TABLE webinfo (inx INTEGER PRIMARY KEY ,website varchar(50) UNIQUE,userna
me varchar(50),password varchar(50));

and the txt file kk.txt is utf8:
0,nytime,gf,a1..7
1,bbc,1982,tbth432
2,oknic,y...@sohu.com,bbaa1122

when I use
" .import kk.txt webinfo"
in cmd line I got the error msg:
Error: datatype mismatch
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] cann't work

[YAN HONG YE]

two same structure tables, when use this sql:
insert into table2  values(select * from table1 where filename like "%55");

but show error: sqlite error 1 - near "select": syntax error
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group by z HAVING count(z>12.5)=2

[YAN HONG YE]

If I wanna to know how much z>12.5 result in the database,could I use this 
following cmd?
SELECT * from TableA WHERE z in (
   SELECT z FROM TableA GROUP BY z
   HAVING count(z>12.5)=2 
);

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

[sqlite] order by clause should come after union not before

[YAN HONG YE]

select *  from hbc order by cmc desc limit 10
union
select * from hbc where qph>0
union
select * from hbc where hctl=1

this sql cmd cause the error:
order by clause should come after union not before


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

[sqlite] rekey

[YAN HONG YE]

hello, when I use sqlite3_key to crypt my sqlite database "my.db" and the key 
is "abc",
and when I use sqlitespy to open the database, then sqlitespy require me to 
input the key,
when I input "abc" to it,but show me couldn't open the database, I don't know 
why, 
these following function is the source:

1:
j = sqlite3_key(db, argv[1], 2);

2:
  j = sqlite3_rekey(db,NULL, 0 );

3:
int sqlite3_key(sqlite3 *db, const void *zKey, int nKey)
{
  /* The key is only set for the main database, not the temp database  */
  return sqlite3CodecAttach(db, 0, zKey, nKey);
}

4:
int sqlite3CodecAttach(sqlite3* db, int nDb, const void* zKey, int nKey)
{
  /* Attach a key to a database. */
  Codec* codec = (Codec*) sqlite3_malloc(sizeof(Codec));
  CodecInit(codec);

  /* No key specified, could mean either use the main db's encryption or no 
encryption */
  if (zKey == NULL || nKey <= 0)
  {
/* No key specified */
if (nDb != 0 && nKey > 0)
{
  Codec* mainCodec = (Codec*) 
mySqlite3PagerGetCodec(sqlite3BtreePager(db->aDb[0].pBt));
  /* Attached database, therefore use the key of main database, if main 
database is encrypted */
  if (mainCodec != NULL && CodecIsEncrypted(mainCodec))
  {
CodecCopy(codec, mainCodec);
CodecSetBtree(codec, db->aDb[nDb].pBt);
#if (SQLITE_VERSION_NUMBER >= 3006016)
mySqlite3PagerSetCodec(sqlite3BtreePager(db->aDb[nDb].pBt), 
sqlite3Codec, sqlite3CodecSizeChange, sqlite3CodecFree, codec);
#else
#if (SQLITE_VERSION_NUMBER >= 3003014)
sqlite3PagerSetCodec(sqlite3BtreePager(db->aDb[nDb].pBt), sqlite3Codec, 
codec);
#else
sqlite3pager_set_codec(sqlite3BtreePager(db->aDb[nDb].pBt), 
sqlite3Codec, codec);
#endif
db->aDb[nDb].pAux = codec;
db->aDb[nDb].xFreeAux = sqlite3CodecFree;
#endif
  }
  else
  {
CodecSetIsEncrypted(codec, 0);
sqlite3_free(codec);
  }
}
  }
  else
  {
/* Key specified, setup encryption key for database */
CodecSetIsEncrypted(codec, 1);
CodecSetHasReadKey(codec, 1);
CodecSetHasWriteKey(codec, 1);
CodecGenerateReadKey(codec, (char*) zKey, nKey);
CodecCopyKey(codec, 1);
CodecSetBtree(codec, db->aDb[nDb].pBt);
#if (SQLITE_VERSION_NUMBER >= 3006016)
mySqlite3PagerSetCodec(sqlite3BtreePager(db->aDb[nDb].pBt), sqlite3Codec, 
sqlite3CodecSizeChange, sqlite3CodecFree, codec);
#else
#if (SQLITE_VERSION_NUMBER >= 3003014)
sqlite3PagerSetCodec(sqlite3BtreePager(db->aDb[nDb].pBt), sqlite3Codec, 
codec);
#else
sqlite3pager_set_codec(sqlite3BtreePager(db->aDb[nDb].pBt), sqlite3Codec, 
codec);
#endif
db->aDb[nDb].pAux = codec;
db->aDb[nDb].xFreeAux = sqlite3CodecFree;
#endif
  }
  return SQLITE_OK;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to show image

[YAN HONG YE]

sqlite3_exec(db,"create table image (filename varchar(128) unique,img 
blob);",0,0,&zErrMsg);
and put image to my sqlite db:
sqlite3_prepare(db,"insert into image values('.jpg',?);",-1,&stat,0);

I export the html use this command:
sqlite3 -html film.db "select * from image;">my.html
but I couldn't see the  jpg in my web. why?



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

[sqlite] how to show sqlite db image in html

[YAN HONG YE]

sqlite3_exec(db,"create table image (filename varchar(128) unique,img 
blob);",0,0,&zErrMsg);
and put image to my sqlite db:
sqlite3_prepare(db,"insert into image values('.jpg',?);",-1,&stat,0);

the problem is:
how to show my image in the web?
sqlite3 -html film.db "select * from film;">my.html
is it right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query runs in SQLite Database Browser but not in iPad app

[YAN HONG YE]

I have a sqlite db file in my pc, and now I wanna to copy my db file to my ipad.
I need open db files in ipad,but I don't know how to open the file. and where 
should I copy my db files to my ipad.

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

[sqlite] copy table to another db file

[YAN HONG YE]

I have two db files:

sqlite3 *db1;
sqlite3 *db2;
rc1 = sqlite3_open("myfile1", &db1);
rc2 = sqlite3_open("myfile2",  &db2);

I want to copy db1.table1 to db2 file, but I don't know how to do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to write "\n\r" each line in the output txt file?

[YAN HONG YE]

>when I use :
>
>.output akk.txt
>select * from dhq where qph>0;
>.output stdout
>
> command to write a txt file,I found no "\n\r" in the each line,

when I use ultraedit to open the txt file, I found end of each line is 0x0D, 
How to write "0x0D 0x0A" each line?
may be should change the sqlite3.c or shell.c? 

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

[sqlite] how to write line feed "\n\r" when output a txt file

[YAN HONG YE]

when I use :

.output akk.txt
select * from dhq where qph>0;
.output stdout

command to write a txt file,I found no "\n\r" in the each line  , how to write 
"\n\r" each line in the output txt file? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] run sql file

[YAN HONG YE]

I have a database {TABLE t( num integer, date text );} file, and a bbb.sql file 
content:

INSERT INTO "t" VALUES(1,'1/1/2011');
INSERT INTO "t" VALUES(2,'1/1/2011');
INSERT INTO "t" VALUES(2,'2/1/2011');
INSERT INTO "t" VALUES(3,'1/1/2011');
INSERT INTO "t" VALUES(3,'2/1/2011');
INSERT INTO "t" VALUES(3,'3/1/2011');
INSERT INTO "t" VALUES(5,'7/1/2011');

DELETE FROM t WHERE rowid NOT IN (
SELECT rowid FROM
( SELECT rowid, num, date FROM t ) CROSS JOIN
( SELECT num min_num, min( date ) min_date FROM t GROUP BY num ) ON
num=min_num AND date=min_date
);

I want to run the bbb sql file in command ,how to write command in c or c++ or 
console command line?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 9

[YAN HONG YE]

On  Wed, 9 May 2012 02:24:58 +0100
Simon Slavin  wrote:

> alter table myref add stkcode varchar(30);
> update myref set stkcode = dzhhq.stkcode;
> 
> this sql command couldn't run in my sqlite.

It does not know which row from the table dzhhq it is meant to use.

Simon.

I means:
use another table column, to update the current table column: like this:
alter table myref add upfu;
update myref set upfu =dzhhq.upfu where exists (select * from dzhhq where 
myref.stkname=dzhhq.stkname);

but it said no such column: dzhhq.upfu
but dzhhq.upfu really exist.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] two table update question

[YAN HONG YE]

alter table myref add stkcode varchar(30);
update myref set stkcode = dzhhq.stkcode;

this sql command couldn't run in my sqlite.
 

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

[sqlite] inner join problem

[YAN HONG YE]

update dzhhq set mnote=
case when  mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
else mnote
end

is ok, but when I use this cmd then appear error msg:

update dzhhq set mnote=
case when mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how much "case...when..." command?

[YAN HONG YE]

UPDATE bb SET Slevel =
CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
case...
csse...
...
CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;
in this command, I don't know sqlite allow how much "case...when..." in one sql 
command.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] globe variable convert to sql

[YAN HONG YE]

int iwk is a globe variable,sql command is:

update mydb set ml=  case iwk>5 and score>80 when 1 then 1 else 0 end;

 but command run failed,the reason is iwk varible not a sql variable,
how can I convert globe variable to sql variable?


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

[sqlite] Select time('now')

[YAN HONG YE]

the current time is 15:15
when I use this following command:
sqlite> Select time('now');
return 07:15:42
not current time,why?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite gui app

[YAN HONG YE]

hello, I  wana show my sqlite database in wxwidgets gui library,but I don't 
know how to do this.
could you tell me any resource about this?
thank you!

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

[sqlite] two tables question

[YAN HONG YE]

update dzhhq  set dzhhq.mlevel=
case 
 dzhhq.stkname=myref.stkname when 1 then 88 else 0   
end;

show error:
Query Error: near ".": syntax error Unable to execute statement

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

[sqlite] how to update value of string

[YAN HONG YE]

my sqlite database "db" have a column remarks text default "2012", I want to 
add string "high" to the string of remark as "2012high" when the score >80

and add string "middle" to the string of remark as "2012middle" when the 
score>60 and score <80
 
update the db.remarks value strcat(remark,"high") where db.score>80

how to do this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_transaction trans(db); can't work in c, I don't know why?

[YAN HONG YE]

sqlite_transaction trans(db); 
sqlite3_prepare(db,"insert into image values('.rar',?);",-1,&stat,0); 
sqlite3_bind_blob(stat,1,ffile,filesize,NULL); 
sqlite3_step(stat); 
free(ffile); 
sqlite3_finalize(stat); 
trans.commit();
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_transaction can't work

[YAN HONG YE]

sqlite3_transaction trans(db); can't work in c, I don't know why?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about The open-source Fossil version control system

[YAN HONG YE]

Date: Tue, 03 Apr 2012 20:56:15 +0200
Kees Nuyt  wrote:

>The open-source Fossil  version control system
>used by SQLite contains an implementation of system() that correctly deals
>with UTF8 even on windows systems.  Perhaps the OP can copy some of the
>code from that.  LInk:
>
>http://www.fossil-scm.org/fossil/artifact/70e4b10c0208b3?ln=685-707
>
> But I don't know how to write.

Well, perhaps today you don't know how to write code that uses the
example, but if you take some time to learn it, you may be able to do it
in a few weeks or months.
Or you may want to hire someone who can do it for you.

-- 
When I open the source on the page, but I don't found 
  #include "config.h"
  #include "main.h"
file, where it is?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to use c binding and write output in c

[YAN HONG YE]

Date: Mon, 2 Apr 2012 00:37:18 -0400
From: Richard Hipp  wrote:

On Sun, Apr 1, 2012 at 11:21 PM, Roy Tam  wrote:

> 2012/4/2 YAN HONG YE :
> > when I run this following code  , the html file encoding changed to
> GB2312,not utf-8, I don't know why?
> > system("echo ^ >mm.html");
> >   system("sqlite3 -html -header foods.db \"select * from dzh where
> qph15>10;\" >>mm.html");
> >   system("echo ^ >>mm.html");
>
> You're windows user, right?
> system() in windows will convert all things to System Codepage
> (CP_ACP), so please use C bindings and write output in C but not using
> system().
>

The open-source Fossil <http://www.fossil-scm.org/> version control system
used by SQLite contains an implementation of system() that correctly deals
with UTF8 even on windows systems.  Perhaps the OP can copy some of the
code from that.  LInk:

http://www.fossil-scm.org/fossil/artifact/70e4b10c0208b3?ln=685-707


--
But I don't know how to write  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 52, Issue 1

[YAN HONG YE]

when I run this following code  , the html file encoding changed to GB2312,not 
utf-8, I don't know why?
system("echo ^ >mm.html");
   system("sqlite3 -html -header foods.db \"select * from dzh where qph15>10;\" 
>>mm.html");
   system("echo ^ >>mm.html");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to wrie "" char in my html file?

[YAN HONG YE]

>sqlite3 -html C:\mydatabase\mydzh.db "select ''">mm.html
this command result is not  in the mm.html file, it's this following 
text:


not I wanted, how to wrie  char in my html file?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] about sqlite3_exec function


2012-03-30

Thread
YAN HONG YE
sqlite3_exec( db, "???", 0, 0, &pErrMsg);

I wanna add this following command into sqlite3_exec func:
"sqlite3 -html -header mydzh.db \"select * from dhq where ph15>10;\" >mm.html"
 like this:
sqlite3_exec( db, "-html -header \"select * from dhq where ph15>10;\" 
>mm.html", 0, 0, &pErrMsg);

but it doesn't  work
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] put jpg into sqlite database


2012-03-30

Thread
YAN HONG YE
this following code is my import girl.jpg to my sqlite database, but I don't 
know why JPG didn't up to the bin.db.

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

#pragma comment(lib, "sqlite3.lib")

sqlite3 * db; 

sqlite3_stmt *stat; 
int main(int argc, char **argv)
{
char *zErrMsg=0; 
FILE *fp=NULL; 
long filesize=0; 
char* ffile=NULL; 
char* buf=NULL;
sqlite3_open("Bin.db",&db);
if(db==NULL)
{ return 0;} 
fp=fopen("girl.jpg","rb");
if(fp!=NULL) 
{fseek(fp,0,SEEK_END);
filesize=ftell(fp);
fseek(fp,0,SEEK_SET);
ffile=(char*)malloc(filesize);

size_t sz=fread(ffile,sizeof(char),filesize,fp);
printf("%s",ffile);
printf("%d",filesize);
fclose(fp);
} 

sqlite3_exec(db,"create table image (filename varchar(128) unique,img 
blob);",0,0,&zErrMsg); 

sqlite3_prepare(db,"insert into image values  ('girl.jpg',?)",-1,&stat,0); 

sqlite3_bind_blob(stat,1,ffile,filesize,NULL); 

sqlite3_step(stat); 
free(ffile); 

sqlite3_finalize(stat); 

sqlite3_close(db); 


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



[sqlite] how to merge this three command in c++


2012-03-29

Thread
YAN HONG YE
This follow 3 commands,I wanna merge to c++,but don't know how to do:

sqlite3 my.db " select '' " > my.html
sqlite3 -html -header my.db " select * from BOD " >> my.html
sqlite3 my.db " select '' " >> my.html

is this follow right?
char bbb[300];
strcpy(bbb,"BEGIN\n " select '' " > my.html\n  \
-html -header " select * from BOD " >> my.html\n  \
" select '' " >> my.html\n  \
COMMIT;

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



[sqlite] how to add time in a new column


2012-03-28

Thread
YAN HONG YE
sqlite> alter table dzh add mtime NOT NULL DEFAULT CURRENT_TIME;
Error: Cannot add a column with non-constant default
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] how to built xml file from sqlite database?


2012-03-28

Thread
YAN HONG YE
how to built xml file from sqlite database?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] .headers error


2012-03-26

Thread
YAN HONG YE
C:\sqlite\lib>sqlite3 -html foods.db "select * from dzh;" >mm.html "-headers on"

sqlite3: Error: too many options: "-headers on"
Use -help for a list of options.
how to write this command?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] HOW TO EXPORT TABLE HEAD


2012-03-26

Thread
YAN HONG YE
WHEN I export sqlite database to a html file or txt file, I couldn't know how 
to include the database table head.
who can tell me? 
Thank you!

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



[sqlite] how to export to html file with table


2012-03-26

Thread
YAN HONG YE
Date: Mon, 26 Mar 2012 08:22:56 +0200
From: Steinar Midtskogen 

to export to the html file  command is:

sqlite3 -html film.db "select * from film" > mm.html

but the result had no table,only a text file without line feed, how to export 
the html with tables and table column header?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] how to export to html file?


2012-03-25

Thread
YAN HONG YE
my sqlite database want to export to html file, I know the command 
sqlite3 -html film.db "select * from film;" 
could show the table in cmd window, but how to export to the html file  like 
sqlite3 -html film.db  mm.html "select * from film;"
Thank you!

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



[sqlite] how to get sql result?


2012-03-21

Thread
YAN HONG YE
I want to get the virable char myresult[200]= select name where id>20 from 
mytable
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] decimal function not found


2012-03-20

Thread
YAN HONG YE
my sqlite sql sentens is :
"select '88', Convert(decimal(5,1),AVG(qph))"
but build the project in c shows the error:
Error: no such function: decimal
how to process this problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] how to show sqlite database in vc6 listctrl control?


2012-03-20

Thread
YAN HONG YE
I have a sqlite database, and wish to create a gui application to show the 
database use vc6 listctrl control, I don't know how to write c++ cource, anyone 
could tell me the source in the codeproject web or any other website have the 
resource about this topic?
thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] ABOUT IMPORT TXT FILE TO SQLITE


2012-03-15

Thread
YAN HONG YE
On Date: Wed, 14 Mar 2012 23:13:57 -0400
From: Kevin Benson 
wrote:

Quick solution to above error may be to try path with forward slashes:

C:\sqlite\lib>".separator ',' \n .import /sqlite/lib/ddl.txt dzh \n" |
sqlite3 /sqlite/lib/foods.db

and 8.3 path format where needed:

C:\sqlite\lib>".separator ',' \n .import /progra~1/sqlite/lib/ddl.txt dzh
\n" | /progra~1/sqlite/lib/foods.db

When I use this code, It can't work. and the same error msg. 

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



[sqlite] import a text to sqlite


2012-03-14

Thread
YAN HONG YE
On Tue, 13 Mar 2012 02:28:38 +, YAN HONG YE 
wrote:

>here my code,where is wrong?
>char mma[250];
>strcpy(mma,"sqlite3 foods.db \" .separator ',' \" \".import dzhhkmysql.txt 
>dzh\"");
>system(mma);

dot-commands for the sqlite3 command line tool have to be on separate
lines.

Do the equivalent of:
printf ".separator ','\n.import dzhhkmysql.txt dzh\n" | sqlite3 foods.db

C:\sqlite\lib>".separator ',' \n .import ddl.txt dzh \n" | sqlite3 foods.db
The system cannot find the path specified.
but the two file ddl.txt and foods.db certainty under C:\sqlite\lib\
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  1   2    >  

    

      
    
    
 1 - 100 of 109 matches
    

    
Advanced search


Search the list




    
    

      

                               Site Navigation
      
      

        
The Mail Archive home
        

          sqlite-users - all messages
        

          sqlite-users  - about the list
        
Expand
      
    

    

      

  				Mail list logo
      
      
    
  

    

	        	      Footer information
    
    

      
The Mail Archive home
      
Add your mailing list
      
FAQ
      
Support
      
Privacy