Missing a matching closing delimiter in statement

Madiba87

Hiiii,

When i try to insert data on db Vertica i encounter this error «  Missing a matching closing delimiter in statement , ‘ INSERT INTO mydb ( ´Dav’id’, ´Smith’, Joe, ladouille’),

Please could you help me, i dont know how resolve my problem with « Dav’id »

Thanks for your help.

LenoyJ

With two adjacent single quotes?

dbadmin=> create table data ( name varchar );
CREATE TABLE

dbadmin=> insert into data values ('Dav''id');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from data;
  name
--------
 Dav'id
(1 row)

Jim_Knicely

Another option:

dbadmin=> create table data ( name varchar );
CREATE TABLE

dbadmin=> insert into data SELECT $$Dav'id$$;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM data;
  name
--------
 Dav'id
(1 row)

See:
https://forum.vertica.com/discussion/240097/simplify-string-literals-with-dollar-quoted-string-literals

Madiba87

I cant change the format of my values, because in my db, data are write like this « Dav’id. i want to know how can insert my data with this constraint

Jim_Knicely

What do you mean you can't change the format? Of the actual data? The Dollar ($$) quoted method is not changing the data format...

Another example:

dbadmin=> truncate table data;
TRUNCATE TABLE

dbadmin=> \d data
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | data  | name   | varchar(80) |   80 |         | f        | f           |
(1 row)

dbadmin=> INSERT INTO data VALUES ($$'Dav'id', 'Smith', Joe, ladouille'$$);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM data;
                name
------------------------------------
 'Dav'id', 'Smith', Joe, ladouille'
(1 row)

Madiba87

q1 <- paste("COPY DB FROM local '",fichier,"' WITH DELIMITER AS ';' SKIP 1 ABORT ON ERROR",sep="")
res <- dbSendUpdate(vertica1, q1)

Error in .local(conn, statement, ...) :
execute JDBC update query failed in dbSendUpdate ([Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid integer format '"Dav'id","XXXXXXX",0,"XXXX","XXXXXXXX","xxxxxx",NA,0,NA,NA,NA' for column 1 (nom_etablissement)))

This is my error

Jim_Knicely

Can you post the structure of the DB table (column names and data types)?

Is the nom_etablissement an integer?

This simple example works fine using the COPY command:

dbadmin=> \! cat /home/dbadmin/yourdata.txt
"Dav'id","XXXXXXX",0,"XXXX","XXXXXXXX","xxxxxx",NA,0,NA,NA,NA

dbadmin=> \d db
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | db    | col1   | varchar(10) |   10 |         | f        | f           |
 public | db    | col2   | varchar(10) |   10 |         | f        | f           |
 public | db    | col3   | int         |    8 |         | f        | f           |
 public | db    | col4   | varchar(10) |   10 |         | f        | f           |
 public | db    | col5   | varchar(10) |   10 |         | f        | f           |
 public | db    | col6   | varchar(10) |   10 |         | f        | f           |
 public | db    | col7   | varchar(10) |   10 |         | f        | f           |
 public | db    | col8   | int         |    8 |         | f        | f           |
 public | db    | col9   | varchar(10) |   10 |         | f        | f           |
 public | db    | col10  | varchar(10) |   10 |         | f        | f           |
 public | db    | col11  | varchar(10) |   10 |         | f        | f           |
(11 rows)

dbadmin=> COPY db FROM '/home/dbadmin/yourdata.txt' DELIMITER ',' ENCLOSED BY '"';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT * FROM db;
  col1  |  col2   | col3 | col4 |   col5   |  col6  | col7 | col8 | col9 | col10 | col11
--------+---------+------+------+----------+--------+------+------+------+-------+-------
 Dav'id | XXXXXXX |    0 | XXXX | XXXXXXXX | xxxxxx | NA   |    0 | NA   | NA    | NA
(1 row)