Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Data manipulation with Pentaho's SQLConnection

  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Default Data manipulation with Pentaho's SQLConnection

    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

    Code:
    // 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(); 
    }

  2. #2
    Join Date
    Mar 2011
    Posts
    257

    Default

    Xactions can fire updates. not sure about inserts.

  3. #3
    Join Date
    Jan 2011
    Posts
    8

    Default

    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    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)

    Code:
    java.sql.Connection realConnection = connection.getNativeConnection();
    java.sql.PreparedStatement pstmt = realConnection.prepareStatement(query);
    pstmt.setParameter (1, id);
    pstmt.setParameter (2, title);
    pstmt.executeUpdate();
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Jan 2011
    Posts
    8

    Default

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.