This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 511
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: 3817

Moved to JDBC forum


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

Joined: Oct 11, 2000
Posts: 1121

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Error Calling stored procedure from my java codes.
 
Similar Threads
NullPointerException on Liferay
how to make java database connectivity with oracle 10g on windows vista operating system.
Unsupported JNDI Object
Error after integrating JForum
uddibrowser + juddi v3 / how to config