'Malformed string' exception when inserting into Firebird (Delphi, UniDAC, UniSQL, INSERT, parameters)

Using Delphi 2010, UniDAC components, Firebird 2.5 SuperServer. Database character set is ISO_8559_1 (my Windows default).

I am writing a data tra开发者_运维知识库nsfer application to transfer data from an Access database to a Firebird database that has identical table structure. I am using a ADOQuery component to select all rows from source table, and then looping through that recordset, and using UniSQL component with an INSERT statement with parameters, assigning parameter values from the corresponding source dataset field values.

When running the insert command, it throws a 'Malformed string' exception. I am stuck and need help to resolve the issue.

Code follows:

function TDataTransfer.BeginTransfer(AProgressCallback: TProgressCallback): Boolean;
var
  slSQLSelect, slSQLInsert: TStringList;
  i, f, z: Integer;
  cmdS, cmdI: String;
  adods: TADODataSet;
  fbcmd: TUniSQL;
  fbscript: TUniscript;
  q: String;
  s : WideString;
begin
  FProgressCallback := AProgressCallback;

  fbscript := TUniscript.Create(nil);
  try
    fbscript.Connection := FirebirdConnection;
    FirebirdConnection.StartTransaction;
    try
      fbscript.Delimiter := ';';
      fbscript.ExecuteFile(ExtractFilePath(ParamStr(0)) + 'Firebird_Script_0.txt');
      FirebirdConnection.CommitRetaining;

      slSQLSelect := TStringList.Create;
      slSQLInsert := TStringList.Create;
      adods := TADODataSet.Create(nil);
      fbcmd := TUniSQL.Create(nil);
      try
        adods.Connection := AccessConnection;
        fbcmd.Connection := FirebirdConnection;

        slSQLSelect.LoadFromFile(ExtractFilePath(ParamStr(0)) + 'Access_Select.txt');
        slSQLInsert.LoadFromFile(ExtractFilePath(ParamStr(0)) + 'Firebird_Insert.txt');

        z := slSQLSelect.Count - 1;
        for i := 0 to z do begin
          cmdS := slSQLSelect[i];
          cmdI := slSQLInsert[i];

          adods.CommandText := cmdS;
          fbcmd.SQL.Text := cmdI;

          adods.Open;

          while not adods.Eof do begin
            for f := 0 to adods.FieldCount - 1 do
              try
                if adods.FieldDefs[f].DataType = ftWideString then begin
                  s := adods.Fields[f].AsAnsiString ;
                  q := '"';
//                  if AnsiStrPos(PAnsiChar(@s), PAnsiChar(q)) <> nil then
//                    s := StringReplace(s, '"', '""', [rfReplaceAll]);
                  fbcmd.Params[f].Value := s;
                end
                else
                if adods.FieldDefs[f].DataType = ftWideMemo then
                  fbcmd.Params[f].SetBlobData(adods.CreateBlobStream(adods.Fields[f], bmRead))
                else
                  fbcmd.Params[f].Value := adods.Fields[f].Value;
              except
                raise;
              end;

            try
              fbcmd.Execute;
              //  FirebirdConnection.CommitRetaining;
            except
              raise;
            end;
            adods.Next;
          end;
          adods.Close;

          FProgressCallback((i + 1) * 100 div (z + 1), 10);
        end;
      finally
        slSQLSelect.Free;
        slSQLInsert.Free;
        adods.Free;
        fbcmd.Free;
      end;

      fbscript.ExecuteFile(ExtractFilePath(ParamStr(0)) + 'Firebird_Script_1.txt');

      FirebirdConnection.Commit;

      Result := True;
    except
      FirebirdConnection.Rollback;
      Result := False;
    end;
  finally
    fbscript.Free;
  end;


end; 

TIA, SteveL

---

If you try to replace s := StringReplace(s, '"', '""', [rfReplaceAll]); with s := StringReplace(s, '''''', '''', [rfReplaceAll]); and uncomment the line;