GeeCON Prague 2014*
The moose likes JDBC and the fly likes Error Calling stored procedure from my java codes. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error Calling stored procedure from my java codes." Watch "Error Calling stored procedure from my java codes." New topic
Author

Error Calling stored procedure from my java codes.

Pramod Paritala
Greenhorn

Joined: Dec 04, 2008
Posts: 3
Hi,

I've created stored procedure in postgresql as follows

create or replace function getRecordCount(tableNameAndCond in text) returns integer AS $$
declare
recCount integer;
begin
execute 'select count(*) from '||tableNameAndCond into recCount;
return recCount;
end;
$$Language plpgsql;


I am trying to call this stroed procedure from my java program. Here with I am pasting my java code.

cs = con.prepareCall("{call getrecordcount[(?)] }");
cs.setString(1, query);
rs = cs.executeQuery ();


when i try to execute this bunch of code i am getting an error org.postgresql.util.PSQLException: ERROR: syntax error at or near "as"

here is the full stack trace


13:25:26,020 ERROR [STDERR] org.postgresql.util.PSQLException: ERROR: syntax error at or near "as"
Position: 37
13:25:26,020 ERROR [STDERR] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
13:25:26,020 ERROR [STDERR] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
13:25:26,020 ERROR [STDERR] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
13:25:26,020 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)
13:25:26,020 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
13:25:26,020 ERROR [STDERR] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
13:25:26,020 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
13:25:26,020 ERROR [STDERR] at my.com.ihis.common.dao.BaseDAO.recordCount(BaseDAO.java:768)
13:25:26,020 ERROR [STDERR] at my.com.ihis.maintenance.dao.DepartmentDAO.retrieve(DepartmentDAO.java:239)
13:25:26,020 ERROR [STDERR] at my.com.ihis.maintenance.action.DepartmentAction.search(DepartmentAction.java:188)
13:25:26,020 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
13:25:26,020 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
13:25:26,020 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
13:25:26,020 ERROR [STDERR] at java.lang.reflect.Method.invoke(Unknown Source)
13:25:26,020 ERROR [STDERR] at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
13:25:26,020 ERROR [STDERR] at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
13:25:26,021 ERROR [STDERR] at my.com.ihis.common.action.BaseAction.execute(BaseAction.java:90)
13:25:26,021 ERROR [STDERR] at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
13:25:26,021 ERROR [STDERR] at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
13:25:26,021 ERROR [STDERR] at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
13:25:26,021 ERROR [STDERR] at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
13:25:26,021 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
13:25:26,021 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
13:25:26,021 ERROR [STDERR] at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
13:25:26,021 ERROR [STDERR] at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:189)
13:25:26,021 ERROR [STDERR] at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:91)
13:25:26,021 ERROR [STDERR] at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:92)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
13:25:26,021 ERROR [STDERR] at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
13:25:26,021 ERROR [STDERR] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:325)
13:25:26,021 ERROR [STDERR] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
13:25:26,021 ERROR [STDERR] at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
13:25:26,021 ERROR [STDERR] at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
13:25:26,021 ERROR [STDERR] at java.lang.Thread.run(Unknown Source)


Thanks in Advance,
Pramod


Thanks & Regards,<br />Pramod Kumar Paritala
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 513
Hello Pramod,
Firstly I dont think this should be posted in ORM forum.

Secondly what is the $$ coming at the end of first line where you declare the procedure name. I believe the first line should be clubbed with the rest of the body of Stored procedure.

Regards,
Amit
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3833

Moved to JDBC forum


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Pramod,
I don't know postgresql but it appears to me that you have created a stored function rather than a stored procedure.
If that is the case then your java code is incorrect.
I believe it should be...

For more details you can refer to the java documentation.

Good Luck,
Avi.
 
 
subject: Error Calling stored procedure from my java codes.