CODEWITHSUNDEEP
vbscriptasp-classicmariadbadodb
Adam
Adam

Reputation: 926

ADODB.Command: Prepared statement not returning expected number of records

I'm using ADODB.Command to execute prepared statements in Classic ASP, however the number of records being return by MariaDB is incorrect, and I can't figure out why.

The code below gives an example of the problem I'm having:

<%

    Dim conn, comm, rs, SQL, SQLparam

    set conn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.Recordset")
    conn.Open("DSN=localhost")

        ' Standard execution            

        SQL = "SELECT * FROM tests WHERE ID > 0"

        set rs = conn.Execute(SQL)  

        response.write "<p>Results: " & uBound(rs.getRows(),2) & "</p>"

        ' Prepared statement execution          

        SQL = "SELECT * FROM tests WHERE ID > ?"
        SQLparam = 0

        Set comm = Server.CreateObject("ADODB.Command")

            comm.ActiveConnection = conn
            comm.CommandText = SQL
            comm.Parameters.Append(comm.CreateParameter("@param",varType(SQLparam),1,250,SQLparam)) 

            set rs = comm.Execute()

            response.write "<p>Results: " & uBound(rs.getRows(),2) & "</p>"

        set comm = nothing

    rs.close() : set rs = nothing   
    conn.close() : set conn = nothing

%>

Output:

Results: 4433 ' as expected
Results: 10

I feel like I must be missing something obvious. I've played around with the "CreateParameter" settings as outlined by Microsoft here, but the wrong number of records are always returned by MariaDB, never mind what I change.

What's also strange is that if I change the SQL to do a SELECT COUNT, the prepared statement returns the correct count value, it just doesn't return the correct number of rows when I try to select them.

Upvotes: 2

Views: 710

Answers (1)

Adam
Adam

Reputation: 926

After some further testing this appears to be a driver issue. I'm using MariaDB but with the MySQL ODBC connector (8.0 Unicode Driver). After switching to the MariaDB ODBC connector the prepared statement works as expected. I apologise for not testing further before posting.

I guess MariaDB and the MySQL ODBC connector are incompatible when trying to perform certain database functionality, because up until now I've never had any issues.

Upvotes: 2

Related Questions