Devart Forums

(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.

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

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