Passing parameters to Insert statement in PostgreSQL

Question

I have a WCF windows service and that service is used to do the CURD operation over PostgreSQL. Now I am trying to pass dynamic parameters to insert or update statements in PostgreSQL.

I have built a string at the client end with the query and parameters and at the service end I am parsing that string for Query and parameter name, values.

Here is the string that I am sending from the client end: Insert into Test_Images (ImageID,Image,Description) Values (@ImageID,@Image,@Description)*!*cf92d82b3fc6b53490df71a2e2989dfc*!*@ImageID*!*5*!*@Image*!*5646546546546546541621321654168514685141654564532168541*!*@Description*!*'Test'

Here is the code that I am using at the service end to pass these values to the database:

Private Sub InsertBlob()
    'dbconn is the psqlconnection       
    procCmd.Connection = dbConn
    If (paramValues IsNot Nothing And paramNames IsNot Nothing) Then
        If (paramNames.Length <> paramValues.Length) Then
            Dim sE As New Exception("Parameter Names count and Values count do not match")
            sE.Data.Add("HTTPSTATUSCODE", 400)
            Throw sE
        End If
        For pCount = 0 To paramNames.Length - 1
            procCmd.Parameters.AddWithValue(paramNames(pCount), paramValues(pCount))
        Next
    End If
    dbConn.Open()
    procCmd.CommandText = sQuery
    procCmd.ExecuteNonQuery()
End Sub

This method is giving up the pervasive error. Pervasive.Data.SqlClient.Lna.k: [LNA][PSQL][SQL Engine]Error in expression: @ImageID

While the query below without parameters always executes correctly.

 Insert into Test_Images (ImageID,Image,Description) Values (2,5646546546546546541621321654168514685141654564532168541,'Test')

Could someone please help me with inserted these values as parameters. And also I would need help in passing the BLOB data for the Image field.

Note: The data for the Image field is a LONGVARBINARY type, as I cannot insert the total BLOB array here I have inserted a small binary string just for understanding. I am using BLOB array as I cannot send the path for the image as the client and server will not be on the same machine.

Can you show what values you are passing to the paramValues? Don't use AddWithValue, but create parameter with explicitly setting it's type — Fabio, Jun 13 '20 at 02:59
The "@" prefix for parameters is a Microsoft thing. Some ADO.NET providers have added support for it, e.g. Connector/Net for MySQL, because they are being used in a Microsoft stack. That's not the case for all though. Your question boils down to what is the correct way to represent a parameter for PostgreSQL and that is something that you can easily research for yourself. — jmcilhinney, Jun 13 '20 at 03:00
*I have built a string at the client end with the query* - no way I'd allow a client of my webservice to build an SQL string and send it in for execution; that's a massive hack just waiting to happen — Caius Jard, Jun 13 '20 at 08:39
@Fabio I am sending '(2(DB TYPE-Integer), Byte Array(BLOB VALUE of Image, DB TYPE-LONGVARBINARY), Test (DB TYPE-VarChar))' as ParamValues. As these are the Dynamic values I had difficulty getting the type of each parameter. Could you suggest me if there is any way to do that? I tried sending the Image Parameter value as base64 Encoded and sent it directly in the query string, but is throwing up an error. Could you let me know the correct way to send the image value for a LongVarBinary type? Thanks in advance. — teja, Jun 15 '20 at 14:20

score 0 · Answer 1 · answered Jun 13 '20 at 08:32

0

The fine manual says "use ? as parameter placeholders, positionally. Add values in the same order as ? appear in the SQL" (paraphrase)

answered Jun 13 '20 at 08:32

Caius Jard

72,509
5
49
80

Passing parameters to Insert statement in PostgreSQL

1 Answers1