Database Insert Error

Targets Web
1

Ok I am going crazy here. I am using the below to insert to a database table some field values from my web form.

Var sql As String
sql = "INSERT INTO DBC SET Legacy=?, Hostname=?, Database=?, Username=?, Password=?, ORG_ID=?"

Try
    app.db.ExecuteSQL(sql, legacy, hostName, dataBase, userName, hash, orgID)

    MessageBox("Database Configuration Stored")
Catch error As DatabaseException
    System.Beep
    MessageBox "Connection failed. Error: " + error.Message + " - " + legacy + " - " + hostName + " - " + dataBase + " - " + userName + " - " + orgID
End Try

I get the following error whenever I trigger the code.
" Connection failed. Error: Expected 0 parameters to be bound but received 6"

I am obviously passing 6 parameters but why is it stating it expects 0?

Thoughts?

2

DatabaseException has a number of scenarios where the error message is wrong. What is your database engine? I don’t recognize this syntax and I’ve seen DatabaseException use this error message when the SQL syntax is bad.

3

I am using Xojo Cloud and mySQL. The weird part is I am using this same code in another page but it is only passing 2 parameters.

4

Have you tried passing the arguments as an array?

// Typed in the forum so not tested
var pv() as string
pv.add("first param")
pv.add.("second param")
db.executeSQl(sql, pv())

As @Tim_Parnell mentioned, I’ve noticed that the MySQL plugin returns the wrong error when the number of parameters doesn’t match the prepared statement

5

When I try array I get this error
" Connection failed. Error: Parameters cannot be arrays"

6

Sometimes to help debug the bad error messages, I run the query in a database management tool. Of course, you’ll need to manually fill in some values for the placeholders. That sometimes gives me the actual error.

7

Well, apparently you can’t have a field in a table named database ::Smacks Forehead::

8

Well there is that :rofl:

9

Most reserved names can be used if “quoted” in most DBMS, in MySQL, backquoted, like

INSERT INTO DBC SET `Legacy`=?, `Hostname`=?, `Database`=? ...

Maybe it works… Try it and tell me.

10

Isn’t the insert into suppose to have () and VALUES() insted of SET ?

11

you have to use a variant array

12

You mean as in:

sql = "INSERT INTO DBC (Legacy, Hostname, Database, Username, Password, ORG_ID) values (?, ?, ?, ?, ?, ?, ?)"

.
That’s what I thought, but perhaps that’s an SQLite limitation.

1 Like
13

SQLite is not involved here:

14

I know, which is why I said “Perhaps that’s an SQLite limitation”.

15

MySQL has such alternative option. That’s why I answered him in a focused way towards that DBMS. By the way, I tested my possible solution, and

... SET `Database` = 'whatever' -- works.`

While

... SET Database = 'whatever' -- fails.`
1 Like