Java.sql.sqlexception Error Syntax Error At Or Near $1
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company
Postgresql Syntax Error At Or Near $1
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs org.postgresql.util.psqlexception: error: syntax error at or near ")" hibernate Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, caused by: org.postgresql.util.psqlexception: error: syntax error at or near "-" just like you, helping each other. Join them; it only takes a minute: Sign up org.postgresql.util.PSQLException: ERROR: syntax error at or near “$1” up vote 4 down vote favorite I am getting this PSQLException: org.postgresql.util.PSQLException: ERROR: syntax
Pq Syntax Error At Or Near $1
error at or near "$1" Position: 37 When I run the following code: ps = connection.prepareStatement("SELECT current_timestamp + INTERVAL ?;"); ps.setString(1, "30 minutes"); System.out.println(ps); rs = ps.executeQuery(); However, the println function displays this in the console: SELECT current_timestamp + INTERVAL '30 minutes' Anyone know what is wrong? The query in the console runs fine in pgAdmin so I know it isn't a syntax error. java postgresql jdbc share|improve this question edited Oct 10 '15
Org Postgresql Util Psqlexception Error Syntax Error At Or Near User
at 20:33 BalusC 684k20924822698 asked Oct 10 '15 at 20:32 Kevin Orriss 416617 Makes no difference, the semicolon is perfectly valid –Kevin Orriss Oct 10 '15 at 20:34 the query works for me, is this is the actual code or do you have any variable in place of "30 minutes" –silentprogrammer Oct 10 '15 at 20:41 This is the actual code... Are you running postgres? –Kevin Orriss Oct 10 '15 at 20:42 There is a bug with setting strings as an interval, I am sure of it... setString works for other strings and setInt works too... –Kevin Orriss Oct 10 '15 at 20:51 It seems to be postgresql bug –herry Oct 10 '15 at 20:58 | show 3 more comments 2 Answers 2 active oldest votes up vote 5 down vote accepted Although the syntax INTERVAL '30 minutes' is valid when you write SQL directly in a console, it is actually considered to be an interval literal and won't work where the string that follows the word INTERVAL is not a literal string. Prepared statements in PostgreSQL are implemented on the server side using PREPARE and each ? is seen as an actual variable on the server. This is also why it complains about $1 although you never wrote a $
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss sql error: 0, sqlstate: 42601 the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each http://stackoverflow.com/questions/33058684/org-postgresql-util-psqlexception-error-syntax-error-at-or-near-1 other. Join them; it only takes a minute: Sign up org.postgresql.util.PSQLException: ERROR: syntax error at or near “(” up vote 0 down vote favorite I have craeted a Web Application that can be deployed on Heroku by maven eclipse. Group Id: org.glassfish.jersey.archetypes Artifact Id: jersey-heroku-webapp version: 2.17 I tested the appication on the localhost and POSTMAN and it http://stackoverflow.com/questions/30216175/org-postgresql-util-psqlexception-error-syntax-error-at-or-near works fine. I pushed it to heroku to test it on servlet container but I am getting 520 OK 520 it is just a number that I return in the SQLEXCEPTION. IN the Heroku log I found this error: 2015-05-13T13:10:37.364388+00:00 app[web.1]: at java.lang.Thread.run(Thread.j ava:745) 2015-05-13T13:10:37.389547+00:00 app[web.1]: org.postgresql.util.PSQLException: ERROR: syntax error at or near "(" 2015-05-13T13:10:37.389560+00:00 app[web.1]: Position: 45 2015-05-13T13:10:37.389740+00:00 app[web.1]: at org.postgresql.core.v3.QueryE xecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) Database class: public class Database { public Database() { } public void drivercConnection() { try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("jar works :) "); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static Connection getConnection() throws URISyntaxException, SQLException { URI dbUri = new URI(System.getenv("DATABASE_URL")); String username = dbUri.getUserInfo().split(":")[0]; String password = dbUri.getUserInfo().split(":")[1]; String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath(); Connection con = DriverManager.getConnection(dbUrl, username, password); return con; } public int insertData(String mac, int route, double latD, double longD) { int status = 201; drivercConnection(); try { Connection con = null; try { con = getConnection(); } catch (URISyntaxException e) { // TODO
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta http://dba.stackexchange.com/questions/78353/set-role-via-parameterized-query Discuss the workings and policies of this site About Us Learn https://github.com/pgjdbc/pgjdbc/issues/271 more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database syntax error skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top SET ROLE via parameterized query up vote 1 down vote favorite In the very helpful response I received syntax error at to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I would like to prevent SQL injection attacks by parameterizing the SET ROLE statement. My approach in Groovy (which uses JDBC) was: def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver') sql.execute 'SET ROLE ?', user but this generates a syntax error. The documentation says SET ROLE can take a string literal, but I'm just not clear how I can validly pass it one. Any suggestions? postgresql jdbc parameter sql-injection share|improve this question asked Oct 3 '14 at 22:31 beldaz 785413 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote accepted Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2245) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1974)
Sign in Pricing Blog Support Search GitHub This repository Watch 61 Star 263 Fork 224 pgjdbc/pgjdbc Code Issues 114 Pull requests 20 Projects 0 Pulse Graphs New issue org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" #271 Closed indicalabs opened this Issue Apr 2, 2015 · 1 comment Projects None yet Labels None yet Milestone No milestone Assignees No one assigned 2 participants indicalabs commented Apr 2, 2015 I am able to run the same query in Aginity Redshift SQL editor, but the prepared statement is throwing the below error SQL is : unload ('select search_id,user_id,search_term,date_time_stamp,source_file_name,date_created from test.XX_XXXXX where source_file_name LIKE \'%2014-09-30- XXXXX.csv\' ') to ? credentials 'aws_access_key_id=XXXXXXXXXXXXXXXXXXXXXXXX;aws_secret_access_key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' parallel off delimiter '~' GZIP ALLOWOVERWRITE; ERROR is:
2015-04-02 15:51:30,867 DEBUG [org.springframework.batch.repeat.support.RepeatTemplate] - Position: 180>
2015-04-02 15:51:30,867 DEBUG [org.springframework.batch.repeat.support.RepeatTemplate] - Position: 180>
2015-04-02 15:51:30,867 ERROR [org.springframework.batch.core.step.AbstractStep] -
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [unload ('select search_id,user_id,search_term,date_time_stamp,source_file_name,date_created from test.XX_XXXXX where source_file_name LIKE \'%2014-09-30-XXXXX.csv\' ') to ? credentials 'aws_access_key_id=XXXXXXXXXXXXXXXXXXXXXXXX;aws_secret_access_key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' parallel off delimiter '~' GZIP ALLOWOVERWRITE;]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Position: 180
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExcept