Hi Shalin,

first to your issue. I do not see a call to myConnection.commit() - If the connection is not in auto-commit mode it will get rolled back. See here a bit from the Connection class javadoc:

" Note: When configuring a Connection, JDBC applications should use the appropritate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available. By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved."

Two suggestions:
  1. The proper way to close a connection is by calling close(), in the case of a connection retrieved from c3p0 this does not lead to immediately closing the connection, the connection is instead returned into the connection pool
  2. Creating connections is expensive, that is why we have connection pools such as c3p0. In DatabaseConnection.closeConnection() you destroy the complete pool, and in  SingletonConnection.instance() you recreate it from scratch, which makes me wonder why you use c3p0 at all. Maybe you wanted something different:
class MyDataSource {
  private static volatile MyDataSource DATA_SOURCE;

  private final String driverName, username, password, url;
  private final ComboPooledDataSource datasource;

  public MyDataSource(String driverName, String username, String password, String url) throws Exception {
    this.driverName = driverName;
    this.username = username;
    this.password = password;
    this.url = url;
    this.datasource = setupDataSource(); // here comes your initialization code.
  }

  public static void setDataSource(MyDataSource dataSource) { // Called sometime during system start-up
    DATA_SOURCE = dataSource;
  }

  public static Connection getConnection() {
    Connection connection = DATA_SOURCE.datasource.getConnection();
    connection.setAutoCommit(false);
    return connection;
  }
  ...
}

When you need a connection you do:

Connection connection = MyDataSource.getConnection();
try {
  // do whatever is needed close any statements and result sets explicitly
  connection.commit(); // If we had just a read-only transaction we would not call commit
} finally {
  connection.close();
}

with regards,
juraj
Send c3p0-users mailing list submissions to
	c3p0-users@lists.sourceforge.net

To subscribe or unsubscribe via the World Wide Web, visit
	https://lists.sourceforge.net/lists/listinfo/c3p0-users
or, via email, send a message with subject or body 'help' to
	c3p0-users-request@lists.sourceforge.net

You can reach the person managing the list at
	c3p0-users-owner@lists.sourceforge.net

When replying, please edit your Subject line so it is more specific
than "Re: Contents of c3p0-users digest..."


Today's Topics:

   1. Postgres Stored Function with Insert statement executing but
      not altering database tables (Shalin Lazar)


----------------------------------------------------------------------

Message: 1
Date: Fri, 6 Jul 2012 15:30:17 +0200
From: Shalin Lazar <shalin.lazar@gmail.com>
Subject: [c3p0-users] Postgres Stored Function with Insert statement
	executing but not altering database tables
To: c3p0-users@lists.sourceforge.net
Message-ID:
	<CAD7yc6uDgeKOg9nnPyWEfnr2Cwi1N6CfcjE5azxJfwERrH4tuw@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"

Hi,

I have a Java Application that accesses a PostgreSQL Database via a c3p0
Connection pool.

My application's Business Logic is implemented as Postgres Stored
Procedures, as I want to be able to change the front end without much
effort should the need arise. I had originally planned on using EJB 3.0,
but Dali failed to generate Entity Beans from my Postgres Database
which is still
being looked at <https://bugs.eclipse.org/bugs/show_bug.cgi?id=375728>.

I continued my work by using stored procedures, with a simple Singleton
Pattern to handle connections. However, after looking closer at SQL
Injection, I decided to implement CallableStatements together with c3p0 to
manage connections to my database. The c3p0 packages are only found on the
client side (Java Swing front end), with just PostgreSQL 8.4 on my server.
Is this a correct structure?

I'm able to retrieve multiple ResultSets using this setup/structure, but
when I have a Stored Function that is supposed to insert values into a
database table, the Stored Function seems to run well, but no data is
inserted. I even have a sequenced ID (auto increment) which is incremented,
yet the data isn't inserted.

I will break down the code into parts so that you can see where/if I went
wrong.

First the database. Here is an example table:

