| Subject | Re: [firebird-support] Error while trying to add a primary key |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-10-31T23:54:42Z |
At 04:28 AM 1/11/2008, you wrote:
Be aware that your SELECT query (if allowed, which it ought not to be) will not return anything pertaining to records where mediatype is NULL. Another possibility is that the DSQL engine allowed your invalid SQL but returned no result because there was no query to run.
If you have existing data in a field, you won't be able to make it a PK if NULLs are present in any records. I suspect that this is what the low-level message would have told you, if the correct firebird.msg were available.
And next - stick to legal SQL for all queries AND for DDL requests. Particularly avoid pseudo-SQL statements that were created by Microsoft tools for the Access database engine.
Always commit after each DDL statement; and *never* combine DDL and DML in a single transaction.
./heLen
>I have a table which has 6 records in it, each of which has a unique valueThis is invalid SQL. This query should return an INVALID TOKEN error. Perhaps you are using a utility tool that screens out schema qualifiers on SELECT statements. Whatever, Firebird does not support them.
>in one column. Very simply, these values are 0, 1, 2, 3, 4, 5. In
>addition, the following SELECT statement returns zero rows:
>
>SELECT AL5.MEDIATYPE_ID, Count (AL5.MEDIATYPE_ID) FROM SYSDBA.MEDIATYPE AL5
>GROUP BY AL5.MEDIATYPE_ID HAVING Count (AL5.MEDIATYPE_ID)>1
Be aware that your SELECT query (if allowed, which it ought not to be) will not return anything pertaining to records where mediatype is NULL. Another possibility is that the DSQL engine allowed your invalid SQL but returned no result because there was no query to run.
>However, when I execute the following SQL statement:The significant part of this message is "message file...\firebird.msg not found". You are getting an exception here, with a high-level message ID (sqlcode -607) but the engine can't find firebird.msg to send you the low-level gdscode (9 digits) that identifies the actual exception.
>
>alter table mediatype add constraint pk_mediatype primary key (mediatype_id)
>
>I get the following error message:
>
>IAG [HY000] [ODBC Firebird Driver][Firebird]unsuccessful metadata update
>can't format message 8:21 -- message file
>C:\Hyperion\products\biplus\bin\firebird.msg not found
>attempt to store duplicate value (visible to active transactions) in unique
>index "RDB$INDEX_5" (-607)
If you have existing data in a field, you won't be able to make it a PK if NULLs are present in any records. I suspect that this is what the low-level message would have told you, if the correct firebird.msg were available.
>(Interestingly, if I specify sysdba.mediatype in the ALTER statement, whichSee above. That SELECT was not valid SQL either. But DML and DDL are two different beasts.
>worked successfully in the SELECT, I get an invalid token message when it
>encounters the period.
>Note that I am logged on as sysdba.)That is not wise for DDL requests unless sysdba is both the database owner and the owner of all of the objects - which is not good practice.
>How can I resolve this? TIA!First, make sure that you don't have a wrong message file lurking in the path that is visible to your client interface - ODBC if it's your own code, or the path visible to whatever tool you are using. Remove or rename such files. Of course, also make sure that the correct firebird.msg is in the firebird root directory on your server.
And next - stick to legal SQL for all queries AND for DDL requests. Particularly avoid pseudo-SQL statements that were created by Microsoft tools for the Access database engine.
Always commit after each DDL statement; and *never* combine DDL and DML in a single transaction.
./heLen