Hi to everyone.
Im new with PostgreSQL. Im migrating an SQLServer DB to PostgreSQL and Im having
some problems with insertions. The problem consist only with the SQL INSERT
function via the JDBC driver. When I try to insert some data to some table It
seems that everithing its working fine, but I cannot see the new data in the
table. In the other hand, when I use the SQL Editor from PGAdmin3 it works fine
the same statement.
The other SQL functions like SELECT work fine with the same JDBC so my problem
is only with INSERT.
Please help, because I dont know what Im doing Wrong and Im not receiving any
error messages of any kind. My application is an WEB Based one and I am using
the following:
Linux White Box 3.0 Resping 1
SDK Java 1.4.2
Tomcat 4.1.31
Apache 2.0
mod_jk 1.2
PostgreSQL 8.1
postgresql-8.1dev-400.jdbc3.jar (Downloaded from Postgresql.org)
My DB is coded with LATIN1
The tomcat logs doesnt show any errors
The PostgreSQL logs doesnt show any errors
My application doesnt crash
All my tests done with postgres account.
This is my Stored Procedure
-----------------------------
CREATE OR REPLACE FUNCTION guardavisita(int4, int4, "varchar", "varchar",
"varchar", "varchar", "varchar", "varchar", "varchar", "varchar", "varchar",
"varchar", "varchar", "varchar", "varchar")
RETURNS int4 AS
$BODY$DECLARE
v_idproducto ALIAS FOR $1;
v_tipo ALIAS FOR $2;
v_http_ip ALIAS FOR $3;
v_http_host ALIAS FOR $4;
v_http_tamanocont ALIAS FOR $5;
v_http_mime ALIAS FOR $6;
v_http_path ALIAS FOR $7;
v_http_metodo ALIAS FOR $8;
v_http_srvltpath ALIAS FOR $9;
v_http_nombresrv ALIAS FOR $10;
v_http_puerto ALIAS FOR $11;
v_http_protocolo ALIAS FOR $12;
v_http_sesionid ALIAS FOR $13;
v_http_url ALIAS FOR $14;
v_http_pais ALIAS FOR $15;
BEGIN
INSERT INTO visitas (fechahora, idprod, tipo, http_ip, http_host,
http_tamanocont, http_mime, http_path, http_metodo, http_srvltpath,
http_nombresrv, http_puerto,http_protocolo, http_sesionid, http_url, http_pais)
VALUES (CURRENT_DATE, v_idproducto, v_tipo, v_http_ip, v_http_host,
v_http_tamanocont, v_http_mime, v_http_path, v_http_metodo, v_http_srvltpath,
v_http_nombresrv, v_http_puerto, v_http_protocolo, v_http_sesionid, v_http_url,
v_http_pais);
RETURN 0;
END;$BODY$
LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION guardavisita(int4, int4, "varchar", "varchar", "varchar",
"varchar", "varchar", "varchar", "varchar", "varchar", "varchar", "varchar",
"varchar", "varchar", "varchar") OWNER TO postgres;
-----------------------------
This is the way I call it
-----------------------------
try
{
Connection conexion = poolDeConexion.getConnection();
try
{
CallableStatement cs = conexion.prepareCall("{? = call
guardavisita(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt(2, iIDProducto);
cs.setInt(3, Tipo);
cs.setString(4, http_IP);
cs.setString(5, http_Host);
cs.setString(6, http_TamanoCont);
cs.setString(7, http_MIME);
cs.setString(8, http_Path);
cs.setString(9, http_Metodo);
cs.setString(10, http_SrvltPath);
cs.setString(11, http_NombreSrv);
cs.setString(12, http_Puerto);
cs.setString(13, http_Protocolo);
cs.setString(14, http_SesionID);
cs.setString(15, http_URL);
cs.setString(16, http_Pais);
cs.execute();
respuesta = cs.getInt(1);
System.out.println("The SP answer = " + respuesta); //This always is 0
cs.close();
poolDeConexion.free(conexion);
}
catch(SQLException sqle)
{
System.out.println("Some message : " + sqle.getErrorCode() + "->" + sqle);
}
}
catch(Exception e)
{
System.err.println("Some message : " + e );
}
-----------------------------
Thanks in advance.
Hugo Maldonado
