Message=unterminated dollar-quoted string

ccampbell · Post by **ccampbell** » Fri 18 Jun 2010 22:01

(pg 8.4)

Hi, I'm attempting to execute the sql statement below using the pgSQLScript object. In doing so I receive the following error:

---- Code
sqlText = strstreamCI.ReadToEnd()
strstreamCI.Close()
pgScript = New PgSqlScript(sqlText, pgConn)
pgScript.Execute()

---- Error Details
Devart.Data.PostgreSql.PgSqlException occurred
CallStack=""
DetailMessage=""
ErrorCode=42601
ErrorCode (ExternalException)=-2147467259
FileName=scan.l
Hint=""
LineNumber=907
Message=unterminated dollar-quoted string at or near "$$
SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' )"
Position=79
ProcedureName=base_yyerror
Source=Devart.Data.PostgreSql
StackTrace:
at Devart.Data.PostgreSql.r.c(ad A_0)
at Devart.Data.PostgreSql.ad.af()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Devart.Common.DbScript.ExecuteSqlStatement(SqlStatement sqlStatement)
at Devart.Common.DbScript.b(SqlStatement A_0)
at Devart.Common.DbScript.ExecuteNext(IDataReader& reader)
at Devart.Common.DbScript.Execute()
at pgConvert.modFunctions.LoadFunctions(String strOutputFolder, String strSchema) in C:\DevNet\DDir\pg\pgConvert\modFunctions.vb:line 34
InnerException:

---- problem portion of sqlText:

CREATE OR REPLACE FUNCTION regexp_matches( citext, citext ) RETURNS TEXT[] AS $$
SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
$$ LANGUAGE SQL IMMUTABLE STRICT;

This script is a portion of the \contrib\citext.sql file that gets installed with PostgreSQL. I can load the same script into the SQL editor in Postgres and run it with no problems.

I know it's not a syntax error with the script, but whatever is parcing it inside your control doesn't seem to care for it. Any ideas would be appreciated. Thanks.

StanislavK · Post by **StanislavK** » Mon 21 Jun 2010 09:35

We've reproduced the problem. We will investigate it and inform you about the results.

StanislavK · Post by **StanislavK** » Fri 09 Jul 2010 17:14

We've fixed the problem with creating stored functions using the $ sign. The fix is available in the latest 4.95.146 build of dotConnect for PostgreSQL. The build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area:
http://secure.devart.com/

For more information on improvements and fixes available in dotConnect for PostgreSQL 4.95.146, please refer to
http://www.devart.com/forums/viewtopic.php?t=18426