These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: SqlQuery and variables
PostPosted: Thu Mar 29, 2007 11:40 am 
Newbie

Joined: Sun Jan 14, 2007 2:52 pm
Posts: 19
I am trying to use a SqlQuery which makes use of MySql user variables.
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html

The syntax is @myVar:=something.
However Hibernate uses :varName as its syntax for parameters. So when I try to run my query it complains that := is an invalid

Code:
org.springframework.orm.hibernate3.HibernateQueryException: Space is not allowed after parameter prefix ':'.


Is there a simple way to escape this? I have tried \\:= without success.

Thanks,
Martin.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 30, 2007 6:33 pm 
Newbie

Joined: Sun Jan 14, 2007 2:52 pm
Posts: 19
I have still not had any success finding any details about this. I have tried a number of sequences that I have found in examples such as a / or enclosing it in {} but neither works. Surely it must be possible to use a : in a statement?

Also when I use addJoin() the result of .list() becomes an array, with one element per those selected. Why is this? They are already correctly nested inside the base entity so why include them at the top level also?


Top
 Profile  
 
 Post subject: Re: SqlQuery and variables
PostPosted: Tue Dec 08, 2009 8:42 am 
Newbie

Joined: Thu Oct 27, 2005 4:07 am
Posts: 11
Hi!

I am running into a very simlar problem when using sql-server 2008:

Code:
String sql = "insert into polygontest (id, name, geom) "
   + "values (2, 'TEST', GEOMETRY::STGeomFromText('POLYGON((40 5, 20 15, 15 20, 40 5))', 0))";
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.executeUpdate();


when doing so, hibernate will treat ":STGeomFromText" as variable replacing it with "?"

the resulting SQL will be:
insert into polygontest (id, name, geom) values
(2, 'TEST', GEOMETRY?('POLYGON((40.0 5.0, 60.0 55.0, 50.0 20.0, 40.0 5.0))', 0));

as a workaround I am using the following deprecated method:
Code:
Statement stmt = session.connection().createStatement();
stmt.execute(sql);


is there a possibility to avoid the replacement?
any escape sequence?

thank you

Peter


Top
 Profile  
 
 Post subject: Re: SqlQuery and variables
PostPosted: Thu Jan 21, 2010 5:44 pm 
Newbie

Joined: Thu Jan 21, 2010 5:37 pm
Posts: 1
So, is there anyway we can use the geometry::<anyfunction> in an hibernate query without having problems with the parameters replacement?!?! I've tried creating a synonym function like this:

Code:
/****** Object:  UserDefinedFunction [dbo].[STGeomFromTextAlias]    Script Date: 01/14/2010 17:34:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[STGeomFromTextAlias] ( @geometry_tagged_text nvarchar(max), @srid INT)
RETURNS geometry
AS
BEGIN
    RETURN geometry::STGeomFromText(@geometry_tagged_text, @srid);
END
GO


But then indexes does not seem to be used or something else is going bad since queries take 15x more time to be executed...

I really need to find some kind of workaround... Anyone has any ideas, like a way to modify the parser itself or any other idea?!?!

Thank you!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.