COPY statement from MATLAB giving error

Hari_rao

I'm trying to run COPY statement from MATLAB in my local machine to load data into Vertica database on the server. I keep getting error: 

 

'[Vertica][JDBC](11300) A ResultSet was expected but not generated from query "COPY Schema.tableName (FK_CUSTOMER_ID,FK_RUN_START_DATE_ID,FK_RUN_END_DATE_ID,FK_TRAVEL_ID,FK_ORIGIN_ID,FK_DEST_ID,FK_SEGMENT_ID, SEGMENT_PERCENTAGE,LAST_UPDATED) FROM LOCAL ''D:\MATLAB\Testing_env\test2.dat''  DELIMITER '','' ;". Query not executed. '

 

This is the code I'm executing: -

 

 

COPY Schema.tableName (FK_CUSTOMER_ID,FK_RUN_START_DATE_ID,FK_RUN_END_DATE_ID,FK_TRAVEL_ID,FK_ORIGIN_ID,FK_DEST_ID,FK_SEGMENT_ID, SEGMENT_PERCENTAGE,LAST_UPDATED) FROM LOCAL 'D:\MATLAB\Testing_env\test2.dat'  DELIMITER ',' ;

 I'm using a function exec(conn,sqlstmnt); in MATLAB which executes the sqlstmnt using the connection settings handle in conn. There is no problem with the connection since I have tried other queries with the same connection.

After Contacting the MATLAB support, they told me because the error says that exec(conn,sqlstmnt) is expecting a result set after the execution and in this case there is nothing to return, the error is generated. So they suggested anothere approach as below which does not expect any thing in return.

s = conn.Handle.createStatement;
s.executeUpdate(sqlstmnt);

But when I tried this method, I get another error :-

 

Error using get
The name 'createStatement' is not an accessible property for an instance of class
'com.vertica.jdbc.VerticaConnectionImpl'.

Error in database/subsref (line 16)
B = get(A,S(idx).subs);

Error in Testing_COPY (line 51)
s = conn.Handle.createStatement;

 

Any help is greatly appreciated. 

Gayatri_R

Hi

 

Please see the example snippets provided here : https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ConnectingToHPVertica/ClientJDBC/ExecutingQueriesThroughJDBC.htm

 

Statement stmt = conn.createStatement();

 

Use the executeUpdate method for DML SQL queries that change data in the database, such as INSERT, UPDATE and DELETE which do not return a result set.

stmt.executeUpdate("INSERT INTO address_book " +
                   "VALUES ('Ben-Shachar', 'Tamar', 'tamarrow@example.com'," + 
                   "'555-380-6466')");
stmt.executeUpdate("INSERT INTO address_book (First_Name, Email) " +
                   "VALUES ('Pete','pete@example.com')");

 

Try modifying your code to use createStatement() and stmt.executeUpdate() and see if it works.

 

 

Thanks

Gayatri

Hari_rao

That worked perfectly. Thanks Gayatri. One inclusion for MATLAB, I used the below commands: -

 

connHandle = conn.Handle;
stmt = connHandle.createStatement;
results = stmt.executeUpdate(sqlstmnt);

sqlstmnt has the COPY statement as usual.

 

Thank You once again