pgsqlException - 'Parameter $1 missing' when I call script.execute

[kcrenshaw]

When I execute the following code, I get the error 'Parameter $1 is missing'. Could someone please help?

Dim conn As PgSqlConnection
Dim connStr As String
Dim cmdStr As String

connStr = "Server=localhost;Port=5432;User Id=postgres;Password=secret;Database=pgdata;"
conn = New PgSqlConnection(connStr)
cmdStr = "CREATE OR REPLACE FUNCTION SP_TEST2(TEXT, TEXT) RETURNS INTEGER AS AS $$ " & _
"DECLARE " & _
"cid ALIAS FOR $1; " & _
"rpid ALIAS FOR $2; " & _
"BEGIN " & _
"SELECT * FROM tasks WHERE createdby = cid AND responsibleperson = rpid; " & _
"RETURN 0; " & _
"END; " & _
"$$ LANGUAGE 'plpgsql';"
Try
If Not conn.State = ConnectionState.Open Then conn.Open()
Dim script As New PgSqlScript(cmdStr, conn)
script.Execute()

Catch ex As Exception
Throw ex
Finally
If Not conn Is Nothing Then
If Not conn.State = ConnectionState.Closed Then conn.Close()
End If
End Try

Thanks!

Kevin

[Alexey]

You have a syntax error: "AS AS"

[kcrenshaw]

Thanks for your reply. However, I ran the script without the syntax error and I still get the same result.

[Alexey]

Could you please provide us with the PostgreSQLDirect version (specify trial/std/pro as well) and your PostgreSQL Server version.
Try to execute your sql statement using any other client (e.g., pgAdmin).

[kcrenshaw]

Thanks for you quick response.

I am using postgresql version 8.1 on Windows XP Professional with PostgresqlDirect version 2.20 Standard edition.

I am able to execute the statement using psql at the command prompt with no problems. It also works using pgAdmin.

Thanks for your help,

Kevin

[Alexey]

Send us small test project if possible to reproduce the problem; it is
desirable to use 'test' schema objects, otherwise include definition of
your own database objects. Do not use third party components.

[kcrenshaw]

I have sent you a sample project to demonstrate the problem to [email protected]. Please let me know if you need anything else. Gmail would not allow me to send the project, so I had to send it from another account. The email has the subject 'Parameter $1 missing' when I call script.execute'.

Thanks,

Kevin

[Alexey]

We were unable to find any project attached. Respective message has been send to your e-mail. Please attach the project to the reply on that message.