CREATE TABLE "zzTest"
(
  id bigserial NOT NULL,
  "zzCostSubjectId" bigint NOT NULL,
  "updatedInt" bigint,
  timed date,
  CONSTRAINT "pk_zzTest" PRIMARY KEY (id)
)

A simple Stored Function that inserts values into the table, returning the
newly generated id.

CREATE OR REPLACE FUNCTION zztest_insert(zzcostsubject bigint, updatedint
bigint)
  RETURNS bigint AS
$$
declare
subjectId bigint;
currentTime timestamp without time zone;
begin
currentTime = NOW()::timestamp without time zone;
INSERT INTO "zzTest"("zzCostSubjectId", "updatedInt", timed)
VALUES (zzCostSubject, updatedInt, currentTime);
select into subjectId currval('"zzTest_id_seq"');
return subjectId;
end;
$$
LANGUAGE 'plpgsql';

Since I had already implemented a Singleton Pattern for the jdbc
connection, I've simply used the same pattern to implement the connection
pool.

public class DatabaseConnection {
private static ComboPooledDataSource datasource;
private static final String DRIVER_NAME = "org.postgresql.Driver";
private static final String UNAME = "postgres";
private static final String PWD = "m1a2p3s";
private static final String URL = "jdbc:postgresql://localhost:5432/fps";

public DatabaseConnection (){
datasource = setupDataSource();
}

// load jdbc driver
public static ComboPooledDataSource setupDataSource (){
ComboPooledDataSource cpds = new ComboPooledDataSource();
try{
cpds.setDriverClass(DRIVER_NAME);
} catch (PropertyVetoException e){
e.printStackTrace();
}
cpds.setJdbcUrl(URL);
cpds.setUser(UNAME);
cpds.setPassword(PWD);
// cpds.setMinPoolSize(5);
// cpds.setAcquireIncrement(5);
// cpds.setMaxPoolSize(20);
return cpds;
}
// create connection
public static Connection establishConnection() throws SQLException {
return datasource.getConnection();
}

// close connection
public static void closeConnection() throws SQLException{
DataSources.destroy(datasource);
}
}

NOTE: I had been getting connection errors with defined
setMinPoolSize(), setAcquireIncrement() and setMaxPoolSize() values.
Leaving the default values seems to have solved this problem though I'm not
certain.

Here is the Singleton Pattern:

public class SingletonConnection {
private static DatabaseConnection con;

public SingletonConnection(){}

public static DatabaseConnection instance(){

assert con == null;
con = new DatabaseConnection();
return con;
}
}

I've also implemented an MVC Architecture, and since all Model Classes have
certain things in common that I want them all to do, I've created an
abstract class AbstractModel, which also does a session check as to whether
the user "session" is indeed the one the user is currently on. If not, then
the user should be notified and prompted to re-log in. Here is the
AbstractModel class which is still being worked on:

