Simple Insert

[11_azrael_11]

Try to do this but it tells me that i have a synthax error so.

Code: Select all

  vQuery.Close;
  vQuery.SQL.Clear;
  vQuery.ParamCheck := False;
  vQuery.SQL.Add
    ('INSERT INTO USER (NUM, NICKNAME, PASSWORD, EMAIL, IP_ADDRESS, NAME, SURNAME, AVATAR, ' +
    'DATETIME_CREATED, LAST_DATETIME_VISIT, COUNTRY, COUNTRY_CODE, GENRE, SERVER_FOLDER) VALUES (' + User_Reg.Database_Num + ', ' +
     User_Reg.Username + ', ' + User_Reg.Password + ', ' + User_Reg.Email + ', ' + User_Reg.IP + ', ' + User_Reg.Name + ', ' +
    User_Reg.Surname + ', ' + User_Reg.Avatar + ', ' + User_Reg.DateTime_Created + ', ' +
    User_Reg.DateTime_Created + ', ' + User_Reg.Country + ', ' + User_Reg.Country_Code + ', ' + User_Reg.Genre
    + User_Reg.Server_Folder + ')');
  vQuery.ExecSQL;
  

Values everythin is VarChar(100) except the Num, Avatar, genre that is Integer with range (10);

When i run this command in the mysql server everything run just fine so what i am doing wrong.

I user delphi community edition.
It is an FMX application.

Thank you.

[marsupilami]

Hello Azrael,

without knowing the variables it is hard to tell what happens. TZSQLMonitor can log the query that gets sent to the database actually.
I could assume that you forgot to add a separator. Also you forgot that Delphi and SQL share the same string encoding routines which means you have to add single quotes:

Code: Select all

  vQuery.Close;
  vQuery.SQL.Clear;
  vQuery.ParamCheck := False;
  vQuery.SQL.Add
    ('INSERT INTO USER (NUM, NICKNAME, PASSWORD, EMAIL, IP_ADDRESS, NAME, SURNAME, AVATAR, ' +
    'DATETIME_CREATED, LAST_DATETIME_VISIT, COUNTRY, COUNTRY_CODE, GENRE, SERVER_FOLDER) VALUES (''' + User_Reg.Database_Num + ''', ''' +
     User_Reg.Username + ''', ''' + User_Reg.Password + ''', ''' + User_Reg.Email + ''', ''' + User_Reg.IP + ''', ''' + User_Reg.Name + ''', ''' +
    User_Reg.Surname + ''', ''' + User_Reg.Avatar + ''', ''' + User_Reg.DateTime_Created + ''', ''' +
    User_Reg.DateTime_Created + ''', ''' + User_Reg.Country + ''', ''' + User_Reg.Country_Code + ''', ''' + User_Reg.Genre /* look here -> */ + ''', '''
    + User_Reg.Server_Folder + ''')');
  vQuery.ExecSQL;

In Your case I strongly recommend the use of parameters:

Code: Select all

  vQuery.Close;
  vQuery.SQL.Text := 'INSERT INTO USER (NUM, NICKNAME, PASSWORD, EMAIL, IP_ADDRESS, NAME, SURNAME, AVATAR, ' +
    'DATETIME_CREATED, LAST_DATETIME_VISIT, COUNTRY, COUNTRY_CODE, GENRE, SERVER_FOLDER) ' +
    'VALUES (:NUM, :NICKNAME, :PASSWORD, :EMAIL, :IP_ADDRESS, :NAME, :SURNAME, :AVATAR, ' +
    ':DATETIME_CREATED, :LAST_DATETIME_VISIT, :COUNTRY, :COUNTRY_CODE, :GENRE, :SERVER_FOLDER)'
  vQuery.ParamByName('Num').AsInteger := User_Reg.Database_Num;
  vQuery.ParamByName('NICKNAME').AsString := User_Reg.Username;
  vQuery.ParamByName('PASSWORD').AsString := User_Reg.Password;
  vQuery.ParamByName('EMAIL').AsString := User_Reg.Email;
  vQuery.ParamByName('IP_ADDRESS').AsString := User_Reg.IP;
  vQuery.ParamByName('NAME').AsString := User_Reg.Name;
  vQuery.ParamByName('SURNAME').AsString := User_Reg.Surname;
  vQuery.ParamByName('AVATAR').AsInteger := User_Reg.Avatar;
  vQuery.ParamByName('DATETIME_CREATED').AsString := User_Reg.DateTime_Created;
  vQuery.ParamByName('LAST_DATETIME_VISIT').AsString := User_Reg.DateTime_Created;
  vQuery.ParamByName('COUNTRY').AsString := User_Reg.Country;
  vQuery.ParamByName('COUNTRY_CODE').AsString := User_Reg.Country_Code;
  vQuery.ParamByName('GENRE').AsInteger := User_Reg.Genre;
  vQuery.ParamByName('SERVER_FOLDER').AsString := User_Reg.Server_Folder;
  vQuery.ExecSQL;

This way you don't need to think about quotes and it also helps protecting from SQL injection.

Best regards,

Jan

[Fr0sT]

If you printed your resulting Sql and tried to execute it directly in DB IDE, you'd quickly see your mistake

[aehimself]

[...]
In Your case I strongly recommend the use of parameters:
[...]
This way you don't need to think about quotes and it also helps protecting from SQL injection.

Not just in this case, in all cases. SQL injection is deadly, using parameters will prevent that and make your queries look a LOT more professional!