UniDac - MySQL Insert Record

[bryan.ray]

Hello,
I've been pounding my head against a wall all day trying to figure this out.

I'm using:
UniDAC 6.1.3 for RAD Studio XE 8.
Windows 8.1
MySQL 5.6 running on a remote windows server (IIS)

I have 2 tables and I can't seem to insert data into them. I don't get any errors on the first insert, but nothing gets inserted.

First Table is stormdata
Second Table is positiondata

I want to insert information into the stormdata table, then get the LastInsertID to be used to add multiple records to the second table.

Here's my code for the first table insert: (no errors, but nothing is inserted)

Code: Select all

Procedure InsertStormDataRecord(aStorm : TStorm);
var
  sql, FormattedDateTime : String;
begin
    DateTimeToString(formattedDateTime, 'yyyy-mm-dd hh:mm', aStorm.AdvDateTime);
    sql := 'INSERT INTO stormdata VALUES('+ quotedStr('0')                      +','+  //this is the autoinc value. needs to be 0
                                         quotedStr(aStorm.Name)                 +','+
                                         quotedStr(aStorm.ID)                   +','+
                                         quotedStr(aStorm.AdvNumber)            +','+
                                         quotedStr(aStorm.Basin)                +','+
                                         quotedStr(formattedDateTime)           +','+
                                         quotedStr(aStorm.Cat)                  +','+
                                         quotedStr(IntToStr(aStorm.Eye))        +','+
                                         quotedStr(IntToStr(aStorm.MoveDir))    +','+
                                         quotedStr(IntToStr(aStorm.MoveSpeed))  +','+
                                         quotedStr(IntToStr(aStorm.SLP))        +','+
                                         quotedStr(aStorm.FA)                   +','+
                                         quotedStr(aStorm.PA)                   +','+
                                         quotedStr(aStorm.CD)                   +','+
                                         quotedStr(aStorm.WW)                   +','+
                                         quotedStr('0')                         +','+
                                         quotedStr(aStorm.Agency)               +','+
                                         quotedStr(GetCatNumber(aStorm.Cat))
                                          + ')' ;

    //Now execute the sql.
    frmMain.insertStormToDB.SQL.Add(QuotedStr(sql));
    frmMain.insertStormToDB.Prepare;
    frmMain.insertStormToDB.Active := True;
    frmMain.insertStormToDB.Execute;
    frmMain.Log('Attempted Insert of '+aStorm.Name+'. Storm Record(s) Affected = '+IntToStr(frmMain.insertStormToDB.RowsAffected)); //this comes back as -1
end;

Here's my code for the second table:

Code: Select all

Procedure InsertPositionDataRecord(aStorm : TStorm);
var
 x, y : integer;
 TypeStr, formattedDateTime, sql, insertID : String;
 pos : TDBPositionObject;
