Delphi DBExpress/Firebird 参数化查询中的字符串截断错误
我在 Delphi 中有一个使用 DBExpress TSQLQuery 的查询，看起来像这样
ActiveSQL.sql.add('SELECT * FROM MYTABLE where MYFIELD=(:AMYFIELD) ');
ActiveSQL.ParamByName('AMYFIELD').AsString := 'Some random string that is to long for the field';
ActiveSQL.Open;
如果我运行它，当它执行 open 命令时，我会得到以下异常
在带有消息的 TDBXError 类中 '算术异常，数值 溢出或字符串截断'。
这是由于 AMYFIELD 中的字符串比表字段长度长造成的，MYFIELD 是 Varchar(10)，如果我将其修剪为较短的字符串，则可以正常工作，如果我将字符串直接添加到 SQL 中，则可以正常
ActiveSQL.sql.add('SELECT * FROM MYTABLE where MYFIELD="Some random string that is to long for the field" ');
工作好的， ie 不会抱怨截断，现在如果这是一个插入/更新，我想知道有关截断的信息，但由于它刚刚用于搜索，我想停止它。
有什么方法可以告诉 DBExpress 可以截断我的字符串吗？ 或者是否有可行的解决办法，
我想避免添加类似
l_input := copy(l_input,0,fieldLength-1); 的
内容 因为看起来很混乱并且会使维护代码变得更加困难。
我正在通过 interbase 驱动程序使用 Delphi 2007 和 Firebird 2，这有帮助吗？
更新：
@Erick Sasse 它看起来像你的权利，我在 firebird FAQ 网站 http://www.firebirdfaq.org/faq79/
@inzKulozik LeftStr 工作正常，尽管我无法让 ActiveSQL.ParamByName('AMYFIELD').Size 工作，但这对我来说仍然很混乱，而且更难维护。
我还看到了一种将 substr 添加到 SQL 的方法：类似
select * from mytable where myname = substr(:MYNAME,0,10)
Again 的东西看起来更难维护，理想情况下我想要一个 Firebird/DBExpress 配置设置来解决这个问题，但在我找到一个之前，我会使用 inzKulozik 的解决方案和希望表结构不要改变太多。
I have a query in Delphi using DBExpress TSQLQuery that looks like so
ActiveSQL.sql.add('SELECT * FROM MYTABLE where MYFIELD=(:AMYFIELD) ');
ActiveSQL.ParamByName('AMYFIELD').AsString := 'Some random string that is to long for the field';
ActiveSQL.Open;
If I run it, when it executes the open command I get the following exception
in class TDBXError with message
'arithmetic exception, numeric
overflow or string truncation'.
This is caused by the string in AMYFIELD been longer then the tables field length, MYFIELD is Varchar(10), If I trim it down to a shorter string it works OK, and if I add the string directly into the SQL like so
ActiveSQL.sql.add('SELECT * FROM MYTABLE where MYFIELD="Some random string that is to long for the field" ');
it works OK, i.e. does not complain about the truncation, now if this was an insert/update I would want to know about the truncation , but as its just been used for a search I would like to stop it.
Is there any way I can tell DBExpress that it is OK to truncate my strings? or is there a workable work around for this
I would like to avoid having to add something like
l_input := copy(l_input,0,fieldLength-1);
as looks messy and would make maintaining the code harder.
I am using Delphi 2007 with Firebird 2 via the interbase driver if that helps?
UPDATE:
@Erick Sasse it looks like your right, I found the error message on the firebird FAQ site http://www.firebirdfaq.org/faq79/
@inzKulozik the LeftStr works fine, although I cannot get ActiveSQL.ParamByName('AMYFIELD').Size to work , but this still seams messy to me, and harder to maintain.
I have also seen a method that adds substr to the SQL: something like
select * from mytable where myname = substr(:MYNAME,0,10)
Again looks harder to maintain, Ideally I would like a Firebird/ DBExpress config setting that fixes this problem, but until I can find one I'll go with inzKulozik's solution and hope the table structure does not change to much.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您无法从位置 0 复制子字符串！
试试这个：
或者
或者
You can't copy substring from position 0!
Try this:
or
or
此错误消息来自 Firebird，而不是 DBX。 我认为 DBX 将参数发送到数据库的方式与您输入的方式完全相同，因此应该由 Firebird 来截断它。
This error message is from Firebird, not DBX. I think DBX sends parameters to the database exactly the way you enter it, so it should be Firebird job to truncate it.