|
This
might seem like a stupid suggestion but try changing the order you pass the
parameters in so the number of records you are retrieving is first and the id is
second.
If you
look at the generated query it uses P1 and P2 and if they standard for
parameters they are the wrong way around.
Its
worth a try.
Owen
Hellu,
I have problems with a advanced finder,
in combination with the Ms SQL
server.
I have a the following
finder:
------------
<finder-method query="select top $2
* from sgssmsmo where acc_id=$1 order
by dattimrec desc"
partial="false">
<!-- Generated SQL: "select top ?
* from sgssmsmo where acc_id=? order
by dattimrec desc"
-->
<method>
<ejb-name>nl.unwired.sgs.sms.SmsMo</ejb-name>
<method-name>findByAcc_IdNext</method-name>
<method-params>
<method-param>int</method-param>
<method-param>int</method-param>
</method-params>
</method>
</finder-method>
---------
The SUN jdbc-odbc driver (I also
tried the i-net Opta driver) fires the
following query (see with the Ms
profiler):
------------
declare @P1 int
set @P1=NULL
exec
sp_prepexec @P1 output, N'@P1 int,@P2 int',
N'select top @P1 * from
sgssmsmo where acc_id=@P2 order by dattimrec desc', 0,
2
select @P1
-----------
Which gives the following
error:
-------------
Nested
exception is:
java.sql.SQLException: [Microsoft][ODBC SQL Server
Driver][SQL Serv
er]Line 1: Incorrect syntax near '@P1'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown
Source)
at
sun.jdbc.odbc.JdbcOdbc.standardError(Unknown
Source)
at
sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown
Source)
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(UnknownSource)
......
-----
If I change the $2 argument in the finder to a
constant:
---------------
<finder-method query="select
top 5 * from sgssmsmo where acc_id=$2 order
by dattimrec desc"
partial="false">
<!-- Generated SQL: "select top 5
* from sgssmsmo where acc_id=? order
by dattimrec desc"
-->
<method>
<ejb-name>nl.unwired.sgs.sms.SmsMo</ejb-name>
<method-name>findByAcc_IdNext</method-name>
<method-params>
<method-param>int</method-param>
<method-param>int</method-param>
</method-params>
</method>
</finder-method>
--------------
Everyting goes fine ?!!!,
that is, the driver fires the following query:
-------------
declare @P1
int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 int', N'select top 5 * from sgssmsmo
where acc_id=@P1 order by dattimrec desc', 0
select
@P1
-------------
Any idea what it is ??? (I am not such a Ms
SQL server expert yet ...)
I print the input argument and they aren't zero
or null:
----
public Collection
findByAcc_IdNext(int acc_Id, int num) throws
RemoteException,
FinderException;
----
BTW: I use Orion 1.5.1 and are parsing it all so
that it can be used on the
Ms SQL database, whereas it worked on the
Postgres database.
Why isn't Orion/the JDBC driver just firing the
generated query that I see
in the orion-ejb.jar file ???.
What am I
missing here :(
Eddie
|
