RE: Query help please ??
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 queryit uses P1 and P2 and if they standard for parameters they are the wrong way around. Its worth a try. Owen -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of EddieSent: 08 October 2001 19:53To: Orion-InterestSubject: Query help please ??Hellu,I have problems with a advanced finder, in combination with the Ms SQLserver.I have a the following finder: finder-method query="select top $2 * from sgssmsmo where acc_id=$1 orderby dattimrec desc" partial="false" !-- Generated SQL: "select top ? * from sgssmsmo where acc_id=? orderby dattimrec desc" -- method ejb-namenl.unwired.sgs.sms.SmsMo/ejb-name method-namefindByAcc_IdNext/method-name method-params method-paramint/method-param method-paramint/method-param /method-params /method /finder-method-The SUN jdbc-odbc driver (I also tried the i-net Opta driver) fires thefollowing query (see with the Ms profiler):declare @P1 intset @P1=NULLexec sp_prepexec @P1 output, N'@P1 int,@P2 int', N'select top @P1 * fromsgssmsmo where acc_id=@P2 order by dattimrec desc', 0, 2select @P1---Which gives the following error:- Nested exception is:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]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 orderby dattimrec desc" partial="false" !-- Generated SQL: "select top 5 * from sgssmsmo where acc_id=? orderby dattimrec desc" -- method ejb-namenl.unwired.sgs.sms.SmsMo/ejb-name method-namefindByAcc_IdNext/method-name method-params method-paramint/method-param method-paramint/method-param /method-params /method /finder-method--Everyting goes fine ?!!!, that is, the driver fires the following query:-declare @P1 intset @P1=1exec sp_prepexec @P1 output, N'@P1 int', N'select top 5 * from sgssmsmowhere acc_id=@P1 order by dattimrec desc', 0select @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) throwsRemoteException, FinderException;BTW: I use Orion 1.5.1 and are parsing it all so that it can be used on theMs SQL database, whereas it worked on the Postgres database.Why isn't Orion/the JDBC driver just firing the generated query that I seein the orion-ejb.jar file ???.What am I missing here :(Eddie
Re: Query help please ??
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 SQLserver.I have a the following finder: finder-method query="select top $2 * from sgssmsmo where acc_id=$1 orderby dattimrec desc" partial="false" !-- Generated SQL: "select top ? * from sgssmsmo where acc_id=? orderby dattimrec desc" -- method ejb-namenl.unwired.sgs.sms.SmsMo/ejb-name method-namefindByAcc_IdNext/method-name method-params method-paramint/method-param method-paramint/method-param /method-params /method /finder-method-The SUN jdbc-odbc driver (I also tried the i-net Opta driver) fires thefollowing query (see with the Ms profiler):declare @P1 intset @P1=NULLexec sp_prepexec @P1 output, N'@P1 int,@P2 int', N'select top @P1 * fromsgssmsmo where acc_id=@P2 order by dattimrec desc', 0, 2select @P1---Which gives the following error:- Nested exception is:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]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 orderby dattimrec desc" partial="false" !-- Generated SQL: "select top 5 * from sgssmsmo where acc_id=? orderby dattimrec desc" -- method ejb-namenl.unwired.sgs.sms.SmsMo/ejb-name method-namefindByAcc_IdNext/method-name method-params method-paramint/method-param method-paramint/method-param /method-params /method /finder-method--Everyting goes fine ?!!!, that is, the driver fires the following query:-declare @P1 intset @P1=1exec sp_prepexec @P1 output, N'@P1 int', N'select top 5 * from sgssmsmowhere acc_id=@P1 order by dattimrec desc', 0select @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) throwsRemoteException, FinderException;BTW: I use Orion 1.5.1 and are parsing it all so that it can be used on theMs SQL database, whereas it worked on the Postgres database.Why isn't Orion/the JDBC driver just firing the generated query that I seein the orion-ejb.jar file ???.What am I missing here :(Eddie
