61973

Can we use DDL Commands in a prepared statement (PostgreSQL)?

Question:

DDL commands follow:

CREATE TABLE — creates a table with the column names the user provides.

DROP TABLE — deletes all rows and removes the table definition from the database.

ALTER TABLE — adds or removes a column from a table.

I need few examples if there is a possibility of using these Commands in PostgreSQL and Java?

public boolean create(Employee employee) { try { callableStatement = openConnection().prepareCall("{call insert_employee(?,?,?)}"); callableStatement.setInt(1, employee.getEid()); callableStatement.setString(2, employee.getEname()); callableStatement.setInt(3, employee.getSid()); i = callableStatement.execute(); callableStatement.close(); closeConnection(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return i; }

is there any chance of using DDL CREATE command in such type? using prepared statements?

Answer1:

Yes you can, if you use EXECUTE and wrap it in a FUNCTION. The function call allows you to pass parameters, and inside the FUNCTION you use string manipulation to modify the DDL statement. Finally, the use of EXECUTE in the FUNCTION makes it so. Here is a simple example of a parameterized CREATE SEQUENCE statement...

DROP FUNCTION sf.start_mc(integer); CREATE FUNCTION sf.start_mc(thefirst integer) RETURNS void AS $$ BEGIN EXECUTE format('CREATE SEQUENCE sf.mastercase START %s',thefirst); END; $$ LANGUAGE plpgsql;

We use the string function "format" to manipulate the statement and include the parameter that was passed to the function. Of course, your SQL looks rather unusual, particularly if you include the CREATE FUNCTION before you call it. This example comes from a data migration job that I recently did. After CREATEing the function we used it like this:

DROP SEQUENCE sf.mastercase; -- the following uses the above function to set the starting value of a new sequence based on the last used -- in the widget table select sf.start_mc((select substring("widgetId",4)::integer + 1 from widgets where "widgetId" like 'MC-%' order by "widgetId" desc limit 1));

Note that the outer SELECT doesn't select anything, it just makes a place to do the function call. The number that is passed as a parameter comes from the inner SELECT which is wrapped in parentheses. A simpler call would be

select sf.start_mc(42);

You can wrap anything in a CREATEd FUNCTION. But this does mean that you are stuck with PostgreSQL and that you need to integrate your DB schema, and schema changes, into your development process as a first class citizen.

Answer2:

Did you try it?

It isn't supported by the server, so even if it seems to work in the client side JDBC driver I don't recommend it:

regress=> PREPARE CREATE TABLE test ( id serial primary key ); ERROR: syntax error at or near "CREATE" LINE 1: PREPARE CREATE TABLE test ( id serial primary key ); ^

There's no advantage to doing so anyway since you cannot <em>parameterize</em> them, so you can't write:

CREATE TABLE ? ( ? text, ...)

and then specify the placeholder values as query parameters to the Statement.

In PostgreSQL only <em>planned</em> statements may be prepared and parameterised server-side. Currently that means INSERT, UPDATE, DELETE and SELECT.

You'll need to do your own string interpolation and safe quoting according to PostgreSQL's <a href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html" rel="nofollow">lexical structure rules</a> - which are pretty much those of the SQL spec. Wrap all identifiers in "double quotes" and double any literal double quotes, eg "these are literal ""double quotes""" for the table name these are literal "double quotes".

The very fact that you want to do this suggests that you probably have design issues in your schema and might need to re-think how you're going about things. Maybe post a more detailed question on dba.stackexchange.com that explains what you want to achieve with this and why?

Recommend

  • Inno Setup - Run InstallUtil from .Net 4.5 Location
  • How to avoid creating multiple string objects in java?
  • Monodevelop: `Waiting for debugger`
  • Creating a POST with url elisp package in emacs: utf-8 problem
  • How to determine from which module a specific function was imported in elixir
  • Using CREATE_FUNC in Cocos2dx
  • The GridView 'OrdersGridView' fired event RowDeleting which wasn't handled
  • How to print next line in python
  • Nuget crash in visual studio 2015
  • How to constantly wait for user input while printing values in a script?
  • Keras metric produces unexpected values
  • check the difference between two arrays of objects in javascript
  • AlertDialog before loading url in webview
  • How to Implement INotifyPropertyChanged on Entity Framework Entity
  • Iterating over mmaped gzip file with boost
  • Shell script to execute nohup against an inputed filename
  • Reveal Icon Button - Bootstrap 3
  • How to detect beginning of line, or: “The name 'getCharPositionInLine' does not exist in t
  • magento 2 rest api : get cart items with images
  • ODBC connection to an .accdb file
  • Python C binding error
  • in Gwt, there are 2 different packages (or 2 options) for doing drag n Drop? Which one is better?
  • Bulk loading into PostgreSQL from a remote client
  • Android NDK refer to external libraries in JNI
  • Opening tel: links from UIWebView
  • Use 2D Text into 3D scenes in JavaFX results in blurry texts
  • Using loops in Jasmine (with injected service)
  • Excel Reverse Match
  • Bundling python(“.py”)files along with java class files for a web application
  • Signed Java web start application with Glassfish 4.1 and Java7
  • Add checkbox dynamically using angular 2
  • Pandas vs matplotlib datetime
  • Magento-Change Attribute of All Products
  • How to define something in JavaScript [closed]
  • Neo4j…how to get a visual representation of my data?
  • Stacked bar chart with continuous time-axis as x-axis
  • matrix multiplication apache pig
  • Google App Engine backend servlet not responding
  • Bitrate JWplayer
  • Accessing Arguments, Workflow Variables from custom activities