Exasol via ADO: Named parameter syntax in SQL-statement

Question

I have a project: Excel frontend, MS Access DB as backend, connecting via ADO.

Now we are migrating MS Access DB to Exasol - still using ADO via {EXASolution Driver}.

One issue I don't get solved are named parameters like in SELECT ProjectName FROM tblProject WHERE IDProject = @ID

Below code runs against MS Access DB but fails with Exasol: invalid token @

If I replace @ID with a ? output is created as expected - with MS Access and Exasol.

That's why I am sure the syntax of @ID is wrong. I found nothing in Exasol documentation - only results referring to queries/scripts.

So my question is: What is the correct Exasol syntax to have named parameters in the SQL-statement.

Sub testExasol()
     
h_DB.connect
Dim cn As ADODB.Connection: Set cn = h_DB.Connection

Dim strSQL As String
strSQL = "SELECT ProjectName FROM tblProject WHERE IDProject = @ID"

Dim cmd As ADODB.Command: Set cmd = New ADODB.Command
Dim p As ADODB.Parameter
With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL

    Set p = New ADODB.Parameter
    With p
        .Name = "p1"
        .Type = adInteger
        .direction = adParamInput
        .value = 4
    End With
    .Parameters.Append p
End With

Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenDynamic, adLockOptimistic

Debug.Print rs.fields(0)

End Sub

h_db is a class handling connection stuff - it can switch between the two DBs. That part is not the issue - as results are returned from Exasol when using the ? as parameter placeholder.

Exasol via ADO: Named parameter syntax in SQL-statement

0 Answers0