UniDac Select & Edit (Caution: Rookie question...)

[sundance]

Hi,
I'm evaluating UniDAC for an app (command line, built with Delphi7) to access a mySQL database.
My first basic test was to read a record from db, change a field value and update the db.

Here is my code:

Code: Select all

uniConn := TUniConnection.Create(nil);
uniQuery := TUniQuery.Create(nil);

try
  uniConn.Server := dbHost;
  uniConn.Port := 3306;
  uniConn.Username := dbUser;
  uniConn.Password := dbPassword;
  uniConn.Database := dbName;
  uniConn.ProviderName := TMySQLUniProvider.GetProviderName;
  uniConn.Open;

  uniQuery.Connection := uniConn;
  uniQuery.SQL.Text := Format('SELECT * FROM %s WHERE hostname=''%s''',[dbTable, ComputerName]); 
  uniQuery.Open;
  uniQuery.First;
  if not uniQuery.Eof then begin
    stUser := uniQuery.FieldByName('username').AsString;
    expDate := uniQuery.FieldByName('reset_date').AsDateTime;

    uniQuery.Edit;
    uniQuery.FieldByName('last_update').AsDateTime := Now;
    uniQuery.Post;
  end;
  uniQuery.Close;

finally
  uniConn.Free;
  uniQuery.Free;
end;

uniQuery.Post throws an exception here (error in SQL statement near "index=3567"), where index is an auto_increment field and thus cannot be written.

My questions are:
- Is my code using edit/post correct or could it be done better?
- How can I inspect the SQL sent to the server?
- How can I handle this error with "index" (without creating a SQL UPDATE statement)?
- How can I use the server's time (mySQL function NOW()) instead of client time (Delphi function "Now")?

Thanks a lot for your help!

.sundance.

[AndreyZ]

Hello,

The point is that the INDEX is a MySQL reserved word. To avoid the problem, you should set the QuoteNames option to True. Here is a code example:

Code: Select all

uniQuery.Options.QuoteNames := True;

- Is my code using edit/post correct or could it be done better?

You can execute the UPDATE statement on the server, for example:

Code: Select all

uniConn.ExecSQL('UPDATE tablename SET last_update = NOW() WHERE ...');

- How can I inspect the SQL sent to the server?

You can use our free tool dbMonitor for monitoring SQL statements sent to the server. For more information about dbMonitor, please refer to http://www.devart.com/dbmonitor/
To use it in your console application, you should create the TUniSQLMonitor component. Note that to use the TUniSQLMonitor component, you should add the UniSQLMonitor unit to the USES clauses of your unit.

- How can I use the server's time (mySQL function NOW()) instead of client time (Delphi function "Now")?

Answered above.