You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Collin Peters <ca...@gmail.com> on 2010/03/17 22:13:03 UTC
Using iBatis to create a Postgres function - fails because of
semi-colon
Hi all,
I am trying to accomplish an obscure use-case. I am trying to
implement the generation of a Postgres function (aka stored procedure)
via iBatis. I am using an <insert> tag and simply have the standard
PostgreSQL syntax for creating the funciton within it. I created all
this functionality through unit tests and everything worked great. As
soon as I tried to run the application from within my webapp (Tomcat)
however, it bailed with an "unterminated dollar-quoted string at or
near..." error. After some debugging I found out that it was
truncating the query after the semi-colon.
I guess this makes sense as iBatis uses prepared statements and can
therefore only execute one SQL statement at a time. So in this case I
am executing one SQL statement which is a 'CREATE FUNCTION' but within
that statement there are multiple SQL statements (i.e. the code within
the function). iBatis doesn't seem to be able to handle this though
and simple searches through the generated SQL statement and stops at
the first semi-colon.
I am wondering if there are any work-arounds to this problem? The
only solution that I can think of is to pass off the generation of my
custom function to another postgres function, and simply call that
function with the necessary arguments.
Regards,
Collin
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Using iBatis to create a Postgres function - fails because of
semi-colon
Posted by Guy Rouillier <gu...@burntmail.com>.
I'm focusing on the error message you received: unterminated
dollar-quoted string. Since the code works via iBATIS in the unit test,
it seems iBATIS is not the issue. Try switching your unit test to use
TransactionAwareDataSourceProxy and see if it fails. If so, that will
narrow your problem identification to working with that proxy.
If changing delimiters doesn't work, PostgreSQL can work without dollar
quoted strings. See the documentation for alternatives.
On 3/18/2010 11:55 AM, Collin Peters wrote:
> You are correct. I can create the stored proc via iBatis via a unit
> test, but when I run it as a deployed webapp on Tomcat (using Java btw)
> it fails. So perhaps iBatis isn't to blame here, but hopefully I can be
> pointed in the right direction. The only thing that is really different
> between the unit test and the deployed webapp is the datasource. I'm
> using Spring so the unit test uses
> org.springframework.jdbc.datasource.DriverManagerDataSource and the
> webapp uses
> org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.
>
> I'm not sure what you mean by the delimiter comment. The $ isn't the
> problem, its the semi-colon. AFAIK the semi-colon use to end a
> statement is not configurable and within a stored proc there could
> obviously be many semi-colons.
>
> Collin
>
> On Wed, Mar 17, 2010 at 7:54 PM, Guy Rouillier <guyr-ml1@burntmail.com
> <ma...@burntmailcom>> wrote:
>
> I'm not following your situation. You say it works fine through a
> unit test but fails in Tomcat. By fine, do you mean that you can
> create your stored proc via iBATIS in your unit test? If so and it
> fails on your webapp, then I'm guessing your webapp is using $ signs
> for other purposes. You don't mention what language your webapp is
> using.
>
> At any rate, if you webapp language uses $ signs for its own
> purposes, PostgreSQL allows you to define whatever character you
> wish for delimiters.
>
>
> On 3/17/2010 5:13 PM, Collin Peters wrote:
>
> Hi all,
>
> I am trying to accomplish an obscure use-case. I am trying to
> implement the generation of a Postgres function (aka stored
> procedure)
> via iBatis. I am using an<insert> tag and simply have the standard
> PostgreSQL syntax for creating the funciton within it. I
> created all
> this functionality through unit tests and everything worked
> great. As
> soon as I tried to run the application from within my webapp
> (Tomcat)
> however, it bailed with an "unterminated dollar-quoted string at or
> near..." error. After some debugging I found out that it was
> truncating the query after the semi-colon.
>
> I guess this makes sense as iBatis uses prepared statements and can
> therefore only execute one SQL statement at a time. So in this
> case I
> am executing one SQL statement which is a 'CREATE FUNCTION' but
> within
> that statement there are multiple SQL statements (i.e. the code
> within
> the function). iBatis doesn't seem to be able to handle this though
> and simple searches through the generated SQL statement and stops at
> the first semi-colon.
>
> I am wondering if there are any work-arounds to this problem? The
> only solution that I can think of is to pass off the generation
> of my
> custom function to another postgres function, and simply call that
> function with the necessary arguments.
>
> Regards,
> Collin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> <ma...@ibatis.apache.org>
> For additional commands, e-mail:
> user-java-help@ibatis.apache.org
> <ma...@ibatis.apache.org>
>
>
>
>
> --
> Guy Rouillier
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> <ma...@ibatis.apache.org>
> For additional commands, e-mail: user-java-help@ibatis.apache.org
> <ma...@ibatis.apache.org>
>
>
--
Guy Rouillier
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Using iBatis to create a Postgres function - fails because of
semi-colon
Posted by Collin Peters <ca...@gmail.com>.
You are correct. I can create the stored proc via iBatis via a unit test,
but when I run it as a deployed webapp on Tomcat (using Java btw) it fails.
So perhaps iBatis isn't to blame here, but hopefully I can be pointed in the
right direction. The only thing that is really different between the unit
test and the deployed webapp is the datasource. I'm using Spring so the
unit test uses org.springframework.jdbc.datasource.DriverManagerDataSource
and the webapp uses
org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.
I'm not sure what you mean by the delimiter comment. The $ isn't the
problem, its the semi-colon. AFAIK the semi-colon use to end a statement is
not configurable and within a stored proc there could obviously be many
semi-colons.
Collin
On Wed, Mar 17, 2010 at 7:54 PM, Guy Rouillier <gu...@burntmail.com>wrote:
> I'm not following your situation. You say it works fine through a unit
> test but fails in Tomcat. By fine, do you mean that you can create your
> stored proc via iBATIS in your unit test? If so and it fails on your
> webapp, then I'm guessing your webapp is using $ signs for other purposes.
> You don't mention what language your webapp is using.
>
> At any rate, if you webapp language uses $ signs for its own purposes,
> PostgreSQL allows you to define whatever character you wish for delimiters.
>
>
> On 3/17/2010 5:13 PM, Collin Peters wrote:
>
>> Hi all,
>>
>> I am trying to accomplish an obscure use-case. I am trying to
>> implement the generation of a Postgres function (aka stored procedure)
>> via iBatis. I am using an<insert> tag and simply have the standard
>> PostgreSQL syntax for creating the funciton within it. I created all
>> this functionality through unit tests and everything worked great. As
>> soon as I tried to run the application from within my webapp (Tomcat)
>> however, it bailed with an "unterminated dollar-quoted string at or
>> near..." error. After some debugging I found out that it was
>> truncating the query after the semi-colon.
>>
>> I guess this makes sense as iBatis uses prepared statements and can
>> therefore only execute one SQL statement at a time. So in this case I
>> am executing one SQL statement which is a 'CREATE FUNCTION' but within
>> that statement there are multiple SQL statements (i.e. the code within
>> the function). iBatis doesn't seem to be able to handle this though
>> and simple searches through the generated SQL statement and stops at
>> the first semi-colon.
>>
>> I am wondering if there are any work-arounds to this problem? The
>> only solution that I can think of is to pass off the generation of my
>> custom function to another postgres function, and simply call that
>> function with the necessary arguments.
>>
>> Regards,
>> Collin
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>>
>
> --
> Guy Rouillier
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: Using iBatis to create a Postgres function - fails because of
semi-colon
Posted by Guy Rouillier <gu...@burntmail.com>.
I'm not following your situation. You say it works fine through a unit
test but fails in Tomcat. By fine, do you mean that you can create your
stored proc via iBATIS in your unit test? If so and it fails on your
webapp, then I'm guessing your webapp is using $ signs for other
purposes. You don't mention what language your webapp is using.
At any rate, if you webapp language uses $ signs for its own purposes,
PostgreSQL allows you to define whatever character you wish for delimiters.
On 3/17/2010 5:13 PM, Collin Peters wrote:
> Hi all,
>
> I am trying to accomplish an obscure use-case. I am trying to
> implement the generation of a Postgres function (aka stored procedure)
> via iBatis. I am using an<insert> tag and simply have the standard
> PostgreSQL syntax for creating the funciton within it. I created all
> this functionality through unit tests and everything worked great. As
> soon as I tried to run the application from within my webapp (Tomcat)
> however, it bailed with an "unterminated dollar-quoted string at or
> near..." error. After some debugging I found out that it was
> truncating the query after the semi-colon.
>
> I guess this makes sense as iBatis uses prepared statements and can
> therefore only execute one SQL statement at a time. So in this case I
> am executing one SQL statement which is a 'CREATE FUNCTION' but within
> that statement there are multiple SQL statements (i.e. the code within
> the function). iBatis doesn't seem to be able to handle this though
> and simple searches through the generated SQL statement and stops at
> the first semi-colon.
>
> I am wondering if there are any work-arounds to this problem? The
> only solution that I can think of is to pass off the generation of my
> custom function to another postgres function, and simply call that
> function with the necessary arguments.
>
> Regards,
> Collin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
--
Guy Rouillier
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Using iBatis to create a Postgres function - fails because of
semi-colon
Posted by Collin Peters <ca...@gmail.com>.
Oh - I forgot to mention a few other details.
* I am using the iBatis spring integration (ibatis-sqlmap-2.3.0.jar,
spring-ibatis-2.0.8.jar)
* The weirdest part here is that the code fully works when I run it
through a unit test, but when I run it as a deployed webapp, it fails.
On Wed, Mar 17, 2010 at 2:13 PM, Collin Peters <ca...@gmail.com> wrote:
> Hi all,
>
> I am trying to accomplish an obscure use-case. I am trying to
> implement the generation of a Postgres function (aka stored procedure)
> via iBatis. I am using an <insert> tag and simply have the standard
> PostgreSQL syntax for creating the funciton within it. I created all
> this functionality through unit tests and everything worked great. As
> soon as I tried to run the application from within my webapp (Tomcat)
> however, it bailed with an "unterminated dollar-quoted string at or
> near..." error. After some debugging I found out that it was
> truncating the query after the semi-colon.
>
> I guess this makes sense as iBatis uses prepared statements and can
> therefore only execute one SQL statement at a time. So in this case I
> am executing one SQL statement which is a 'CREATE FUNCTION' but within
> that statement there are multiple SQL statements (i.e. the code within
> the function). iBatis doesn't seem to be able to handle this though
> and simple searches through the generated SQL statement and stops at
> the first semi-colon.
>
> I am wondering if there are any work-arounds to this problem? The
> only solution that I can think of is to pass off the generation of my
> custom function to another postgres function, and simply call that
> function with the necessary arguments.
>
> Regards,
> Collin
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org