Welcome to the new Gigaspaces XAP forum. To recover your account, please follow these instructions.

Ask Your Question
0

Problems with SQLQuery

When I try to execute a read multiple operation with a SQLQuery I??m getting:

Failed to execute readMultiple: Failed to getCacheContext.; nested exception is: com.j_spaces.core.sadapter.SAException: java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!

This is the code section:

SQLQuery<templ> sqlQuery = new SQLQuery<templ>(templ, query);

Object templs[] = (Object[]) gigaSpace.readMultiple((Object)sqlQuery, Integer.MAX_VALUE, ReadModifiers.EXCLUSIVE_READ_LOCK);

I??m using org.openspaces.core.GigaSpace and this is the query= select * from Templ where id in (1)

Thanks for help.

{quote}This thread was imported from the previous forum. For your reference, the original is [available here|http://forum.openspaces.org/thread.jspa?threadID=1898]{quote}

asked 2008-04-26 17:49:34 -0500

nullcipher2 gravatar image

updated 2013-08-08 09:52:00 -0500

jaissefsfex gravatar image
edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

The query String should not include the ?select * from Templ where? part. It should be: id=1.

In fact - for such query you can use simple template that set the id field to be 1 and all the other fields to be null, No need to use SQLQuery.

Shay

answered 2008-04-26 18:04:00 -0500

shay hassidim gravatar image
edit flag offensive delete link more

Comments

Sorry, the query i`m using is ="id in (1)" for this time, but the problem is that query could be = "in (1,2,3,4)", so id=1 is wrong.

With id=1, the error is the same...

I think the problem is that the space is persistent but Templ has "@SpaceClass(replicate=true,persist=false,fifo=false,includeProperties=IncludeProperties.EXPLICIT)", the persistent objects works but when I try to do a query via gs-ui, ex: select uid,* from Templ WHERE id=1, the gui throws:

java.sql.SQLException: Select failed Failed to execute readMultiple: Failed to getCacheContext.; nested exception is: com.j_spaces.core.sadapter.SAException: java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based! at com.j_spaces.jdbc.SelectQuery.executeOnSpace(SourceFile:362) at com.j_spaces.jdbc.QueryHandler.a(SourceFile:97) at com.j_spaces.jdbc.QueryProcessor.executeQuery(SourceFile:294) at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.j_spaces.obf.iw.a(SourceFile:333) at com.j_spaces.obf.fh.b(SourceFile:292) at com.j_spaces.obf.fh.a(SourceFile:358) at com.j_spaces.obf.jk.dispatch(SourceFile:118) at com.j_spaces.obf.ca.run(SourceFile:62) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) Caused by: java.sql.SQLException: Failed to execute readMultiple: Failed to getCacheContext.; nested exception is: com.j_spaces.core.sadapter.SAException: java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based! at com.j_spaces.jdbc.executor.QueryExecutor.execute(SourceFile:269) at com.j_spaces.jdbc.parser.ExpNode.accept(SourceFile:215) at com.j_spaces.jdbc.SelectQuery.a(SourceFile:400) at com.j_spaces.jdbc.SelectQuery.executeOnSpace(SourceFile:231) ... 13 more

Apparently gigaspaces is searching the record in the database that obviously does not exist.

I need to implement a way to obtain several records of an object, in this case, i need to obtain the Templ records with id= 1, 2, 3, 4, 5 ... with a readMultiple operation. Is there another way to implement this case.

Thanks

nullcipher2 gravatar image nullcipher2  ( 2008-04-27 21:06:06 -0500 )edit

If I'm not wrong any query executed from the UI ignoring the persistent property of the class and treat the class as persistent - i.e. delegate the query to the database. It is not clear if your space configured to use ExternalDatabase. If so , are you running in LRU mode? Please note that such setup (ExternalDatabase + LRU + readMultiple) have some limitations, In most cases the query will be delegated to the database. Single read operations are preferred in this case instead of readMultiple and will use the space cache in better manner - i.e. the database will not be called when not needed. The performance difference in terms of client and space inter-connection overhead will be minimal.

To answer your question - you better perform multiple single separate read operations instead of using in to accomplish what you need. Future versions of the product will have better support for OR and IN SQL conditions.

Shay

shay hassidim gravatar image shay hassidim  ( 2008-04-28 23:05:51 -0500 )edit

Yes, my space is configured with an ExternalDatabase, but the Cache Policy in Cache Manager is "ALL IN CACHE" and Eviction Policy in Standard Cache of Master-Local Cache is LRU. What??s the difference between the Cache Policy and the Eviction Policy. This can affect my SQLQuery??.

nullcipher2 gravatar image nullcipher2  ( 2008-04-29 12:18:18 -0500 )edit

Cache policy and the Eviction Policy are the same thing. Local cache running by default with LRU.

If you are using SQLQuery with local cache and having OR based queries, the recommended way is to use single based read operations with simple template since its behavior is very similar to the way the ExternalDataSource interacts with the database. You might have un-needed calls to the master space to fetch data that will impact the performance.

Shay

Edited by: Shay Hassidim on Apr 29, 2008 4:26 PM

shay hassidim gravatar image shay hassidim  ( 2008-04-29 13:50:32 -0500 )edit

Thanks Shay

nullcipher2 gravatar image nullcipher2  ( 2008-04-29 16:54:47 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2008-04-26 17:49:34 -0500

Seen: 133 times

Last updated: Apr 26 '08