I'm using ElevateDB; in the FormCreate of my XData/Aurelius server I call TDatabaseManager.Update... This works great when the table doesn't exist. When I start the server again, "Update" is called and I receive the error "ElevateDB Error #400 The index By Access Code already exists in the table Store. My table name is "Store" and the index name is "By Access Code". I am using the TMS Data Modeler script to create this non-exclusive index (DBIndex). I need the generated SQL to use the ''IF EXISTS" with the "CREATE INDEX" so that the index is only created if it exists. Is there a way to do this?
The "IF EXISTS" clause is not added to the script, unfortunately.
Regarding the error, is that an exception or just a new entry in the Errors property of TDatabaseManager? If it's the former, we are not aware of any issues. Do you have more details or a sample to reproduce the issue?
It is an exception. In my server I am doing the "Update" database thing every times it starts so that any database changes would be done. So I expected that it would see the indexes already exist not give an error; then it would only do any other database changes (if needed). Should I just trap the exception and ignore the error, and will the other database changes be completed? Is this a "bad" way to do it? Or, should any data changes on the server be done as a separate, deliberate action.
As I mentioned, we are not aware of the issue. If you have a way to let us know how to reproduce it consistently, I'd appreciate it.
I see that your index name has spaces, have you checked how the SQL statement generated by Aurelius looks like? Can you try with an index name without spaces? Can you try to wrap your index name around double quotes in your [DBIndex] attribute?
I use spaces in my index names and in the field names. I do this so that when the user sees a field or index name it will be "friendly" to them and not something in all caps or underlines, etc.
When I first tried this, I received errors, so I fiddled with the placement of quote characters until the generated SQL was accepted. I did try removing spaces, no change. I have wondered how Aurelius / XData would handle my "spaced" field names.
I think that every time I start the server the Update runs the SQL to create/modify the tables and the indexes. It doesn't check to see in the indexes already exists before running the SQL statement. That is why I was wondering if the "IF NOT EXISTS" could be placed with the SQL statement that creates the indexes to eliminate any errors. I can provide a test program to reproduce it if wanted.
Please find attached a test program that demonstrates the problem I'm having. I have also included a second problem I have. Please see the unit mainSAMServer for a description. Thanks.
Davids XData Test.zip (18.5 KB)
Thank you very much for the project. It helped finding the issue. We fixed it here and fix will be included in next release. In case you want to fix it there and manually recompile the packages, in unit Aurelius.Sql.AnsiSQLGenerator, method TAnsiSQLGenerator.GenerateCreateDBIndex, line 166, this is how the line should be:
Result := 'CREATE INDEX ' + IdName(DBIndex.Name) + ' ON ';
Then remove the double quotes from the index names in DBIndex attributes:
[DBIndex('By Store ID', 'Store ID')]
[DBIndex('By Store Name', 'Store Name')]
[DBIndex('By Customer Code', 'Customer Code')]
[DBIndex('By Access Code', 'Access code')]
[DBIndex('By Store Date Modified', 'Store Date Modified')]
[DBIndex('By SAM Date Modified', 'SAM Date Modified')]