PDA

View Full Version : Data manipulation with Pentaho's SQLConnection



bjork
08-13-2012, 04:23 AM
Hi,

I am trying to create an JSP page that uses Pentaho's SQLConnection to insert data into a table in MySQL. However, it throws an exception
org.apache.jasper.JasperException: javax.servlet.ServletException: java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

How can I issue data manipulation statements with Pentaho's SQLConnection? If not possible, what is best practices to do this within the platform? Below is the code I'm using



// Establish connection to database
response.setCharacterEncoding(LocaleHelper.getSystemEncoding());
IPentahoSession userSession = PentahoHttpSessionHelper.getPentahoSession(request);
SQLConnection connection = (SQLConnection) PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, "myConnection", userSession, userSession);

// Set parameters
List params = new ArrayList();
params.add(id);
params.add(title);

// Create query
String query = "INSERT INTO news (id, title) VALUES (?, ?) ON DUPLICATE KEY UPDATE title=?";

try {
connection.prepareAndExecuteQuery(query, params);
connection.execute(query);
} finally {
connection.close();
}

hansva
08-13-2012, 05:32 AM
Xactions can fire updates. not sure about inserts.

bjork
08-13-2012, 06:55 AM
How would I use Xactions to solve this? Pass the data in as parameters? The problem is that eventually I want to pass free text, which can potentially be very long, and then I will run into the problem with too long URLs if I pass them as parameters.

Taqua
08-13-2012, 07:59 AM
The SQLConnection class offers a plain "execute()" method that can be used to fire updates. However, this does not allow you to use prepared statements (and thus excludes you from safe parametrization).

Therefore: Do it the Java way:
(The following code replaces the body of your try-catch block)



java.sql.Connection realConnection = connection.getNativeConnection();
java.sql.PreparedStatement pstmt = realConnection.prepareStatement(query);
pstmt.setParameter (1, id);
pstmt.setParameter (2, title);
pstmt.executeUpdate();

bjork
08-28-2012, 01:39 AM
Hi,

I ended up using the execute method in org.pentaho.platform.plugin.services.connections.sql.SQLConnection and setting the parameters myself. It will try using the getNativeConnection() suggested by Taqua. Thanks!

If