Possibility to debug a prepared statement?

I have a routine where the content for the parameter “?” is flexible built. As a fictive example I would want to select the persons who have the hobbies “handball” and “swimming”.

The SQL would look like

SELECT * FROM tbl_user WHERE ?

The search parameter would be programmatically combined to

“hobby=‘handball’ AND hobby=‘swimming’”

Trying to bind this as MYSQL_TYPE_STRING won’t get a result. The reason might be, that the resulting prepared statement looks like

SELECT * FROM tbl_user WHERE 'hobby='handball' AND hobby='swimming''

In debug mode I’m not able to check the returned prepared statement, it doesn’t show any content.

Is there a possibility to debug a prepared statement?

You can’t use prepared statements like that. You will need two different ones.

Thanks Eli.

But anyway: Is there a possibility to debug a prepared statement?

Not in Xojo, but maybe on the MySQL server by logging the executions?

Thank you, Eli. Will give it a try.

[quote=237698:@Frank Kersten]I have a routine where the content for the parameter “?” is flexible built. As a fictive example I would want to select the persons who have the hobbies “handball” and “swimming”.

The SQL would look like

SELECT * FROM tbl_user WHERE ?

The search parameter would be programmatically combined to

“hobby=‘handball’ AND hobby=‘swimming’”
[/quote]
This wont work

Almost certainly correct

Bound variables are not for statements or fragments of statements but for the variables those statements need

Otherwise sql injection attacks are just as likely and the whole point of prepared statements is, in part, to make sql injection attacks harder if not downright impossible

To create a dynamic prepared statements you need to craft the “base” portion of your statement (SELECT * FROM tbl_user)
Then, in your code see how many criteria were provided & add one WHERE clause + bound variable marker per criteria

Hypothetically something like (this may or may not actually work as this is all written in the forum and not tested)

    dim base_sql as string = "SELECT * FROM tbl_user"
    dim clauses() as string
    for i as integer = 0 to criteria.ubound 
           // lets assume criteria is an array of pairs with 
           // left = a key for the column 
           // right & a value for the value to search for
           clauses.append criteria.left + " = ? "
        next

        dim sql as string = base_sql +If( clauses.ubound >= 0, " where " + join(clauses, "and "), "" )

        dim ps as mysqldb.prepare( sql )
        for i as integer = 0 to clauses.ubound 
             // define the prepared statement bund variables
             // ....... note you need the type of the variable passed in to figure out how to bind this column
             // ... and you may need the type of the db column so if you pass a string to 
            //  select from an integer column you can do the right thing
        next

        dim rs as recordset = ps.sqlselect( ... variable for the select )