Eclipse Community Forums: EclipseLink » Error in implementation(?): Calling Stored Procedures in FireBird 2.1

Home » Eclipse Projects » EclipseLink » Error in implementation(?): Calling Stored Procedures in FireBird 2.1(...results in syntax error)

Show: Today's Messages :: Show Polls :: Message Navigator

Error in implementation(?): Calling Stored Procedures in FireBird 2.1 [message #654343]

Tue, 15 February 2011 10:06

Alexander Orlov

Messages: 10
Registered: July 2009

Junior Member

Calling a Stored Procedure in FireBird 2.1 results in "SQL error code = -104":

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 33
=
Error Code: 335544569
Call: EXECUTE PROCEDURE LOGIN1(param1 = ?, param2 = ?)
bind => [onlinetester, test]
Query: DataReadQuery(name="test_proc" )

Apparently EclipseLink generates the following syntax EXECUTE PROCEDURE LOGIN1(param1 = ?, param2 = ?) which isn't proper FireBird 2.1 syntax and should be called via EXECUTE PROCEDURE LOGIN1 ?, ?.

I've described the details of this problem on StackOverflow: http://stackoverflow.com/questions/4960049/executing-stored- procedures-in-firebird-using-jpa-namedstoredprocedurequery

Report message to a moderator

Re: Error in implementation(?): Calling Stored Procedures in FireBird 2.1 [message #654403 is a reply to message #654343]

Tue, 15 February 2011 15:32

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

From the other post,
@StoredProcedureParameter(queryParameter = "param1", name = "USER_NAME", direction = Direction.IN, type = String.class),
is telling EclipseLink to execute the procedure using named database parameters. If instead of
EXECUTE PROCEDURE LOGIN1("USER_NAME= ? ) you want EXECUTE PROCEDURE LOGIN1(?) try by removing the name from the StoredProcedureParameter definition, leaving it unnamed:

@StoredProcedureParameter(queryParameter = "param1", direction = Direction.IN, type = String.class),

Best Regards,
Chris

Report message to a moderator

Re: Error in implementation(?): Calling Stored Procedures in FireBird 2.1 [message #654509 is a reply to message #654403]

Wed, 16 February 2011 06:12

Alexander Orlov

Messages: 10
Registered: July 2009

Junior Member

Got: "Call: EXECUTE PROCEDURE LOGIN1(param1 = ?)" but the error remains the same. Actually the included parenthesis aren't valid FB SQL syntax. Valid FB SQL syntax should look like "EXECUTE PROCEDURE LOGIN1 ?"

Best Regards,
Alex

Report message to a moderator

Re: Error in implementation(?): Calling Stored Procedures in FireBird 2.1 [message #654682 is a reply to message #654343]

Wed, 16 February 2011 16:24

Alexander Orlov

Messages: 10
Registered: July 2009

Junior Member

Found a solution/workaround: http://stackoverflow.com/questions/4960049/executing-stored- procedures-in-firebird-using-jpa-namedstoredprocedurequery/5 019044#5019044

[Updated on: Wed, 16 February 2011 16:24]

Report message to a moderator

Re: Error in implementation(?): Calling Stored Procedures in FireBird 2.1 [message #654730 is a reply to message #654682]

Wed, 16 February 2011 20:39

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Sorry, only after you found the workaround using the native API to call addUnamedArgument directly was I able to check that EclipseLink is only using named arguments for @StoredProcedureParameter.

Please vote for 294772
- EclipseLink does not support unnamed parameters in @NamedStoredProcedureQuery
to have this fixed.

Best Regards,
Chris

Report message to a moderator