CODEWITHSUNDEEP
javapostgresql
siva
siva

Reputation: 1

not able to insert values in my postgresql database

In java code:

CallableStatement cs=null;
int bcode=1;
int dcode=3;
int noftab=3;
String sql2 = "{? = call public.insertdepttables('"+bcode+"','"+dcode+"','"+noftab+"')}";
cs = conn.prepareCall(sql2);
cs.registerOutParameter(1,Types.INTEGER);
rset1=cs.getInt(1);
system.out.println("success="+rest1);

In insertdepttables:(Function in my db-postgresql)

CREATE OR REPLACE FUNCTION public.insertdepttables
(
  IN  branch_code  integer,
  IN  dept_code    integer,
  IN  no_tables    integer
)
RETURNS integer AS
$$
DECLARE
count integer;
begin
     IF no_tables>0 THEN     
     LOOP
     insert into t_dept_tables(branch_code,dept_code,table_no)values(branch_code,dept_code,no_tables);

     no_tables=no_tables-1; 
     Count=count+1;
     EXIT when no_tables =0;
     END LOOP ;
     END IF;
RETURN count;
end
$$

actually i need to insert the no_table (i.e.,3) times row in my t_dept_tables in postgresql. So wat i have done is i hav written functions in database. I need to call it from java code. So I used callable stmt there:

My output should be like this in db:

bcode dcode table_no
1      3     1
1      3     2
1      3     3

Now while executing java code, I'm getting the result as,

console:

success=3

but 3 rows arent inserted in db, but while executing the function in my db, the values get inserted. So no wrong in my db function. please help me to sort this out.

Upvotes: 0

Views: 517

Answers (1)

roman
roman

Reputation: 117337

Are you sure you haven't forgot to actually call statement, like:

cs.executeQuery();
...
cs.close();

BTW, your task could be achieved without functions, like (don't use string concatenation, use parameter values):

String stm = "insert into t_dept_tables(branch_code,dept_code,table_no) values(branch_code,dept_code,no_tables); VALUES(?, ?, ?)";
cs = con.prepareStatement(stm);
cs.setInt(1, id);
cs.setInt(3, id);
cs.setInt(3, id);
rs = cs.executeUpdate();

Upvotes: 2

Related Questions