Delphi site acquisition data update database don't understand-CodePudding

Ask a question you a question, there are the following database structure
Id list time link updatatime
1 football 22:00 http://www. a.com/11/2015/07/13
2 basketball http://www. became the biggest n/12/2015/07/13
3 volleyball 00:00 2015/07/13 http://www.nba.com/14
I collected from one web page program (list) and time (time), followed by a collection of links, the link is likely to change, how to write MSSQL statement, whether the above data is consistent, if is consistent, do not update, if inconsistent, then update the link to the corresponding ID, and update the updatatime (acquisition), anyway, I collected regularly, not data storage, warehousing, data is judge whether there is a change, how do you write this code? With Delphi

CodePudding user response:

 IF NOT the EXISTS (
SELECT 1 FROM [...]. WHERE [the list]='... 'AND [updatetime]='... '
) 
INSERT INTO [...]. (... ) VALUES (... ) 
The ELSE 
The UPDATE [...]. The SET [link]='... ', [updatetime]='... 'WHERE [the list]='... 'AND [updatetime]='... '

The above SQL calls the loop in Delphi

CodePudding user response:

reference 1/f, gobiz response:

 IF NOT the EXISTS (
SELECT 1 FROM [...]. WHERE [the list]='... 'AND [updatetime]='... '
) 
INSERT INTO [...]. (... ) VALUES (... ) 
The ELSE 
The UPDATE [...]. The SET [link]='... ', [updatetime]='... 'WHERE [the list]='... 'AND [updatetime]='... '

The above SQL calls the loop in Delphi

I've never tried so long SQL statements, will only simple examples, such as:
Adoquery1. Close
Adoquery1. SQL. The clear;
Adoquery1. SQL. Add (' SELECT 1 FROM [...]. WHERE [the list]='... 'AND [updatetime]='... ");
Adoquery1. Open;
Like you to provide the SQL, how do I change to ah, ha ha, really won't

CodePudding user response:

Don't directly in the SQL. The add (); Add your judgment code above is ok?

CodePudding user response:

 procedure TForm1. Button4Click (Sender: TObject); 
Var sList, sTime, sLink, s1, s2, SQL: string; 
The begin 
SList:=Edit1. Text; 
STime:=Edit2. Text; 
SLink:=Edit3. Text; 
With DBGrid1. The DataSource. The DataSet do 
The begin 
If (sList<> FieldByName (' List '.) AsString) are identical or 
(sTime<> FieldByName (' Time '). AsString) are identical or 
(sLink<> FieldByName (' Link '.) AsString) are identical then 
The begin 
IDKey:=FieldByName (' IDKey) asString) are identical. 
DateTimetoString (S2, '- dd yyyy - MM, now); 
S1:=FieldByName (' id '.) AsString; 
SQL:='Update table name set'; 
SQl: SQl + 'List==+ # 39 + sList + # 39 +', '; 
SQl: SQl +='Time=' + # 39 + sTime + # 39 + ', '; 
SQl: SQl + 'Link==+ # 39 + sLink + # 39 +', '; 
SQl: SQl + 'undatatime==+ # 39 + S2 + # 39 +', '; 
SQl: SQl +='where IDKey=' + # 39 + IDKey + # 39; 
ADOQuery1. Active:=False; 
ADOQuery1. SQL. Text:=SQL; 
ADOQuery1. ExecSQL; 
end; 
end; 
end;

IDKey is the primary key,

1, first Edit1 Edit2, Edit3 is input new data

2, through the DBGrid display data, the mouse to click the selected rows,

3, click the button, update the data,

CodePudding user response:

 SQL:='Update table name set'; 
SQl: SQl + 'List==+ # 39 + sList + # 39 +', '; 
SQl: SQl +='Time=' + # 39 + sTime + # 39 + ', '; 
SQl: SQl + 'Link==+ # 39 + sLink + # 39 +', '; 
SQl: SQl + 'undatatime==+ # 39 + S2 + # 39; 
SQl: SQl +='where IDKey=' + # 39 + IDKey + # 39;

A code above is wrong, correct,

CodePudding user response:

reference gangrenxie reply: 3/f

Quote: refer to 1st floor gobiz response:

IF NOT the EXISTS (
SELECT 1 FROM [...]. WHERE [the list]='... 'AND [updatetime]='... '
)
INSERT INTO [...]. (... ) VALUES (... )
The ELSE
The UPDATE [...]. The SET [link]='... ', [updatetime]='... 'WHERE [the list]='... 'AND [updatetime]='... '

The above SQL calls the loop in Delphi

I've never tried so long SQL statements, will only simple examples, such as:
Adoquery1. Close
Adoquery1. SQL. The clear;
Adoquery1. SQL. Add (' SELECT 1 FROM [...]. WHERE [the list]='... 'AND [updatetime]='... ");
Adoquery1. Open;
Like you to provide the SQL, how do I change to ah, ha ha, really not?

 var 
SzSql: String; 
QryADO: TADOQuery; 
The begin 
QryADO:=TADOQuery. Create (nil); 
Try 
QryADO. Connection:=... ; 
//loop handle each data 
For I:=0 to x do 
The begin 
SzSql:='IF NOT the EXISTS (' 
+ 'SELECT 1 FROM [...]. WHERE [the list]=' '... "' AND [updatetime]=' '... "' 
+ ') '
+ 'INSERT INTO [...]. (... ) VALUES (... ) '
The ELSE '+' 
+ 'UPDATE [...]. The SET [link]=' '... "'," updatetime=' '... 'WHERE [the list]=' '... "' AND [updatetime]=' '... "'; 
QryADO. SQL. Text:=szSql; 
QryADO. The Execute (); 
end; 
The finally 
FreeAndNil (qryADO); 
end; 
end;

CodePudding user response:

For this type of SQL statements, can consider to use stored procedures to handle in the data,

CodePudding user response:

refer to the eighth floor yct0605 response:

for this type of SQL statements, can consider to use stored procedures to handle in the data,

In one instance can? Never used?

CodePudding user response:

refer to 7th floor gobiz response:

Quote: reference gangrenxie reply: 3/f

Quote: refer to 1st floor gobiz response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull