Login Register Actian.com  

Actian Community Forum


Go Back   Actian Community Forums > Ingres Forums > Database General
 

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2007-03-02   #1 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default Ingres max SQL length

Hi all,

Hope yr all having more fun than me.

Im working with Hibernate and Ingres, have had no trouble 'saving' my
objects, but am having endless trouble getting them back again. I have
implemented a 'table per class' strategy, which when retreiving involves a
couple of inner joins. This creates a (what im told is anyways) rather
large sql statement, some 670 characters long. Which Ingres won't seem to
accept because its to long, at least thats the current theory.

So my question is.....

Does Ingres have an upper limit for the size of a single SQL statement?

and if so...

Can this limit be changed?

Heres the sql in question

Code:
   select earlywarni0_.EarlyWarningID as UID0_, earlywarni0_2_.critical as
   critical0_, earlywarni0_2_.corePoolSize as corePool3_0_,
   earlywarni0_2_.keepAliveTime as keepAliv4_0_,
   earlywarni0_2_.maximumPoolSize as maximumP5_0_, earlywarni0_2_.queueSize
   as queueSize0_, earlywarni0_2_.jndiName as jndiName0_, earlywarni0_1_.host
   as host5_, earlywarni0_1_.interval as interval5_, earlywarni0_1_.port as
   port5_, earlywarni0_1_.reciever as reciever5_ from EarlyWarning
   earlywarni0_ inner join WebBasedProcessors earlywarni0_1_ on
   earlywarni0_.EarlyWarningID=earlywarni0_1_.WebBased_ID inner join
   ProcessingService earlywarni0_2_ on
   earlywarni0_.EarlyWarningID=earlywarni0_2_.UID
Any answers/critque or funny jokes wld be welcome at this stage.

Cheers

Tom

Archived post - originally posted at 2005-12-19 10:43:49
MetaTom is offline   Reply With Quote
Old 2007-03-02   #2 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default

Problem solved:

Was the edbc driver all along, switch to using the driver in iijdbc.jar
and we're on to the next problem ;)

Cheers

Tom

Archived post - originally posted at 2005-12-19 10:43:49
MetaTom is offline   Reply With Quote
Old 2007-03-02   #3 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default

That helps lot thank you.

Can start looking in different directions now!

Cheers

Tom

Archived post - originally posted at 2005-12-16 07:58:35
MetaTom is offline   Reply With Quote
Old 2007-03-02   #4 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default

Update:

Have managed to modify the HIbernate dialect so it produces sql that is
accepted by isql but am still getting the same error when Hibernate trys
to do it.

:(

Tom

Archived post - originally posted at 2005-12-19 10:06:34
MetaTom is offline   Reply With Quote
Old 2007-03-02   #5 (permalink)
Ingres Community
 
kschendel's Avatar
 
Join Date: Mar 2007
Location: Pittsburgh, PA
Posts: 2,047
Default

The server has no practical upper limit on the size of an SQL statement. I
don't know
about the middle layers you're going through.

R3 has a limit of 126 range variables per statement. Prior versions had a
limit of 30.
A "range variable" is in effect a correlation name, although subqueries
sometimes
count as range variables.

Your SQL statement doesn't look particularly large to me. What error do
you get?
And can you issue it by hand in a terminal monitor (sql or isql)?

Archived post - originally posted at 2005-12-17 23:11:33
kschendel is offline   Reply With Quote
Old 2007-03-02   #6 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default

Update:

Have managed to modify the HIbernate dialect so it produces sql that is
accepted by isql but am still getting the same error when Hibernate trys
to do it. Was all to do with white space formatting.

:(

Tom

Archived post - originally posted at 2005-12-19 10:07:11
MetaTom is offline   Reply With Quote
Old 2007-03-02   #7 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 10
Default

Hi there,

not sure if this helps but here goes.

Your SQL statement is 670 chars long. I have some SQL statements that I
run via JDBC Prepared Statments that are over 1100 characters in length
and work perfectly so I dont think you are reaching a limit of any kind.

I've had a quick search about and I can't find anything that defines a
maximum length - but I know there is a limit of 32 tables referenced in a
single statement (although some other ingres people I've spoken to believe
this limit is actually 16)

Good luck.

Archived post - originally posted at 2005-12-16 05:45:38
IanG is offline   Reply With Quote
Old 2007-03-02   #8 (permalink)
Junior Member
 
Join Date: Mar 2007
Posts: 11
Default

Ingres wont except the sql through the terminal either, which is begining
to make me think that Hibernate is generanating it wrong somehow. When i
try i get this.

Code:
   1> select earlywarni0_.EarlyWarningID as UID0_, earlywarni0_2_.critical as
   cr
   itical0_, earlywarni0_2_.corePoolSize as corePool3_0_, earlywarni0_2_.keep
   AliveTime as keepAliv4_0_, earlywarni0_2_.maximumPoolSize as maximumP5_0_,
   earlywarni0_2_.queueSize as queueSize0_, earlywarni0_2_.jndiName as jndiN
   ame0_, earlywarni0_1_.host as host5_, earlywarni0_1_.interval as interval5
   _, earlywarni0_1_.port as port5_, earlywarni0_1_.reciever as reciever5_ fr
   om EarlyWarning earlywarni0_ inner join WebBasedProcessors earlywarni0_1_
   on earlywarni0_.EarlyWarningID=earlywarni0_1_.WebBased_ID inner join Proce
   ssingService earlywarni0_2_ on earlywarni0_.EarlyWarningID=earlywarni0_2_.
   UID

   E_US0846 Invalid qualifier 'earlywarni0_2_'. 'earlywarni0_2_' must be
   declared in the FROM clause or equivalent. If used in a target list,
   'earlywarni0_2_' must be declared at the current scope.
   (Fri Dec 16 09:46:55 2005)

   End of Request - Terminated by Errors
Am trying to get someone over on the Hibernate forums to explain, but no
joy as yet.

Thanks for any thoughts/advice/ideas

Tom

Archived post - originally posted at 2005-12-19 04:22:38
MetaTom is offline   Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved