Re: Query help please ??

[Eddie]

Hellu,   I solved it. Appearantly you can't give an argument to the top command of Ms SQL. I solved ti through SET ROWCOUNT.   Eddie ----- Original Message ----- From: Eddie To: Orion-Interest Sent: Monday, October 08, 2001 8:52 PM Subject: Query help please ?? 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