begin

    insertID := '';
    insertID := IntToStr(frmMain.insertStormToDB.LastInsertId);
    ///Populate the DBPositionObject Record
    for x := 0 to aStorm.Positions.Count -1 do
    begin
      sql := '';
      if x = 0 then TypeStr := 'C' else TypeStr := 'F';

      pos.Lat := FloatToStr(aStorm.Positions[x].Lat);
      pos.Lon := FloatToStr(aStorm.Positions[x].Lon);

      DateTimeToString(formattedDateTime, 'yyyy-mm-dd hh:mm', aStorm.AdvDateTime);

      pos.PSDateTime := formattedDateTime;
      pos.Wind := IntToStr(aStorm.Positions[x].Wind);
      pos.Gust := IntToStr(aStorm.Positions[x].Gust);

      for y := 0  to aStorm.Positions[x].WindDataList.Count -1 do
      begin
        if aStorm.Positions[x].WindDataList[y].Level = 34 then
        begin
          pos.r34NE := IntToStr(aStorm.Positions[x].WindDataList[y].NE);
          pos.r34SE := IntToStr(aStorm.Positions[x].WindDataList[y].SE);
          pos.r34SW := IntToStr(aStorm.Positions[x].WindDataList[y].SW);
          pos.r34NW := IntToStr(aStorm.Positions[x].WindDataList[y].NW);
        end;

        if aStorm.Positions[x].WindDataList[y].Level = 50 then
        begin
          pos.r50NE := IntToStr(aStorm.Positions[x].WindDataList[y].NE);
          pos.r50SE := IntToStr(aStorm.Positions[x].WindDataList[y].SE);
          pos.r50SW := IntToStr(aStorm.Positions[x].WindDataList[y].SW);
          pos.r50NW := IntToStr(aStorm.Positions[x].WindDataList[y].NW);
        end;

        if aStorm.Positions[x].WindDataList[y].Level = 64 then
        begin
          pos.r64NE := IntToStr(aStorm.Positions[x].WindDataList[y].NE);
          pos.r64SE := IntToStr(aStorm.Positions[x].WindDataList[y].SE);
          pos.r64SW := IntToStr(aStorm.Positions[x].WindDataList[y].SW);
          pos.r64NW := IntToStr(aStorm.Positions[x].WindDataList[y].NW);
        end;

        if aStorm.Positions[x].WindDataList[y].Level = 12 then
        begin
          pos.wNE := IntToStr(aStorm.Positions[x].WindDataList[y].NE);
          pos.wSE := IntToStr(aStorm.Positions[x].WindDataList[y].SE);
          pos.wSW := IntToStr(aStorm.Positions[x].WindDataList[y].SW);
          pos.wNW := IntToStr(aStorm.Positions[x].WindDataList[y].NW);
        end;
      end;

        //All of the position and wind radii fields have been populated.
        //Now update the stormposition table
        frmMain.insertStormToDB.Active := False;
        frmMain.insertStormToDB.SQL.Clear;

        sql := 'INSERT INTO positiondata VALUES('+
                                        QuotedStr('0')+','+ //this is the auto inc value.  needs to be 0
                                        QuotedStr(insertID)+','+
                                        QuotedStr(pos.Lat)+','+
                                        QuotedStr(pos.Lon)+','+
                                        QuotedStr(pos.PSDateTime)+','+
                                        QuotedStr(TypeStr)+','+
                                        QuotedStr(pos.Wind)+','+
                                        QuotedStr(pos.Gust)+','+
                                        QuotedStr(pos.r34NE)+','+
                                        QuotedStr(pos.r34SE)+','+
                                        QuotedStr(pos.r34SW)+','+
                                        QuotedStr(pos.r34NW)+','+
                                        QuotedStr(pos.r50NE)+','+
                                        QuotedStr(pos.r50SE)+','+
                                        QuotedStr(pos.r50SW)+','+
                                        QuotedStr(pos.r50NW)+','+
                                        QuotedStr(pos.r64NE)+','+
                                        QuotedStr(pos.r64SE)+','+
                                        QuotedStr(pos.r64SW)+','+
                                        QuotedStr(pos.r64NW)+','+
                                        QuotedStr(pos.wNE)+','+
                                        QuotedStr(pos.wSE)+','+
                                        QuotedStr(pos.wSW)+','+
                                        QuotedStr(pos.wNW)+
                                        ')';

        frmMain.insertStormToDB.SQL.Add(QuotedStr(sql));
        frmMain.insertStormToDB.Active := True;
        frmMain.insertStormToDB.Execute;
    end;
end;

I am able to copy the content of the "sql" variable and paste into phpmyadmin and execute the query when I remove the double quotes form the insert values. I think the XE8 IDE shows the double quotes in the debugger.
Another note, if I remove the QuotedStr part from QuotedStr(sql);, I get an SQL error reported back.

Sorry for the long post.. feeling a little frustrated.
Also, If I should be taking a different approach to inserting these records, please let me know. I'm using the direct connection and the compiled program is Win 32.

Thanks again,
Bryan

[ViktorV]

To run your sample correctly, please use the following recommendations:
1. Don't quote the generated INSERT SQL query, since the server will return the '#42000 You have an error in your SQL syntax" error message when executing such query.
2. To execute an INSERT query, you should call the Execute method. Don't set the frmMain.insertStormToDB.Active property to True before calling the Execute method, since your SQL query doesn't return recordset, and you will get the "SQL statement doesn't return rows" error message.

[bryan.ray]

Victor,
Thanks for your reply and tips. I did do as you suggest, but I still get an error on insert.
What is weird is I can copy and paste the generated insert SQL statement into your TUniQuery component and execute the SQL statement and it works. Same is true to phpMyAdmin web interface. It just wont work at runtime in the IDE or standalone exe.

Here's a link to the runtime error:
https://drive.google.com/file/d/0BzB2kZ ... sp=sharing

So confused...
Anymore tips I can try?
Thx
Bryan

[ViktorV]

Are you sure you are clearing the insertStormToDB.SQL.Text property before adding a new INSERT SQL query? If you are, please compose a small sample demonstrating the issue and send it to viktorv*devart*com, including scripts for creating database objects.

[bryan.ray]

Victor,
Sorry for the troubles. You were correct, I didn't clear the sql before 1 of the 3 procedures I have.

Thanks again for your help.

Kind Regards,
Bryan

[ViktorV]

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about UniDAC.