Problems with primary key when creating table using repository.Execute

[Meel]

I am writing an add-on for EA in C# and need to create an extra table, but I seem to have hit a wall concerning primary keys.

The following code works just fine:

Code: [Select]

repository.Execute(CREATE TABLE MyTable (ID INTEGER, message TEXT) ;);
But once I add PRIMARY KEY after the first argument to get

Code: [Select]

repository.Execute(CREATE TABLE MyTable (ID INTEGER PRIMARY KEY, message TEXT) ;);
Or add an extra argument as follows :

Code: [Select]

repository.Execute(CREATE TABLE MyTable (ID INTEGER, message TEXT, PRIMARY KEY(ID)) ;);
I get a syntax error.

Anyone who sees what I am doing wrong? Or know why I can not set ID as primary key?
Any help will be much appreciated,  thanks in advance

[Geert Bellekens]

A) How can the code you mentioned work if you don't use double quotes around your SQL string?
B) Don't add tables, or change anything to the standard EA database schema. That is a notoriously bad idea and will come back to bite you later on.

Geert

[Meel]

A) sorry, there are quotationmarks in the original just forgot them when typing the question.

B) hmm, even when the tables are disjoint from the rest of the EA structure?

[qwerty]

A) I think that Execute just permits INSERT, UPDATE and DELETE. You probably want to do that with a native RDBMS tool.
B) the problem will be the export. EA ex-/imports only its own tables in XMI format. You would need to merge your table separately.

q.

[Meel]

A) as I said above CREATE TABLE without primary key works perfectly fine. Can insert and select data as with any other database. The problem arises once I append PRIMARY KEY to ID INTEGER, the I get an syntax error

[Geert Bellekens]

Could also be exactly what it says, an SQL syntax error.

In those cases you can usually find the exact query executed in %appdata%Sparx Systems\EA\DBError.txt

Make sure that your DDL is correct and working before trying to execute it through EA.

Geert

[Arshad]

Hi Meel

Try setting a constarint PK like below

Code: [Select]

Repository.Execute("CREATE TABLE TestTable (ID INTEGER NOT NULL, message TEXT,CONSTRAINT PK_ID PRIMARY KEY (ID));");
But i do agree and suggest  what Geert said on adding tables in EA.

Don't add tables, or change anything to the standard EA database schema. That is a notoriously bad idea and will come back to bite you later on.

HTH
Arshad

[Meel]

Thank you Arshad that seemed to do the trick!
 
But tanking your and Geerts warnings into consideration I'll try to think of some other way of solving the main problem

M