0

I have some spatial datasets which are available in a open source 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 differ from the IDs in the PostGIS database. They only have the same name.

If someone uploads a new dataset into GeoNetwork, the same dataset is uploaded into a PostGis database, too. 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 the same in both cases.

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

  Connection con = null;
            PreparedStatement ps = null;
            final String sqlps = "ALTER TABLE ? ADD COLUMN ?";

            String filen = filename.substring(0, filename.indexOf('.'));
            try {
                con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/geonetwork?user=postgres&password=dominik1");
                try {
                    ps = con.prepareStatement(sqlps);
                    ps.setString(1, filen);
                    ps.setString(2, "GN_ID");

                    ps.execute();

But I get an error:

Exception:org.postgresql.util.PSQLException: ERROR: syntax error at »$1«
  Position: 13

It seems that Postgres has problems with the ? in the prepared statement. Does anyone know a solution for this problem?

I also tried using a normal statement, but this is giving me a findbug error that I try to pass a non-constant string to an execute method on an sql statement. So I really don't know how to get rid of this problem.

1

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers.

The good news: you do not need parameters for a ALTER TABLE statement at all.

final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer";

...
    ps = con.prepareStatement(sqlps);
    ps.execute();
  • Your column needs a data type. I added integer on suspicion. Possibly add a NOT NULL constraint after that.

  • Friendly advice: do not use quoted upper-case column names in Postgres if you can avoid it. Use legal, lower case, unquoted names. Makes your life easier.

0

You can't use ? parameters for identifiers. This is common across database brands.

Use format() , like this:

text sql = format('ALTER TABLE %I ADD COLUMN %I', table_name, column_name);

%I means identifier. It will use identifier rules when substituting values for %I.

For added security you can whitelist characters and identifier names and throw an exception if they don't pass the whitelist.

PostgreSQL will silently truncate identifier names longer than 64 characters, so be mindful of that.

  • 1
    To use format(), you would have to call the Postgres server, which would mean two round trips to the server, which would be inefficient. Either build the statement in the client (Java) or let Postgres execute it (DO statement or PL/pgSQL function with EXECUTE). – Erwin Brandstetter Aug 27 '14 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.