Error: Object already exists in SQLWRITE

Hello,

I am trying to load data into a database table from Matlab table using ODBC by using the command sqlwrite(conn,tablename,data).

conn variable is configured correctly and the output of conn.Message gives [].

tablename is assigned a value followed by schema name. i.e public.TableName

data variable has the value imported from DataBase Explorer(I even tried creating a Matlab table by assigning values as data = table()....)

The table in which data is to be inserted exists in schema in advance.

Upon sqlwrite execution, I am getting the below error:

"Error using database.odbc.connection/sqlwrite (line 102) ODBC JDBC/ODBC Error: ODBC Driver Error: ROLLBACK 4213: Object "Char1_Table" already exists".

line 102 shows "error(message("database:database:WriteTableDriverError","ODBC",string(ME.message)));"

However, if the table in database does not exist then the sqlwrite command works as expected but it does not solve my purpose. Moreover, I could not find the difference in sqlwrite command to differentiate between appending data in table and inserting data in new table. The syntax is same as written in documentation.

Kindly help me in getting through the above specified error. My Matlab version is R2018b(not available to mention in release. only R2018a is available).

Thank You

Answer 1

[Lossie Rooney]

I had this problem with ODBC. Works as expected with JDBC.

[Shahzeb Shafi]

Hello Lossie,

Can you please let me know if you made some changes in ODBC configuration or any other thing to get through the error (if you were getting that previously).

Thanks

Shahzeb

[Lossie Rooney]

I tried different drivers for ODBC. Tried deleting and re-adding the connection. Neither worked. I could only get it to work using JDBC drivers.

Answer 2

[Robin Ruehlicke]

Hi,

I had the same problem. It turned out that schema names are case sensitive in Matlab, although they are case-insensitive in the DB. So Matlab thinks that the table does not exist, although it does exist. When Matlab tries to create it, the DB returns the error.

Take a look at the sqlfind() function, which is called by sqlwrite(). It should not have an empty return value. For Oracle, I had to pass the schema name in capital letters, but that could be different for other DBs.

Once you have found the "correct" spelling of the schema, pass it to sqlwrite separately, e.g.:

sqlwrite(conn, 'table_t', data, 'Schema', 'SCHEMANAME');

Hope that helps.

Robin