How to SQLExecute with insert?

[McKenzie!]

Currently I have changed my game plan. Using the SQLExecute commands opposed to RealStudio's Recordset command. All I have is a book of REALbasic and MySQL reference book. Get all this information in the examples of the web is a waste of time. This is to accept orders RealStudio MYSQL.
Basically, my original plan was to create a simple front-end to see all the records in my (remote) MySQL database. Go forward, backward, search, add and delete records record, but I could not get prevRecord to work. I used the following code:
/

/START SQL CODE HERE
  db.SQLExecute ("INSERT INTO users,
 (FirstName,LastName,Password,Status) "+"Values 
('+FnameBox.text+','+LnameBox.text+','+PwordBox.text+','+StatusBox.text+')")
  if db.error then
    msgbox db.ErrorMessage
    // I GET A SYNTAX ERROR HERE ON db.SQLEXECUTE LINE
  else
    // OK
  end

[Shreevats]

I think you have few errors in your coding. Just break down the sting, build it stepwise. I have done it for you, just have glance over it.
dim test as string

 test="INSERT INTO users (FirstName,LastName,Password,Status) Values ("
  test=test+FnameBox.text+", "
  
  //msgbox test
  test=test+LnameBox.text+", "
  test=test+PwordBox.text+", "
  test=test+StatusBox.text+")"
  
  If db.Connect then
    //connected to database...OK
    msgbox test
    //quit
db.SQLExecute ("test")


My Msgbox displays"
INSERT INTO users (FirstName,LastName,Password,Status) Values (Sam, Adams, 1234, A)

[Villiers]

If you ask me than I would like to let you know that by using that you will get string errors, which will result you to no output. Your test string should be as follows:

Your test string=
INSERT INTO users (FirstName,LastName,Password,Status)
 Values ('Sam', 'Adams', '4566', 'A')

Then execute it:
db.SQLExecute ("test")

This will do the job for you, if you continue using the prior code than you will get the below syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1. Also don’t forget to copy/past the test string value into phpMyAdmin, because it adds the records properly including small details in the RealStudio syntax.

[Ronchi]

In Standard SQL statements it requires semicolon (";") at the end. The names of tables as well as columns need not be cited if you follow the rules and guidelines conservatives. Standards such as the only US-ASCII characters are (AZ, az), digits and a digit not in the first position, with no spaces and all lowercase to uppercase and lowercase to avoid problems. If you break these rules, like having spaces in the name, then you need to cite the name of table or column. I recommend against breaking these rules because SQL makes the job much more difficult. SQL names should have a hyphen to avoid any collision with the keywords.

[Dwarner]

I advise you to define all your columns as NOT NULL, and then use default values wherever necessary. If you want than you can specify an empty string as a default.

Here is a method that I want you to use:

Shared Function escapeAndAddQuotes(data As String) As String
  // Prepare a string for use in a SQL statement.  And then add a single quote mark at beginning and end.

  // © 2011 Basil Bourque. This source code may be used freely forever by anyone taking full responsibility for doing so, without warranty.
  
  if ( data <> "" ) then
    // Replace all single quotes with two single quote characters
    data = DBUtils.escapeQuotes( data )
    
    data = "'" + data + "'" // Add a single quote on each end.
    
  end if
  
  // Return the new data which is ready to be used in SQL
  return data
End Function

[Vandam]

Here below I have put two methods in a class named "DBUtils". I have done it by taking reference from the Apache project. The advantage is that these methods are "Shared" methods i.e. they are not object-oriented or no object needed. You just have to call them by name of the class itself instead of an object reference.

Example Usage:

//This source code may be used freely forever by anyone taking full responsibility for doing so, without warranty.

sql = "SELECT * FROM user_ WHERE login_name_ = " + DBUtils.escapeAndAddQuotes( self.userNameField.Text ) + ";"
rs = db.SQLSelect( sql )
if ( db.Error ) then
  // Add code here to handle errors.
else // Else no error. 
  if ( rs = nil ) then 
    // Handle error.
  Else // Else valid RecordSet.
    // Add code to do what you want to do.
  end if
end if

[void]

I have experienced that while storing data we should use a Prepared Statement to avoid SQL Injection attacks. The same statement should be used for working with data entered by users. Let's break it down a little:

Dim myTest as String
myTest = "INSERT INTO users (firstname) VALUES ('Sam')

Dim firstname as String
firstname = "Sam"

now you can code
myTest = "INSERT INTO users(firstname) VALUES ('" + firstname + "')"

[manimoor]

Hi.The execute statement means,
The EXECUTE statement executes a prepared SQL statement.

Invocation
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java.

Syntax
>>-EXECUTE--statement-name-------------------------------------->

>--+-----------------------------------+-----------------------><
| .-,-------------. |
| V | |
+-USING----host-variable-+----------+
+-USING DESCRIPTOR--descriptor-name-+
| (1) |
'-| multiple-row-insert |-----------'

example
EXEC SQL PREPARE DEPT_INSERT FROM
'INSERT INTO DSN8810.DEPT VALUES(?,?,?,?)';

-- Check for successful execution and read values into S1

EXEC SQL EXECUTE DEPT_INSERT USING 1;