dbExpress /未指定键 [英] dbExpress/No key specified
问题描述
我正在使用dbExpress组件（Delphi 7）开发数据库程序。通过以下组件从数据库检索数据：TSQLDataSet-> TDataSetProvider-> TClientDataSet-> TDatasource-> TDBEdit。到目前为止，该表格已正常运行。 TSQLDataset中的查询是
I am working on a database program, using the dbExpress components (Delphi 7). The data is retrieved from the database via the following components: TSQLDataSet -> TDataSetProvider -> TClientDataSet -> TDatasource -> TDBEdit. Until now, the form has worked correctly. The query in the TSQLDataset is
select id, name, byteken, timeflag from scales where id = :p1
我在数据库表中添加了一个大的（2048）varchar字段；当我将此字段添加到上面的查询中（并将TDBMemo或TDBRichEdit连接到TDatasource）时，当我尝试在新文本字段中编辑值时收到以下消息
I added a large (2048) varchar field to the database table; when I add this field to the above query (and connect either a TDBMemo or a TDBRichEdit) to the TDatasource), I receive the following message when I try to edit the value in the new text field
Unable to find record. No key specified.
当表单上没有TDBMemo时，我会收到相同的错误（但在varchar字段中查询）。从查询中删除varchar字段后，一切都会再次正常运行。
I get the same error when there is no TDBMemo on the form (but with the varchar field in the query). As soon as I remove the varchar field from the query, everything works properly again.
什么原因可能导致此问题？
What could be the cause of this problem?
====更多信息====
==== More information ====
我现在已经在表单中定义了持久字段。保留表键的字段的提供者标志设置为[pfInUpdate，pfInWhere，pfInKey]，而所有其他字段的标志都标记为[pfInUpdate，pfInWhere]。
I have now defined persistent fields in the form. The field which holds the key to the table has its provider flags set to [pfInUpdate,pfInWhere,pfInKey], whereas all the other fields have their flags as [pfInUpdate,pfInWhere]. This doesn't solve the problem.
持久性字段是在clientdataset上定义的。当我在TSQLDataSet上定义它们时，不会出现关于未指定键的错误消息。程序仍然发出此错误消息（我之前忽略了此错误消息）：
The persistent fields were defined on the clientdataset. When I defined them on the TSQLDataSet, the error message about 'no key specified' does not occur. The program still puts out this error message (which I neglected to mention earlier):
EDatabase error: arithmetic exception, numeric overflow or string truncation
大字符串字段在'displaywidth'和'size'中具有正确的值。
The large string field has the correct value in 'displaywidth' and 'size'.
====甚至更多的信息====
==== Even more information ====
我重新编写了表单以使用非数据感知组件。一个查询从数据库中检索数据（使用与我在TSQLDataSet中使用的查询字符串完全相同的查询字符串）；然后将数据传输到控件。用户按下表单上的确定按钮后，数据将通过另一个执行更新或插入的查询传递回数据库。由于这可以正常工作，所以我看不到数据感知组件的问题所在。
I rewrote the form to use non-data aware components. One query retrieves the data from the database (using exactly the same query string as I am using in the TSQLDataSet); the data is then transferred to the controls. After the user presses the OK button on the form, the data is passed back to the database via another query which performs an update or an insert. As this works correctly, I don't see what the problem is with the data aware components.
====另一段信息====
==== Yet another snippet of information ====
我发现了这个问题关于堆栈溢出，似乎解决了类似的问题。我将查询更改为
I found this question on Stack Overflow which seems to address a similar issue. I changed the query to be
select id, name, name, byteken, timeflag,
cast (constext as varchar (2048)) as fconstext
from scales
where id = :p1
并将dbMemo的数据字段设置为'fconstext'。在将文本添加到dbMemo后， applyupdates调用现在失败，并显示以下消息
and set the dbMemo's datafield to be 'fconstext'. After adding text to the dbMemo, the 'applyupdates' call now fails with the following message
column unknown 'fconstext'
尽管存在使用该名称创建的持久字段。
despite the fact that there is a persistent field created with that name.
我不知道这是对水还是有所帮助。
I don't know whether this helps or simply muddies the water.
====更多信息，4月23日====
==== More information, 23 April ====
我从数据库表中删除了该字段，然后又将其添加回去。只要输入到有问题的数据字段中的字符串少于260个字符，编写的程序就可以正常工作。我一次添加了十个字符，没有问题，直到字符串长度为256。然后我添加了更多字符（不计），尝试保存-并得到错误。从现在开始，尝试甚至再添加一个字符都会导致错误消息（该消息位于clientdataset的 applyupdates方法中）。
I dropped the field from the database table, then added it back. The program as written works fine as long as the string being entered into the problematic data field is less than about 260 chars. I added ten characters at a time several times without problem until the string length was 256. Then I added some more characters (without counting), tried to save - and got the error. From this point on, trying to add even one more character causes the error message (which comes at the 'applyupdates' method of the clientdataset).
该字段最初包含832个字符，因此可以成功存储的字符数没有硬性限制。但是一旦出现错误消息，它就总是出现，好像数据库记得有错误一样。
Originally, the field contained 832 characters, so there is not a hard limit to the number of characters which I can successfully store. But once the error message appears, it always appears, as if the database remembers that there is an error.
====详细信息，4月24日，====
==== More information, 24 April ====
再次，我从数据库中删除了该字段，然后又将其添加回了；字符集为WIN1251，原因目前尚不清楚（我不需要西里尔字母）。不管字段本身是如何定义的，使用数据感知控件最多可以输入280个字符。
Once again, I dropped the field from the database then added it back; the character set is WIN1251, for reasons which are not clear to me now (I don't need Cyrillic characters). The maximum number of characters which I can enter using data-aware controls seems to be about 280, regardless of how the field itself is defined.
从那以后，我开始在发生此问题的真实程序中使用无数据意识的控件，并且我可以向您保证，该限制在那里不存在。因此，我可以肯定地确定问题不是由字符大小不匹配所引起的（如建议的那样）。别忘了我正在使用没有Unicode字符串的Delphi 7。我认为其中一个组件存在错误，但是当我使用的是旧版本时，我想问题已经解决了，但是我使用的版本中没有。
I have since moved to using non-data aware controls in the real program where this problem occurs, and I can assure you that this limit does not exists there. Thus I am fairly sure that the problem is not due to a mismatch in character size, as has been suggested. Don't forget that I am using Delphi 7, which does not have unicode strings. I think that there is a bug in one of the components, but as I'm using old versions, I imagine that the problem has been solved, but not in the versions which I use.
====希望最终编辑，2012年4月25日====
==== Hopefully final edit, 25/04/12 ====
根据蚊子的建议，我创建了一个新数据库，其默认字符集是WIN1252（UTF-8没有出现，而且我的程序不是unicode）。在这个干净的数据库中，我定义了一个表，其中上下文字符串的字符集也定义为WIN1252。我运行了有问题的表格的数据感知版本，并且能够毫无问题地输入文本（目前超过1700个字符）。
Following mosquito's advice, I created a new database whose default character set is WIN1252 (UTF-8 did not appear as a choice and and anyway my programs are not unicode). In this clean database I defined the one table, where the 'constext' string's character set was also defined as WIN1252. I ran the data-aware version of the problematic form and was able to enter text without problem (currently over 1700 characters).
因此，问题似乎是通过为数据库定义一个字符集和为该字段定义一个字符集而造成的。我不知道如何回溯一下数据库默认字符集的定义，因此我无法确认这一点。
It would seem, thus, that the problem was created by having one character set defined for the database and one for the field. I don't know how to check in retrospect what the default character set of the database was defined as, so I can't confirm this.
我现在有了定义新数据库（有50多个表）并从原始数据库复制数据的问题。由于此数据库服务于客户的旗舰产品，因此我对此有些警惕。
I now have the small problem of defining a new database (there are 50+ tables) and copying the data from the original database. As this database serves the customer's flagship product, I am somewhat wary of doing this....
推荐答案
无法查找记录。未指定密钥。
从标度为id =：p1
set select id, name, byteken, timeflag from scales where id = :p1
至
从标度为id = 245的小数位数中选择id，名称，byteken，时间戳记
select id, name, byteken, timeflag from scales where id = 245
an
要强制转换
强制转换（上下文为varchar（ 2048））...
如果更改了列的定义，则该列类型的现有CAST可能无效
to casts cast (constext as varchar (2048))..... If a column's definition is altered, existing CASTs to that column's type may become invalid
算术异常，数字溢出或字符串截断
-
字符串截断
串联的字符串不适合基础CHAR或VARCHAR数据类型的大小。如果结果进入表格列，则可能是有效的错误。或者，也许您确实需要增加列大小。存储过程或触发器变量中存储的中间值也是如此。
String truncation It happens when the concatenated string doesn't fit the underlying CHAR or VARCHAR datatype size. If the result goes into a table column, perhaps it's a valid error. Or maybe you really need to increase the column size. Similar goes for intermediary values stored in stored procedure or trigger variables.
字符音译失败
当数据库中的数据存储在一个字符集中，但是音译到所需字符集失败时，就会发生这种情况。字符集音译发生在很多地方。有一个自动的：
您从数据库（通过SELECT或其他方式）检索的每条数据都将从数据库字符集表的列转译为连接字符集 >。如果字符集相差太大，将有两种转换：首先是从列字符集到Unicode，然后是从Unicode到连接字符集。
另外，您可以通过将列CAST转换为另一个字符集来手动请求音译，例如：
CAST（column_name AS varchar（100）字符集WIN1251）。
音译失败的原因是因为某些字符集中根本不存在某些字符。例如，WIN1252不包含任何西里尔字符，因此，如果您使用连接字符集WIN1252并尝试从具有西里尔字符的列中进行选择，则可能会出现此类错误。
在现代，最好在应用程序中使用Unicode或UTF8以及UTF8连接字符。并确保至少使用 Firebird 2.0 ，并具有UTF8支持。
Character transliteration failed This happens when you have data in database stored in one character set, but the transliteration to required character set fails. There are various points where character set transliteration occurs. There is an automatic one: Every piece of data you retrieve from database (via SELECT or otherwise) is transliterated from character set of database table's column to connection character set. If character sets are too different, there will be two translations: first from column charset to Unicode and then from Unicode to the connection charset. Also, you can request transliteration manually by CASTing the column to another charset, example: CAST(column_name AS varchar(100) character set WIN1251). The reason that transliteration can fail is that simply some characters don't exist in certain character sets. For example, WIN1252 doesn't contain any Cyrillic characters, so if you use connection charset WIN1252 and try to SELECT from a column with Cyrillic characters, you may get such error. In modern times, it is best to use Unicode or UTF8 in your applications and UTF8 connection character. And make sure you use at least Firebird 2.0, has UTF8 support.
使用DotNetFirebird时参数顺序错误
使用DotNetFirebird时将参数添加到FbCommand的顺序可能会导致-303异常，并带有提示算术异常，数字溢出或字符串截断。参数的顺序必须符合存储过程中参数的顺序-否则将引发异常。示例（.NET，C＃，DotNetFirebird（使用FirebirdSql.Data.FirebirdClient;））
Wrong order of parameters when using DotNetFirebird The order in which Parameters are added to a FbCommand when using DotNetFirebird might cause the -303 exception with the hint "Arithmetic exception, numeric overflow, or string truncation". The order of the parameters has to fit the order of the params in the stored procedure - otherwise the exception will be thrown. Example (.NET, C#, DotNetFirebird (using FirebirdSql.Data.FirebirdClient;))
FbCommand CMD = new FbCommand（ TBLTEXT_ADDTEXT，cnn）;
CMD.Parameters.Add（ TEXT1，FbDbType.VarChar，600）.Value = strText1;
CMD.Parameters.Add（ TEXT2，FbDbType.VarChar，600）.Value = strText2;
CMD.CommandType = CommandType.StoredProcedure;
CMD.ExecuteNonQuery（）;
如果过程 TBLTEXT_ADDTEXT中的参数顺序与向FbCommand-Object添加参数的顺序不同，则会收到-303错误。
FbCommand CMD = new FbCommand("TBLTEXT_ADDTEXT", cnn); CMD.Parameters.Add("TEXT1", FbDbType.VarChar, 600).Value = strText1; CMD.Parameters.Add("TEXT2", FbDbType.VarChar, 600).Value = strText2; CMD.CommandType = CommandType.StoredProcedure; CMD.ExecuteNonQuery(); If the order of the parameters inside the procedure "TBLTEXT_ADDTEXT" differ from the order in which you´re adding parameters to the FbCommand-Object, you´ll receive the -303 error.
4。
否我是纽曼说的 ，但是一旦出现错误消息，它总是会出现，就像
数据库记住存在错误一样。
No'am Newman said But once the error message appears, it always appears, as if the database remembers that there is an error.
不记得了；数据库已损坏！！！
no remembers; the database is damaged !!!
只要您不能更改数据库字符集，并始终尝试在字段中添加和删除字段，桌子损坏了，很难解决问题。 1.对于每个新测试，必须创建一个新数据库（提示：创建一个数据库并将其复制x次）。 2.设置为纯文本而非西里尔字母的字段存储在原始字段中；您看不到它们，但它们在那里。 3.将varchar（8191）和数据库PAGE_SIZE设置为8192。使用UTF8的实际最大VARCHAR长度为8191
As long as you are not able to change your database character-set and always experiment with dropping and adding fields to a damaged table, it's hard to solve the problem. 1. For every new test there must be an new database created (TIP: create one and copy them x times). 2. The field set with plain text not with Cyrillic characters stored in originally field; you can not see them but they are there. 3. set varchar(8191) and database PAGE_SIZE to 8192. The actual maximum VARCHAR length with UTF8 is 8191
CREATE DATABASE语句：
CREATE DATABASE localhost:mybase
USER SYSDBA
PASSWORD masterkey
PAGE_SIZE 8192
DEFAULT CHARACTER SET UTF8;
SET NAMES ISO8859_1;
CREATE TABLE scales (
ID ...,
byteken VARCHAR(8191) COLLATE DE_DE,
....
整理
没有默认整理。您应该为要用于排序（ORDER BY）或比较（UPPER）的每个字段定义排序规则：
There is no default collation. So you should define a collation for every field that is to be used for sorting (ORDER BY) or comparing (UPPER):
您还可以使用ORDER BY指定排序规则子句：
You can also specify the collation with the ORDER BY clause:
ORDER BY LASTNAME COLLATE FR_CA, FIRSTNAME COLLATE FR_CA
或带有WHERE子句：
or with the WHERE clause:
WHERE LASTNAME COLLATE FR_CA = :lastnametosearch
Unicode
Firebird 2.0及更高版本，现在有了新的UTF8字符集，可以正确处理UTF-8格式的Unicode字符串。Unicode归类算法已实现，因此现在您可以使用UPPER（）和新的LOWER（）函数无需指定排序规则。
Firebird 2.0. and above. Now there is the new UTF8 character set that correctly handles Unicode strings in UTF-8 format. The Unicode collation algorithm has been implemented so now you can use UPPER() and the new LOWER() function without the need to specify a collation.
这篇关于dbExpress /未指定键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
