While I agree with you that the more complex statement would have not
accounted for NULL values, but as I said, there are six records with the
values given; the more direct-to-the-point SQL:



SELECT AL1.MEDIATYPE_ID FROM SYSDBA.MEDIATYPE AL1



Simply returns six rows with the values I already had listed, zero through
five.



As to the INVALID TOKEN error you think I should have received, I don't,
except in the DDL case as I mentioned. I mentioned it parenthetically, only
as "interesting", but since you made significant points about it:



If it should not have been used, then SQLTables() should not be returning it
as part of the table catalog. Otherwise, a "generic" ODBC application like
I'm using has no clue what it should do. It would assume (correctly) that
(in this case) SYSDBA is the schema/owner of the tables, leaving the
possibility that the same table name could be used by other schemas/owners.
Further, at least for running SELECT statements, if I don't at least log on
as SYSDBA, I get "access denied" errors trying to run the SQL. So, for DDL
purposes, since SYSDBA is reported as the owner via SQLTables(), that's why
I logged on that way. Note that in the ODBC setup, the option "remove
SCHEMA from SQL query" is there by default, so maybe this is why it runs
without issue. However, it does have the option to "use full SCHEMA", which
suggests this syntax is in fact allowed. (The online Help file accompanying
the driver doesn't seem to explain this option.)



As to firebird.msgs, it is wrong for any ODBC driver to assume the program
making the ODBC function calls has files important to the driver's correct
operation. The driver itself should have a copy of firebird.msgs it can use
and return to the calling program the properly formatted information. This
is, in my opinion, a bug.



But back to my real problem. Taking your advice, I copied firebird.msgs to
my executable's directory, and now the error reads:



DIAG [HY000] [ODBC Firebird Driver][Firebird]unsuccessful metadata update

STORE RDB$INDICES failed

attempt to store duplicate value (visible to active transactions) in unique
index "RDB$INDEX_5" (-607)



The only thing that comes to my mind was that perhaps this connection was
made "read only", though that is not how the default settings of the driver
say it should be made. In any case, I toggled the setting without any
change in the error message.



So, I could still use some help with this. Note that I have little
information on the database itself; it is used internally by a program I
acquired to maintain a media library. I love the program, and although it
provides a robust reporting engine, I'm trying to utilize reporting features
it doesn't have that will make my life better. I've pretty much figured out
all I need to know about the database to do the reporting, but I require the
primary key to utilize one particular reporting functionality.



I'll apologize in advance if I offended anyone with my tone. I try to keep
things "matter of fact", but what may read that way to me may not come
across that way to others. I do truly appreciate the information, Helen and
others.



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Friday, October 31, 2008 4:55 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Error while trying to add a primary key



At 04:28 AM 1/11/2008, you wrote:

>I have a table which has 6 records in it, each of which has a unique value
>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

This 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.

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:
>
>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)

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.

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, which
>worked successfully in the SELECT, I get an invalid token message when it
>encounters the period.

See above. That SELECT was not valid SQL either. But DML and DDL are two
different beasts.

>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





[Non-text portions of this message have been removed]