Sql update in mysql

Databases
1

Hi…
I was trying to update the database field, I am using the sample project of xojo.

[code]Dim sql As String
Dim data As RecordSet

mDB.SQLExecute(“UPDATE FROM team WHERE ID = '” + TextField4.text + “’”)

// Update the customer information
data.Field(“ID”).StringValue = TextField4.Text
data.Field(“name”).StringValue = TextField1.Text
data.Field(“coach”).StringValue = TextField2.Text
data.Field(“city”).StringValue = TextField3.Text
// Update the record in the database
data.Update

mDB.Commit[/code]

Its showing an error while executed.

Any helps…?

Thanks,
Arief

2

You have the wrong SQL syntax.

Try:

 mDB.SQLExecute("UPDATE team Set Field1 = 'MyValue' WHERE ID = '" + TextField4.text + "'")

Or use a prepared statement for user defined text input.

https://documentation.xojo.com/index.php/PreparedSQLStatement

3

Use this:

[code] Dim data As RecordSet = mDB.SQLSelect(“SELECT name, coach, city FROM team WHERE ID = '” + TextField4.text + “’”)

data.Edit
// Update the customer information
data.Field(“ID”).StringValue = TextField4.Text
data.Field(“name”).StringValue = TextField1.Text
data.Field(“coach”).StringValue = TextField2.Text
data.Field(“city”).StringValue = TextField3.Text
// Update the record in the database
data.Update

[/code]

4

Using a prepared statment prevents errors when the user enters things like an apostrophe.

Here is an example.

[code]
dim ps as MySQLPreparedStatement

mDB.Prepare(“UPDATE team SET ID = ?, name = ?, coach = ?, city = ? WHERE ID = ?”)

ps.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(2,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(3,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(4,MySQLPreparedStatement.MYSQL_TYPE_STRING)

ps.Bind(0,TextField4.text)
ps.Bind(1,TextField1.text)
ps.Bind(2,TextField2.text)
ps.Bind(3,TextField3.text)
ps.Bind(4,TextField4.text)

ps.SQLExecute[/code]

5

Hi,

I have been tested, both not working, both code still showing an error, an exception of class nilobjectexception.

The database is exist, but still error for nilobjectexception.

Thanks
Arief

6

Are you sure you are connected to the database? Did you call mDB.Connect?

7

Yes its connected to the database,
Inserting row and deleting is working properly, only updating which is not working.

Thanks
Arief

8

It is probably the

dim mDB as MySQLCommunityServer

code in Neil’s example. Remove or comment out that line.

Also, you are not explaining the error fully. The error will say on which line and the debugger shows you the issue. Please tell us more about the error.

9

[quote=332018:@Simon Berridge]It is probably the

dim mDB as MySQLCommunityServer[/quote]

I revised my example. Sorry I had meant to exclude that line.

10

UPDATE FROM team WHERE ID …

Is not valid SQL… update WHAT? Changing WHAT?

You wont get a recordset returned.

If you correct the statement as Simon suggested to say

Dim data As RecordSet = mDB.SQLSelect("SELECT name, coach, city FROM team WHERE ID = '" + TextField4.text + "'")

Then it may generate a recordset.
(But if mDB is NIl or is not connected, it will fail)

If the SQL runs , it is possible that something typed into TextField4.text can break the statement… it only needs someone to put a semicolon in there and the call will fail.

Lets assume they put in a sensible value such as 23

If they do, and a record 23 exists, then you will get a returned recordset

There is no need to do this:

data.Field("ID").StringValue = TextField4.Text

because you are not changing the ID.

Neil Burkholder’s post is the correct method.

11

Oh, i am forgot to informed, the database is using mysql from phpmyadmin in xampp. Is there any difference between phpmyadmin mysql and sqlcommunityserver?

What is the meaning of ‘?’ sign in neil’s code?

Thanks
Arief

12

It’s a place holder.

Please check the link below. It should help you grasp the idea.
https://documentation.xojo.com/index.php/MySQLPreparedStatement

13

Not on the local machine.

14 
Also, you are not explaining the error fully. The error will say on which line and the debugger shows you the issue. Please tell us more about the error.

The error comes while the code is execute, not in the line code, maybe I have to do checking with the xampp permission.

The xammp app is running on windows 7 64bit machine, the xojo app is running on 32bit machine, connected ny using local pc ip address.

Just the last question, this sql code, will it run on real studio too or just in xojo?

Thanks
Arief

15

xampp runs mysql as a service, so permissions shouldn’t be a problem.

sql runs the same in both. However, the code you originally posted will not run at all.

16

ok, Now I tried to copy neil’s code, then its worked…

[code] mDb = New MySQLCommunityServer
dim ps as MySQLPreparedStatement

mDb.Host = “192.168.1.3”
mDb.UserName = “root”
mDb.Password = “dbexample”
mDb.DatabaseName = “test”

If mDb.Connect Then
mIsConnected = True
ConnectStatusLabel.Text = “Connected to MySQL”
Else
mIsConnected = False
ConnectStatusLabel.Text = "Error connecting to MySQL: " + mDb.ErrorMessage
End If

ps=mDB.Prepare(“UPDATE team SET ID = ?, name = ?, coach = ?, city = ? WHERE ID = ?”)

ps.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(2,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(3,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(4,MySQLPreparedStatement.MYSQL_TYPE_STRING)

ps.Bind(0,TextField4.text)
ps.Bind(1,TextField1.text)
ps.Bind(2,TextField2.text)
ps.Bind(3,TextField3.text)
ps.Bind(4,TextField4.text)

ps.SQLExecute

DataList.DeleteAllRows

Dim sql As String
sql = “SELECT * FROM Team”

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
data.IdxField(3).StringValue, data.IdxField(4).StringValue)

  data.MoveNext
Wend
data.Close

End If[/code]

Thanks for the helps,

Regards,
Arief