DbExpress key specified / No

Question

I am working on a database program using dbExpress components (Delphi 7). Data is extracted from the database through the following components: TSQLDataSet → TDataSetProvider → TClientDataSet → TDatasource → TDBEdit. So far, the form has worked correctly. Query in TSQLDataset

select id, name, byteken, timeflag from scales where id = :p1 

I added a large (2048) varchar field to the database table; when I add this field to the above request (and connect TDBMemo or TDBRichEdit) to TDatasource), I get the following message when I try to change the value in a new text field

 Unable to find record. No key specified. 

I get the same error when there is no TDBMemo on the form (but with the varchar field in the request). As soon as I remove the varchar field from the request, everything works correctly again.

What could be causing this problem?

==== Additional information ====

Now I have defined the constant fields in the form. The field that contains the key to the table has the provider flags set to [pfInUpdate, pfInWhere, pfInKey], while all other fields have their flags like [pfInUpdate, pfInWhere]. This does not solve the problem.

Permanent fields have been defined on clientdataset. When I defined them in TSQLDataSet, the error message "no key specified" does not occur. The program still issues this error message (which I forgot to mention earlier):

 EDatabase error: arithmetic exception, numeric overflow or string truncation 

The large row field has the correct value in 'displaywidth' and 'size'.

==== Even more information ====

I rewrote the form to use non-data components. One query retrieves data from the database (using exactly the same query string as I use in TSQLDataSet); data is then transferred to the controls. After the user clicks the OK button on the form, the data is transferred back to the database through another request that performs an update or insert. Since this works correctly, I do not see that the problem is with data-aware components.

==== Another piece of information ====

I found this stack overflow question that seems to address a similar issue. I changed the request as

 select id, name, name, byteken, timeflag, cast (constext as varchar (2048)) as fconstext from scales where id = :p1 

and set the dbMemo data field as "fconstext". After adding text to dbMemo, the call to "applyupdates" now fails with the following message

 column unknown 'fconstext' 

despite the fact that there is a constant field created with this name.

I do not know if this helps or just stirs up the water.

==== Additional information, April 23 ====

I deleted the field from the database table and then added it back. A program written in writing works fine as long as the line entered in the problem data field is less than 260 characters. I added ten characters at a time several times without problems until the line length was 256. Then I added a few more characters (without counting), tried to save - and got an error. From now on, trying to add another character , an error message appears (which comes in the "applyupdates" clientdataset method).

Initially, the field contained 832 characters, so there is no hard limit on the number of characters that I can successfully save. But as soon as an error message appears, it always appears as if the database remembered that there was an error.

==== Additional information, April 24 ====

Once again, I deleted the field from the database and added it back; the character set is WIN1251, for reasons that I don’t understand (I don’t need Cyrillic characters). The maximum number of characters that I can enter using data controls seems to be around 280, regardless of how this field is defined.

Since then, I switched to using non-data controls in a real program where this problem occurs, and I can assure you that this restriction does not exist there. Thus, I am sure that the problem is not due to the mismatch of character size, as suggested. Do not forget that I am using Delphi 7, which does not have unicode strings. I think that there is an error in one of the components, but since I am using older versions, I think that the problem has been resolved, but not in the versions that I am using.

==== Hope final editing, 25/04/12 ====

Following the advice of mosquitoes, I created a new database whose default character set is WIN1252 (UTF-8 was not selected as a choice, and, in any case, my programs are not unicode). In this clean database, I defined one table where the character set of the string "constext" was also defined as WIN1252. I launched a data-oriented version of the problematic form, and was able to enter text without any problems (currently over 1700 characters).

Thus, it would seem that the problem was created using one character set defined for the database and one for the field. I do not know how to check in retrospect which default character set for the database has been defined, so I cannot confirm this.

Now I have a little problem with defining a new database (there are 50+ tables) and copying data from the original database. Since this database serves the product of the flagship client, I am somewhat wary of this ....

Answer 1

Unable to find entry. Key not specified.

set select id, name, byteken, timeflag from scales, where id =: p1

to

select id, name, byteken, timeflag from the scales, where id = 245

existing identifier during design.

---

for casts cast (constext as varchar (2048)) ..... If the definition of the column is changed, the existing CAST addresses of this type of column may become invalid

Arithmetic exception, numeric overflow or line truncation

• String truncation This occurs when the concatenated string does not match the underlying data type CHAR or VARCHAR. If the result falls into the column of the table, perhaps this is a valid error. Or maybe you really need to increase the size of the column. Similarly for intermediate values ​​stored in a stored procedure or trigger variables .

• Transliteration Error This occurs when you have data in a database stored in one character set, but transliteration to the required character set fails. There are various times when a transliteration of a character set occurs. There is an automatic: Each piece of data that you retrieve from the database (via SELECT or otherwise) is transliterated from the column table of the database character set to the character set of the connection . If the character sets are too different, then there will be two translations: first from the charset column to Unicode, and then from Unicode to the encoding of the connection. In addition, you can request transliteration manually by performing CASTing a column in a different encoding, for example: CAST (column_name AS varchar (100) character set WIN1251). The reason that transliteration may fail is because some characters simply do not exist in certain character sets. For example, WIN1252 does not contain Cyrillic characters, so if you use WIN1252 connection encoding and try to select SELECT from a column with Cyrillic characters, you may get this error. Nowadays, it is best to use Unicode or UTF8 in your applications and the UTF8 connection symbol. And make sure you use at least Firebird 2.0 , has UTF8 support.

• Incorrect parameter order when using DotNetFirebird The order in which parameters are added to FbCommand when using DotNetFirebird can cause a -303 exception with the prompt “Arithmetic exception, numeric overflow or line truncation”. The order of the parameters must match the order of the parameters in the stored procedure - otherwise an 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 (); If the order of the parameters inside the "TBLTEXT_ADDTEXT" procedure is different from the order in which you add the parameters to the FbCommand-Object, you will get the error -303.

4.

No'am Newman said . But as soon as an error message appears, it always appears as if the database remembers that there is an error.

does not remember; the database is corrupted !!!

---

Until you can change your database character set and always experiment with deleting and adding fields to a damaged table, it is difficult to solve the problem. 1. A new database should be created for each new test (TIP: create one and copy them x times). 2. A field set with plain text, but not with Cyrillic characters stored in the source field; you cannot see them, but they are. 3. set varchar (8191) and the PAGE_SIZE database to 8192. The actual maximum length of a VARCHAR 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, .... 

Sort Options

No default sorting. Therefore, you must define a sort for each field that should be used for sorting (ORDER BY) or comparison (UPPER):

You can also specify a mapping to the ORDER BY clause:

 ORDER BY LASTNAME COLLATE FR_CA, FIRSTNAME COLLATE FR_CA 

or with a WHERE clause:

 WHERE LASTNAME COLLATE FR_CA = :lastnametosearch 

Unicode

Firebird 2.0 and higher. Now there is a new UTF8 character set that correctly processes Unicode strings in UTF-8 format. The Unicode sorting algorithm was implemented, so now you can use UPPER () and the new LOWER () function without having to specify sorting.

Answer 2

Check the provider UpdateMode property. If it is set to upWhereChanged or upWhereKeyOnly , you need a key in the database table to work properly.