jdbc PSQLException: ERROR: syntax error at or near “CONFLICT”
0
I'm trying to update a table in my postgres DB with JDBC, and for most of my queries it works just fine. But sometime I get this error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "CONFLICT"
When I do the request manually in postgres it works just fine:
INSERT INTO user_jira (key_user,account_id,name_user,email_adress,display_name,active)
VALUES ('admin','5a283eThisIsJustAnExample','admin','john.john@proceedit.com','john john',true)
ON CONFLICT (key_user)
DO UPDATE
SET account_id=excluded.account_id,
name_user=excluded.name_user,
email_adress=excluded.email_adress,
display_name=excluded.display_name,
active=excluded.active;
Does anyone konws whats going on?
Edit: here is how I connect to the db and send my query:
String url = "jdbc:postgresql://host:port/db";//connect(url);
Properties props = new Properties();
props.setProperty("user","user");
props.setProperty("password","*********");
try {
dyDATAconn = DriverManager.getConnection(url, props);
System.out.println("connected to "+url);
Statement st;
//sql statement
st = dyDATAconn.createStatement();
int userRowInserted = st.executeUpdate(User.sqlUpdate(newUser));
int taskRowInserted = st.executeUpdate(Task.sqlUpdate(allTask));
int timeSpentRowInserted = st.executeUpdate(TimeSpent.sqlUpdate(allTimeSpent));
System.out.println("tsk: "+taskRowInserted+"nTS:"+timeSpentRowInserted);
} catch (SQLException e) {
System.out.println("connection to dyJIRA@dyDATA failed");
System.out.println(User.sqlUpdate(newUser));
e.printStackTrace();
return -1;
}
I can't show how I create my query, but I only got pb with users and the requests are all the same format as the one above
java sql postgresql jdbc
asked Nov 22 at 14:24
Nathan van Hille
83
add a comment |
0
I'm trying to update a table in my postgres DB with JDBC, and for most of my queries it works just fine. But sometime I get this error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "CONFLICT"
When I do the request manually in postgres it works just fine:
INSERT INTO user_jira (key_user,account_id,name_user,email_adress,display_name,active)
VALUES ('admin','5a283eThisIsJustAnExample','admin','john.john@proceedit.com','john john',true)
ON CONFLICT (key_user)
DO UPDATE
SET account_id=excluded.account_id,
name_user=excluded.name_user,
email_adress=excluded.email_adress,
display_name=excluded.display_name,
active=excluded.active;
Does anyone konws whats going on?
Edit: here is how I connect to the db and send my query:
String url = "jdbc:postgresql://host:port/db";//connect(url);
Properties props = new Properties();
props.setProperty("user","user");
props.setProperty("password","*********");
try {
dyDATAconn = DriverManager.getConnection(url, props);
System.out.println("connected to "+url);
Statement st;
//sql statement
st = dyDATAconn.createStatement();
int userRowInserted = st.executeUpdate(User.sqlUpdate(newUser));
int taskRowInserted = st.executeUpdate(Task.sqlUpdate(allTask));
int timeSpentRowInserted = st.executeUpdate(TimeSpent.sqlUpdate(allTimeSpent));
System.out.println("tsk: "+taskRowInserted+"nTS:"+timeSpentRowInserted);
} catch (SQLException e) {
System.out.println("connection to dyJIRA@dyDATA failed");
System.out.println(User.sqlUpdate(newUser));
e.printStackTrace();
return -1;
}
I can't show how I create my query, but I only got pb with users and the requests are all the same format as the one above
java sql postgresql jdbc
asked Nov 22 at 14:24
Nathan van Hille
83
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together withreWriteBatchedInserts=true?
– a_horse_with_no_name
Nov 22 at 14:29
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
And how does that code generate the query? BTW: Your use ofStatementandexecuteUpdate(String)suggests your code is vulnerable to SQL injection.
– Mark Rotteveel
Nov 22 at 15:04
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18
add a comment |
0
0
0
I'm trying to update a table in my postgres DB with JDBC, and for most of my queries it works just fine. But sometime I get this error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "CONFLICT"
When I do the request manually in postgres it works just fine:
INSERT INTO user_jira (key_user,account_id,name_user,email_adress,display_name,active)
VALUES ('admin','5a283eThisIsJustAnExample','admin','john.john@proceedit.com','john john',true)
ON CONFLICT (key_user)
DO UPDATE
SET account_id=excluded.account_id,
name_user=excluded.name_user,
email_adress=excluded.email_adress,
display_name=excluded.display_name,
active=excluded.active;
Does anyone konws whats going on?
Edit: here is how I connect to the db and send my query:
String url = "jdbc:postgresql://host:port/db";//connect(url);
Properties props = new Properties();
props.setProperty("user","user");
props.setProperty("password","*********");
try {
dyDATAconn = DriverManager.getConnection(url, props);
System.out.println("connected to "+url);
Statement st;
//sql statement
st = dyDATAconn.createStatement();
int userRowInserted = st.executeUpdate(User.sqlUpdate(newUser));
int taskRowInserted = st.executeUpdate(Task.sqlUpdate(allTask));
int timeSpentRowInserted = st.executeUpdate(TimeSpent.sqlUpdate(allTimeSpent));
System.out.println("tsk: "+taskRowInserted+"nTS:"+timeSpentRowInserted);
} catch (SQLException e) {
System.out.println("connection to dyJIRA@dyDATA failed");
System.out.println(User.sqlUpdate(newUser));
e.printStackTrace();
return -1;
}
I can't show how I create my query, but I only got pb with users and the requests are all the same format as the one above
java sql postgresql jdbc
asked Nov 22 at 14:24
Nathan van Hille
83
I'm trying to update a table in my postgres DB with JDBC, and for most of my queries it works just fine. But sometime I get this error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "CONFLICT"
When I do the request manually in postgres it works just fine:
INSERT INTO user_jira (key_user,account_id,name_user,email_adress,display_name,active)
VALUES ('admin','5a283eThisIsJustAnExample','admin','john.john@proceedit.com','john john',true)
ON CONFLICT (key_user)
DO UPDATE
SET account_id=excluded.account_id,
name_user=excluded.name_user,
email_adress=excluded.email_adress,
display_name=excluded.display_name,
active=excluded.active;
Does anyone konws whats going on?
Edit: here is how I connect to the db and send my query:
String url = "jdbc:postgresql://host:port/db";//connect(url);
Properties props = new Properties();
props.setProperty("user","user");
props.setProperty("password","*********");
try {
dyDATAconn = DriverManager.getConnection(url, props);
System.out.println("connected to "+url);
Statement st;
//sql statement
st = dyDATAconn.createStatement();
int userRowInserted = st.executeUpdate(User.sqlUpdate(newUser));
int taskRowInserted = st.executeUpdate(Task.sqlUpdate(allTask));
int timeSpentRowInserted = st.executeUpdate(TimeSpent.sqlUpdate(allTimeSpent));
System.out.println("tsk: "+taskRowInserted+"nTS:"+timeSpentRowInserted);
} catch (SQLException e) {
System.out.println("connection to dyJIRA@dyDATA failed");
System.out.println(User.sqlUpdate(newUser));
e.printStackTrace();
return -1;
}
I can't show how I create my query, but I only got pb with users and the requests are all the same format as the one above
java sql postgresql jdbc
java sql postgresql jdbc
asked Nov 22 at 14:24
Nathan van Hille
83
asked Nov 22 at 14:24
Nathan van Hille
83
edited Nov 22 at 14:58
asked Nov 22 at 14:24
Nathan van Hille
83
asked Nov 22 at 14:24
Nathan van Hille
83
asked Nov 22 at 14:24
Nathan van Hille
83
83
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together withreWriteBatchedInserts=true?
– a_horse_with_no_name
Nov 22 at 14:29
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
And how does that code generate the query? BTW: Your use ofStatementandexecuteUpdate(String)suggests your code is vulnerable to SQL injection.
– Mark Rotteveel
Nov 22 at 15:04
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18
add a comment |
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together withreWriteBatchedInserts=true?
– a_horse_with_no_name
Nov 22 at 14:29
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
And how does that code generate the query? BTW: Your use ofStatementandexecuteUpdate(String)suggests your code is vulnerable to SQL injection.
– Mark Rotteveel
Nov 22 at 15:04
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together with
reWriteBatchedInserts=true?– a_horse_with_no_name
Nov 22 at 14:29
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together with
reWriteBatchedInserts=true?– a_horse_with_no_name
Nov 22 at 14:29
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
And how does that code generate the query? BTW: Your use of
Statement and executeUpdate(String) suggests your code is vulnerable to SQL injection.– Mark Rotteveel
Nov 22 at 15:04
And how does that code generate the query? BTW: Your use of
Statement and executeUpdate(String) suggests your code is vulnerable to SQL injection.– Mark Rotteveel
Nov 22 at 15:04
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433035%2fjdbc-psqlexception-error-syntax-error-at-or-near-conflict%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433035%2fjdbc-psqlexception-error-syntax-error-at-or-near-conflict%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the exact version of the Postgres JDBC driver you are using? Are you maybe using batching with your JDBC code together with
reWriteBatchedInserts=true?– a_horse_with_no_name
Nov 22 at 14:29
Please show the actual code used
– Mark Rotteveel
Nov 22 at 14:40
I'm using postgres JDBC 4.2 (I think), the driver is version 42.2.5
– Nathan van Hille
Nov 22 at 15:01
And how does that code generate the query? BTW: Your use of
StatementandexecuteUpdate(String)suggests your code is vulnerable to SQL injection.– Mark Rotteveel
Nov 22 at 15:04
The query is made by reading an JSON file containing all the data, injecting this data in a list of User objects (with fields that match the DB table), and making a sql query string out of all the fields with User.sqlUpdate(List<User> allUser).
– Nathan van Hille
Nov 22 at 15:18