Complex SQL I never handwritten, all in Excel to use existing forms directly to the source program, and I will introduce this method in detail. Below this insertion process Does the insertion process? To know that the contents of each line ' ' are directly pasted from the ready-made Excel, the workload is small. PU_INSERT ('fHD', [// write delivery list to the database 'FID INTEGER factory code' FACTID 'FHDCODE VARCHAR 20 document number' cxbuttonedit1.text 'ORDERNO VARCHAR 20 must fill the order number' cxtextedit3.text 'fHDDATE datetime required delivery date ' pu_today' Remark Varchar 200 Remarks' cxtextedit6.text 'car Varchar 10 teams codenamed' cxtextedit1.text 'receiverman Varchar 10 consignee' cxtextedit5.text 'DeliverTo Varchar 80 place of delivery' Cxtextedit2.text]);
=========== PU_INSERT process DELPHI source code ============================= procedure PU_INSERT (TableName: String; SARR: array of string) Var RETS, S, S1, S2: String; I, J, K, M, L: INTEGER; C: CHAR; Beginrets: = '('; l: = high (SARR); for i: = 0 to L Do Begin S: = SARR [I]; K: = 0; S1: = ''; M: = Length (s); for j: = 0 to m do begin if s [j] = # 9 Inc (K ) Else Begin if k = 1 THEN S1: = S1 S [J]; END; END; if i = l Then RETS: = RETS S1 ') VALUES (' else rets: = RETS S1 "; END = 0 to l do begin s: = 0 to l do begin s: = 0 to L do begin s: = 0 to L do begin s: = 0 to l do bas: k: = 0; s1: = '; s2: ='; m: = Length (s) For J: = 0 to M DO Begin IF S [J] = # 9 INC (K) ELSE BEGIN IF K = 2 THEN S1: = S1 S [J]; IF K = 11 THEN S2: = S2 S [J]; End; End; C: = Upcase (S1 [1]); if i = L Then Begin IF (c = 'd') AND (S2 = ') THEN RETS: = RETS ' NULL) ' ELSE // Date is empty if (c = 'f') or (c = 'i') THEN RETS: = RETS S2 ')' ELSE // Numeric RETS: = RETS # 39 S2 # 39 ' ) '; // # 39 is the MSSQL string separator END ELSE BEGIN if (c = 'd') AND (S2 = ') THEN RETS: = RETS ' NULL, 'ELSE IF (C =' f ') OR (c = 'I') Then Ret: = RETS S2 ','
Else Rets: = RETS # 39 S2 # 39 ','; end; end; if debug the tell ('INSERT INTO' TABLENAME '' RETS); PU_EXEC ('INSERT INTO' TABLENAME ' RETS) END; I also compiled another process PU_UPDATE, but only one conditional parameters, it will not be introduced.
Because this method is interpreted during operation, it is slower, before it is officially released, I will use another tool to translate the source code into real SQL. The core source code of this tool software is extracted as follows: function doinsert2 (ss: string) : string; var l: tstringlist; i, j: integer; s: string; st: string; hav: boolean; ch: String; label next1, next2, next3; recomlne // try l: = tstringlist.create; s: = '; for i: = 1 to length (s) do // branch, first line dedicated Begin IF (SS [i] <> # 13) and (ss [i] <> # 10) THEN S: = S SS [I]; IF SS [I] = # 13 dam L.Add (s); s: = '' end; end; for i: = 1 to L.count-1 do // Clear first All character Begin if L [i] [1] = '/' THEN GOTO NEXT3; HAV: = FALSE; S: = '; for J: = 1 to Length (L [i]) Do Begin IF L [i] [j] = # 39 TEN HAV: = true; if HAV THEN S: = S L [i] [j]; end; l [i]: = s; Next3: End; st: = '/// INSERT' # 13 # 10 'Pu_exec (' # 39'insert INTO ' MyFind (SS, 12, # 39) ' ('; for i: = 1 to L.count-1 Do Begin IF l [i] [1] = '/' TEN GOTO next1; if (i <> l.count-1) and (i mod 8) = 0) Then St: = ST # 39 ' ' # 13 # 10 # 39; if i <> l.count-1 Then St: = ST MyTab (L [i], 1) ',' else st: = st myTab (L [i], 1) ') VALUES ('# 39; next1: end; for i: = 1 to l.count -1 Do Begin if L [i] [1] = '/' TEN GOTO next2; st: = st # 13 # 10; if MyTab (L [I], 2) [1] in ['f', 'i ',' f ',' I '] THEN CH: =' 'Else Ch: =' # 39 ; if i <> l.count-1 Then St: = ST ' ' CH MYTAB (L [ I], 12) ' ' CH # 39 ',' # 39 else st: = ST ' ' CH MYTAB (L [i], 12) ' ' CH # 39 ')' # 39 '
) '; Next2: end; result: = st; finalLle; end; end; function doupdate (s: string): String; var l: tstringlist; i, j: integer; s: string; st: string; hav : boolean; ch: String; label next1, next2, next3; begin // try l: = tstringlist.create; s: = '; for i: = 1 to length (ss) do // branch, first line dedicated BeGin IF (SS [I] <> # 13) THEN S: = S SS [I]; if SS [i] = # 13 begin L.Add (s) ; s: = '' end; end; for i: = 1 to L.count-1 do // Clear all the characters before the first 'Begin if L [i] [1] =' / 'THEN GOTO NEXT3 HAV: = FALSE; S: = '; for j: = 1 to length (l [i]) Do Begin if L [i] [j] = # 39 THEN HAV: = true; if HAV THEN S: = S L [i] [j]; end; l [i]: = S; Next3: end; st: = '/// Update' # 13 # 10 'pu_exec (' # 39'update ' myfind SS, 12, # 39) 'SET' # 39; for i: = 1 to L.count-1 Do Begin if L [i] [1] = '/' TEN GOTO NEXT1; St: = ST # 13 # 10 ' ' # 39; if Tab (L [I], 2) [1] in ['f', 'I', 'F', 'I'] THEN CH: = 'ELSE ch: =' # 39 ; st: = st myTab (L [i], 1) '=' # 39; if i <> l.count-1 Then St: = ST ' ' CH MYTAB (L [i ], 12) ' ' CH # 39 ',' # 39 else st: = ST ' ' CH MYTAB (L [I], 12) ' ' CH; Next1: End; i: = POS (',', L [0]); st: = st # 39 'Where '# 39' ' myfind (l [0], i 1,', ') ') '; Result: = st; finallyl.free; end; end; // end of doupdate
Function doinsert: String; Var St, S, SOD, SNEW: STRING; I, I1, I2, I3, I4, L: INTEGER; HAV: Boolean; Begin // St: = SS; / / Start Qkinsertrepeati1: = POS ('PU_INSERT (' # 39, st); if i1 <= 0 THEN BREAK; SOD: = '; for i: = i1 to length (st) do beg: = sod st [i] ; If (ST [i] = ')') AND (ST [i-1] = ']') AND (ST [i 1] = ';') or (ST [i-2] = # 10 ) or (ST [I-2] = # 13))) The Break; end; snew: = doinsert2 (SOD); st: = stringReplace (ST, SOD, SNEW, [RFREPLACEAAll]); Until 1> 2; // Start qkupdaterepeati1: = POS ('pu_update (' # 39, st); if I1 <= 0 THEN BREAK; SOD: = '; for i: = i1 to length (st) do beg: = sod st [i ]; If (ST [i] = ')') AND (ST [i-1] = ']') AND (ST [i 1] = ';') or (ST [i-2] = # 10) OR (ST [I-2] = # 13)) The Break; End; Snew: = Doupdate (SOD); St: = StringReplace (ST, STRINGREPLACE (ST, SOD, SNEW, [RFREPLACEALL]); Until 1> 2; Result : = ST; END;
Procedure tForm1.Button11Click (Sender: Tobject); label lb1; var: tsearchrec; I1, FileAtTRS, I: Integer; T, F: File; A: Array [1..1000000] of char; s1, fff: String; St: String; STIN: STRING; begin if open1.execute = false kil1; s1: = Open1.FileName; memo2.text: = '; FileAtTRS: = FAANYFILE; S1: = ExtractFilePath (S1); // showMessage S1); EXIT; if Findfirst (S1 '*. PAS', FileAtTRS, SR) = 0 THEN REAT IF SR.ATTR = FAREADONLY THEN MEMO2.TEXT: = MEMO2.TEXT 'Operation Failure:'; goto lb1 end; if Sr.attr = favolumeid the begin memo2.text: = MEMO2.TEXT 'operation failed:'; goto lb1 end; if sr.attr = fadirectory the begin memo2.text: = MEMO2.TEXT 'operation failed:'; goto lb1 end Assignfile (T, S1 Sr.Name); Reset (T, 1); Blockread (T, A, 1000000, I1); CloseFile (T); if I1> = 1000000 Then Begin Memo2.Text: = MEMO2.TEXT 'The file is too big, the operation failed "; goto lb1 end; if I1> 0 THEN TRY Stin: ='; for i: = 1 to i1 do stin: = stin a [i]; if deb = 10 Then ShowMessage 'in' stin; st: = doinsert Stin); if deb = 10 Then ShowMessage ('Out' ST); AssignFile (F, S1 Sr.Name); Rewrite (f, 1); BlockWrite (f, ST [1], Length (ST)); Closefile (f); Except Memo2.text: = MEMO2.TEXT 'Open failed:' end; lb1: memo2.text: = memo2.text sr.name # 13 # 10; Application.ProcessMessages; Until FindNext (SR) < > 0; END; I am writing a complex query statement. If I have compiled a window that queries the current library, the source program body (the first 16 lines in the following example) is also sticking from the Excel, pay attention to this In the example, not only SQL is generated, but also sets the width of the fields of DBGRID1, and the Chinese name of the field. That is to say, its data is displayed as a source program.
T.S_ADD (1, 's', '', 'a.trno', 'Shipping Single number', 90, '', '', '', ''); T.S_ADD (1, 'S' , '', 'A.orderno', 'Order Number', 90, '', '', '', ''); T.S_ADD (1, 'S', ',' C.Branchcode ',' Branch ', 61,' ',' ',' ',' '); T.S_ADD (1,' S ',', 'Month (a.times)', 'Month', 60, '', '', '', ''); T.S_ADD (1, 's',' ',' a.times', 'shipping date', 75, ',' ',' ',' '); T.S_ADD (1, 's', '', 'Upper (B.ModleSerial)', 'Series', 60, '', '', '', ''); T.S_ADD (1, 'S' , '', 'a.k_modle', 'finished model', 100, '', '', '', ''); T.S_ADD (1, 's', ',' B.Modlesm ',' Find item Description ', 100,' ',' ',' ',' '); T.S_ADD (1,' s ',' ',' (- a.qty) ',' Delivery Quantity ', 75,' ',' ',' ',' '); t.s_add (1,' s', '', 'a.n_ccj', 'Standard Factory ", 85,' ',' ',', '' ); T.S_ADD (1, 's',' ',' (- a.qty * a.n_ccj) ',' export price ', 130,' ',' ',', ''); t .s_add (1, 's', '', 'B.FactoryPrice', 'Current Property Price', 85, '', '', '', '); T.S_ADD (1,' S ',' ',' (- a.qty * b.FactoryPrice) ',' current price ', 130,' ',' ',' ',' '); t.s_add (1,' s', ',' A.Realccj ',' Order Factory ', 85,' ',' ',' ',' '); T.S_ADD (1,' S ',' ',' (- A.Qty * a.Realccj) ',' Order price is'
, 130, '', '', '', '); T.S_ADD (1,' s', '', 'D.Remark', 'Note', 150, '', '', '', ''); t.s_add (1, 'f', '', 'chg_stkcrd a, modle b, orders c, fhd d', '', 0, '', '', ','); t .s_add (1, 'w', '', '', ', 0,' ',' ',', 'a.k_modle = B.MODLE and A.K_FID =' _ Factid 'and A.TRNTYPE = '# 39' shipping '# 39 ' and a.RDERNO = C.orderno and a.trnno = D.fhdcode '); T.S_ADD (1,' W ',' cxbuttonedit2 ',' a.k_modle ' , '', 0, '=', # 39, # 39, ''); T.S_ADD (1, 'W', 'cxbuttonedit1', 'B.Modlesm', ', 0,' Like ', # 39 '%', '%' # 39, ''); T.S_ADD (1, 'W', 'cxbuttonedit7', 'B.ModleSerial', '', 0, '=', # 39, # 39, ''); T.S_ADD (1, 'W', 'cxtextedit5', '(- a.qty)', '', 0, '> =', '', ',' '); t.s_add (1, 'w', 'cxtextedit4', '(- a.qty)', '', 0, '<=', '', '', ''); T.S_ADD (1, 'W', 'cxdateedit1', 'a.times',' ', 0,'> = ', # 39, # 39,' '); t.s_add (1,' w ',' cxdateedit2 ',' a.times ", '', 0, '<=', # 39, c59 # 39, ''); T.S_ADD (1, 'W', 'cxbuttonedit3', 'a.trnno', '', 0, '=', # 39, # 39, ''); t.s_add (1, 'w', 'cxbuttonedit5', 'a.orderno', '', 0, '=', # 39, # 39, ''); T .s_add (1, 'w', 'cxbuttonedit6', '