public abstract class AbstractModel {
protected  Connection myConnection;
protected  Statement stmt;
protected  CallableStatement cstmt;
protected  ResultSet rs;

protected boolean loginCheck; // if userId and userLoginHistoryId are
valid - true, else false
protected boolean userLoggedIn; // if user is already logged in - true,
else false

public AbstractModel (int userId, Long userLoginHistoryId){
createConnection(); // establish connection
loginCheck = false;
userLoggedIn = false;
if (userLoginHistoryId == 0){ // special case for login
loginCheck = true; // userLoginHistoryId = 0
userLoggedIn = false; // set loginCheck to true, userLogged in to false
} else {
userLoggedIn = true;
try{

myConnection.setAutoCommit(false);
cstmt = myConnection.prepareCall("{ ? = call
\"user_login_session_check\"(?,?) }");
cstmt.registerOutParameter(1, Types.BOOLEAN);
cstmt.setInt(2, userId);
cstmt.setLong(3, userLoginHistoryId);
cstmt.execute();
loginCheck = cstmt.getBoolean(1);

// rs = (ResultSet) cstmt.getObject(1);
// ResultSetMetaData rsmd = rs.getMetaData();
// int columnCount = rsmd.getColumnCount();
// UserIdPasswordDAO idPassobj;
// while (rs.next()){
// for (int i = 1; i <= columnCount; i++){
// System.out.println("resultset " + i + ": "+ rs.getObject(i));
// if (i == 1){ // userId
// loginCheck = rs.getBoolean(1);
// }
// }
// }
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally{
// closeConnection();
}
}

}
// // close connection
// public void closeConnection(){
// try{
// if(!rs.equals(null)){
// rs.close();
// }
//
// cstmt.close();
// myConnection.close();
// } catch (SQLException e){
// System.out.println("SQL Exception: ");
// e.printStackTrace();
// }
//
// }

// close connection
public void closeConnection(){
try{
// if (!rs.isClosed()){
// rs.close();
// }
cstmt.close();
SingletonConnection.instance();
DatabaseConnection.closeConnection();
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
}

}

// establish connection
public void createConnection(){
try {
SingletonConnection.instance();
myConnection = DatabaseConnection.establishConnection();
} catch (SQLException e){
e.printStackTrace();
}
}

// login session check
public boolean expiredLoginCheck (){
if (loginCheck == false && userLoggedIn == true){
closeConnection();
return false;
} else {
return true;
}
}
}

Main problem I'm having is deciding how to close a connection. Both seem to
work (commented out and not commented closeConnection() methods) but both
still raise the connection error
when setMinPoolSize(), setAcquireIncrement() and setMaxPoolSize() are
defined.

Here is the implementation of how I would call the Stored Function:

public class UserModel extends AbstractModel{
public UserModel(int userId, Long userLoginHistoryId) {
super(userId, userLoginHistoryId);
// TODO Auto-generated constructor stub
}

// test for insert (zzTest)
public Long zzTestInsert(){
Long result = new Long(0);
try{
myConnection.setAutoCommit(false);
cstmt = myConnection.prepareCall("{ ? = call \"zztest_insert\"(?,?) }");
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.setLong(2, 22222);
cstmt.setLong(3, 33333);
cstmt.execute();
result = cstmt.getLong(1);
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally {
closeConnection();
}
return result;
}

public Long zzTestInsertSimple(){
Long result = new Long(0);
try{
String sql = "select \"zztest_insert\"(222222, 333333);";
stmt = myConnection.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
result = rs.getLong(1);
}
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally {
closeConnection();
}
return result;
}
}

Both methods run identically. I had originally been using Statement stmt
for all my calls, but due to concerns over SQL Injection, I decided to go
with the CallableStatement. Either way, when using a c3p0 connection pool,
neither of them work. They actually return the correct ID (from the
sequence) but there is no data inserted into the database.

Finally, I call these methods from my Controllers in the following manner

@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
UserModel umodel = new UserModel(userId, userLoginHistoryId);
Long result = umodel.zzTestInsert();
System.out.println(result);
}

How is the structure of my application? Is it correctly implemented? What
improvements can I make?

Most importantly, how can I get these Stored Functions to actually insert
values into respective tables? Does Postgres have some sort of security
mechanism that restricts table data modification in such an instance? Am I
supposed to add some settings to my ComboPooledDataSource?

Any help would be greatly appreciated. Need to sort this out first before I
can go continuing with my project.

Best regards,

Shalin.
-------------- next part --------------
An HTML attachment was scrubbed...

------------------------------

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

------------------------------

_______________________________________________
c3p0-users mailing list
c3p0-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/c3p0-users


End of c3p0-users Digest, Vol 69, Issue 3
*****************************************



-- 
Juraj Wagner | jwa@whitestein.com
Whitestein Technologies s.r.o. | www.whitestein.com
Panenska 28 | 811 03 Bratislava | Slovak Republic
Landline +421 2 5930 0726 | Mobile +421 904 402 708