File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error Calling stored procedure from my java codes.

 
Pramod Paritala
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
amit punekar
Ranch Hand
Posts: 544
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3908
9
Chrome Fedora Hibernate
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moved to JDBC forum
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic