Results 1 to 6 of 6
  1. #1
    filior is offline Member
    Join Date
    Aug 2014
    Posts
    8
    Rep Power
    0

    Default Implement dynamic string into sql statement

    Hello!

    I have some spatial datasets which are availabe in a opensource metadata catalog software GeoNetwork and in a PostGIS database.
    The problem is that the student who created the database used different IDs for the same datasets. So the IDs in the GeoNetwork differs from the IDs in the postGIS database. They only have the same name

    So, if someone uploads a new dataset into GeoNetwork, the same dataset is uploaded into a postGis database too.

    Now I want to add a ID column into the database to give the field the same ID like the ID in geonetwork. I think I can only do this with using the filename cause they are same in both cases.

    Now I want to alter the table in eclipse with following code:

    PHP Code:
    Connection con = null;
                    Statement stmt = null;
                    String query = null;
                    
                    String filen = filename.substring(0, filename.indexOf('.'));
                    con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433" + "user=postgres;password=dominik1;" + "database=geonetwork");  --> (the connection data will later be in a properties file)
                    query = "ALTER TABLE " + filen + " ADD GN_id INTEGER(10) ";
                    stmt = con.createStatement();
                    stmt.execute(query);
    But i get the following findbug error:
    Failed to execute goal org.codehaus.mojo:findbugs-maven-plugin:2.5.2:check (default) on project services: failed with 1 bug
    org.fao.geonet.services.resources.UploadAndProcess .exec(Element, ServiceContext) passes a nonconstant String to an execute method on an SQL statement ["org.fao.geonet.services.resources.UploadAndProces s"] At UploadAndProcess.java:[lines 83-299]

    I tried to suppress this error and worked with PreparedStatement and String.Format but I can't get rid of this error.
    Does anyone have an idea how to solve this problem?

    Thank you very much in advance

    filior

  2. #2
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,050
    Rep Power
    6

    Default Re: Implement dynamic string into sql statement

    You neglect to post your PreparedStatement attempt which is in fact the correct way to please that marvelous error check.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  3. #3
    filior is offline Member
    Join Date
    Aug 2014
    Posts
    8
    Rep Power
    0

    Default Re: Implement dynamic string into sql statement

    Quote Originally Posted by gimbal2 View Post
    You neglect to post your PreparedStatement attempt which is in fact the correct way to please that marvelous error check.
    You are right. I used the preparedStatement wrongly before, now it works. I would like to post it as final solution but I still have an PSQLException: org.postgresql.util.PSQLException: Error: Syntax error at $1
    Position: 13

    Here is the code:

    PHP Code:
    Connection con = null;
    PreparedStatement ps = null;
    final String sqlps = "ALTER TABLE ? ADD COLUMN ?";
    String filen = filename.substring(0, filename.indexOf('.'));
    con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/testdb?user=postgres&password=xxxx");
    ps = con.prepareStatement(sqlps);
    ps.setString(1, filen);
    ps.setString(2, "GN_ID");
    
    ps.execute();
    For me it seems like PSQL has a problem with the ? within the PreparedStatement?

  4. #4
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,050
    Rep Power
    6

    Default Re: Implement dynamic string into sql statement

    Oh man, I'm blind. You're doing a DDL command, I completely missed that. What I said is absolutely not true: PreparedStatement is NOT the solution for this problem. You can only use PreparedStatement for non-DDL commands (insert, update, delete)

    That makes the error check harder to please. Let me explain why it exists: your code is a potential risk of SQL injection and that is why this check (which is not a standard error, it is some Maven plugin you are using) is rejecting the code. I have no clue how you would make your code generate a dynamic SQL command and not trigger this error I'm afraid. Perhaps you can exclude the error check in the plugin for this specific class somehow.
    Last edited by gimbal2; 08-26-2014 at 04:37 PM.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    filior is offline Member
    Join Date
    Aug 2014
    Posts
    8
    Rep Power
    0

    Default Re: Implement dynamic string into sql statement

    Quote Originally Posted by gimbal2 View Post
    Oh man, I'm blind. You're doing a DDL command, I completely missed that. What I said is absolutely not true: PreparedStatement is NOT the solution for this problem. You can only use PreparedStatement for non-DDL commands (insert, update, delete)

    That makes the error check harder to please. Let me explain why it exists: your code is a potential risk of SQL injection and that is why this check (which is not a standard error, it is some Maven plugin you are using) is rejecting the code. I have no clue how you would make your code generate a dynamic SQL command and not trigger this error I'm afraid. Perhaps you can exclude the error check in the plugin for this specific class somehow.
    Thank you for your answer!
    With the preparedStatement I could get rid of the findbug error. Or is the PSQLException part of this problem?

  6. #6
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    837
    Rep Power
    6

    Default Re: Implement dynamic string into sql statement

    PSSQLException is the connectivity error and not related to syntax.whether ty his "jdbc:postgresql_postGIS://localhost:5433/testdb?user=postgres&password=xxxx" is correct ?

    Have u printed ur connection object?
    Ramya:cool:

Similar Threads

  1. Replies: 2
    Last Post: 04-28-2013, 01:50 AM
  2. Replies: 5
    Last Post: 03-28-2012, 09:35 PM
  3. Replies: 11
    Last Post: 08-15-2011, 10:12 AM
  4. dynamic insert query statement
    By NandiniGarg in forum JDBC
    Replies: 4
    Last Post: 12-09-2010, 10:11 AM
  5. Dynamic element in a statement
    By pjeremypj in forum New To Java
    Replies: 2
    Last Post: 12-09-2009, 10:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •