Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Meel on July 03, 2018, 01:09:12 am

Title: Problems with primary key when creating table using repository.Execute
Post by: Meel on July 03, 2018, 01:09:12 am

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

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Geert Bellekens on July 03, 2018, 01:34:09 am

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

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Meel on July 03, 2018, 05:06:09 am

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?

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: qwerty on July 03, 2018, 05:58:26 am

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.

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Meel on July 03, 2018, 03:20:54 pm

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

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Geert Bellekens on July 03, 2018, 04:51:14 pm

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

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Arshad on July 03, 2018, 10:27:30 pm

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.

Quote from: Geert Bellekens on July 03, 2018, 01:34:09 am

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

Title: Re: Problems with primary key when creating table using repository.Execute
Post by: Meel on July 04, 2018, 03:55:54 pm

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

M