在sql中,这样的查询可以执行,但是在delphi中用query,把这些语句写在在query的sql属性中,为什么总是出现 “missing right quote”
SELECT * FROM OpenQuery(INSQL, "SELECT 'AVERAGE ReactTemp'=Avg(ReactTemp)
FROM AnalogWideHistory
WHERE DateTime >= '2003-02-22 08:00:00.000'
AND DateTime <= '2003-02-22 08:50:00.000' AND wwEdgeDetection = 'NONE'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 10000")哎,在delphi中这些嵌入式sql语句该如何写呢?高手帮忙啊
SELECT * FROM OpenQuery(INSQL, "SELECT 'AVERAGE ReactTemp'=Avg(ReactTemp)
FROM AnalogWideHistory
WHERE DateTime >= '2003-02-22 08:00:00.000'
AND DateTime <= '2003-02-22 08:50:00.000' AND wwEdgeDetection = 'NONE'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 10000")哎,在delphi中这些嵌入式sql语句该如何写呢?高手帮忙啊
如
WHERE DateTime >= '2003-02-22 08:00:00.000'
要写成
WHERE DateTime >= QuotedStr('2003-02-22 08:00:00.000')
where qq.ID=aa.ID
s: string;
i: integer;
sql:= format('select * from XXX where NO=''%s'' and PC=''%d''',[s,i]);
adoquery1.add(sql);这种格式清晰,你试试
这个问题你多试几次就出来了。还有提供给你一个建议,你用单步跟踪,把SQL语句COPY下来,然后在分析器里调试,从而很快的就可以判断是那里出错了。
where aa.bianh=ypxx.yaopbh
这是个SQL嵌套查询
返回结果如下
BIANH DANJ YAOPMC
-------- ---------- --------------------
00000287 .42 辅酶A注射液
00001209 16.65 葛根素注射液
00000431 9.7 口宝含漱液
00001257 4.1688 盐酸文拉法辛胶囊
类似where aa='asd' and 1=1的字符串,如果变量strSome:='asd',
用变量构造需要'where aa='''+strSome+''' and 1=1'。
+附近的一个是字符串边界,赋值之后字符串变成了where aa='asd',
用strSql:='where aa='''+strSome+''' and 1=1'之后,showMessage(strSql)
显示的应该是:where aa='asd' and 1=1
FROM AnalogWideHistory
WHERE DateTime >=#39 2003-02-22 08:00:00.000 #39
AND DateTime <=#39 2003-02-22 08:50:00.000 #39 AND wwEdgeDetection =#39NONE#39
AND wwRetrievalMode =#39Cyclic#39
AND wwResolution = 10000')
提示:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Incorrect syntax near '2003'.]
SELECT * FROM OpenQuery(INSQL,'SELECT ''ReactTemp''=Avg(ReactTemp)
FROM AnalogWideHistory
WHERE DateTime > ''2003-02-22 08:00:00.000''
AND DateTime <=''2003-02-22 08:50:00.000''
AND wwEdgeDetection =''NONE''
AND wwRetrievalMode =''Cyclic''
AND wwResolution = 10000')procedure TForm1.Button3Click(Sender: TObject);
begin
memo1.Text:=query1.Text;
query1.Open;
end
关键是,我要做的还不知这些,时间段是可选的,我要动态的改变qquery1.Text
所以改成:
procedure TForm1.Button1Click(Sender: TObject);
var
sql1:string;
begin
sql1:='and convert(varchar(30),datetime,120) Between'
+QuotedStr(FormatDateTime('yyyy-MM-dd HH:mm:ss', datetimepicker1.DateTime))
+'and'
+QuotedStr(FormatDateTime('yyyy-MM-dd HH:mm:ss', datetimepicker3.DateTime));
+'')'
with query1 do
begin
close;
sql.Clear;
sql.add('SELECT * FROM OpenQuery(INSQL,SELECT ''ReactTemp''=Avg(ReactTemp)');
sql.add('FROM AnalogWideHistory');
sql.add('AND wwEdgeDetection =''NONE'' AND wwRetrievalMode =''Cyclic'' AND wwResolution = 10000');
sql.Add(sql1);
memo1.Text:=sql1;
prepare;
execsql; end;
end;但是这样打散了写出来,一堆错误。还是delphi中sql语法问题啊,我头都大了