BUG Type "LO"

[Claudio]

Posted: Mon 16 Nov 2009 18:53 Post subject: Type "lo" question
In project Blob fields, change "Bytea" to "lo";

DLL Version: 1.10.0.7

Delphi 2010.

Parameters;


HostName=localhost
DataBase=test
DriverName=DevartPostgreSQL
User_Name=postgres
Password=
OIDAsLargeObject=True
DetectParamTypes =True
UseUnicode=True

Load Picture; OK

Apply Change;

ERROR: TDBXError column "picture" type "lo" but expression is type "Bytea"

Monitor:

INSERT INTO DbxPgsql_BLOB
(id, name, picture)
values
($1, $2, $3)
:1 (Int32,IN) = 1
:2 (WideString,IN) = "Picture test"
:3 (Blob,IN) =


I need work types "LO".

--- IDENTIFY BUG IN - Project original from DEVART in DEMOS.

\Program Files\Devart\Dbx\PostgreSQL\Demos\Win32\BlobPictures\BlobPics

Original Line in MAIN.PAS

procedure TfmMain.btCreateClick(Sender: TObject);
begin
SQLConnection.ExecuteDirect('CREATE TABLE DbxPgsql_BLOB (' + ' id INTEGER PRIMARY KEY,' + ' Name VARCHAR(50),' + ' Picture bytea' + ')');
end;

Change:

procedure TfmMain.btCreateClick(Sender: TObject);
begin
SQLConnection.ExecuteDirect('CREATE TABLE DbxPgsql_BLOB (' + ' id INTEGER PRIMARY KEY,' + ' Name VARCHAR(50),' + ' Picture oid
' + ')');
end;

Run Project

[bork]

Hello

We are investigating your issue. We will notify you as soon as we have any result.

[bork]

Hello

Only standard parameters can be defined by the TSQLConnection.Params property. All extended driver options should be defined in the runtime:

SQLConnection1.Open;
SQLConnection1.SQLConnection.SetOption(coOIDAsLargeObject, Integer(True));
SQLConnection1.SQLConnection.SetOption(coDetectParamTypes, Integer(True));

And remember that parameter coOIDAsLargeObject should be defined together with coDetectParamTypes and DataSet with large objects should be open in a transaction.

You can find more detailed information about this in the dbExress PosgreSQL driver documentation (the Readme.html file).

[Claudio]

Hello,

I can not use "SetOptions" with Delphi 2010 even with TCRSQLConnection.
Using SQLConnection.Params.Add after open does not work.

Thanks.

[bork]

Hello

Starting with CodeGear RAD Studio 2007 you should assign parameter values to TSQLConnection and TCRSQLConnection at run time in this way:

Code: Select all

SQLConnection.Params.Values['Option Name'] := 'Option Value';

So for Delphi 2010 you should execute the following code in the OnBeforeConnect event:

Code: Select all

procedure TForm1.SQLConnection1BeforeConnect(Sender: TObject);
begin
  SQLConnection1.Params.Values['OIDAsLargeObject'] := 'True';
  SQLConnection1.Params.Values['DetectParamTypes'] := 'True';
end;

[Claudio]

hello,

I am with the following message:

invalid UTF8 character sequence 0x00


I've used "Unicode = True" not worked.

What can it be?

Thanks

[bork]

DBExpress Devart PostgreSQL driver doesn't have "Unicode" parameter. If you want to work in the Unicode mode then you should set the "UseUnicode" parameter to True:
SQLConnection1.Params.Values['UseUnicode'] := 'True';

[Claudio]

Sorry I wrote wrong, so I use "UseUnicode.

Thanks.

[bork]

Unfortunately I cannot reproduce your issue.

Try to create a new application and to execute the following code:

Code: Select all

procedure TForm1.BeforeConnect(Sender: TObject);
begin
  TSQLConnection(Sender).Params.Values['OIDAsLargeObject'] := 'True';
  TSQLConnection(Sender).Params.Values['DetectParamTypes'] := 'True';
  TSQLConnection(Sender).Params.Values['UseUnicode'] := 'True';
end;

procedure TForm1.Button6Click(Sender: TObject);
var
  TestSQLConnection: TSQLConnection;
  TestSimpleDataSet: TSimpleDataSet;
  TD: TTransactionDesc;
begin
  TestSQLConnection := TSQLConnection.Create(self);
  TestSimpleDataSet := TSimpleDataSet.Create(self);
  try
    TestSQLConnection.DriverName := 'DevartPostgreSQL';
    TestSQLConnection.LoginPrompt := false;
    TestSQLConnection.Params.Add('HostName=...'); // your HostName
    TestSQLConnection.Params.Add('DataBase=...'); // your DataBase
    TestSQLConnection.Params.Add('User_Name=...'); // your UserName
    TestSQLConnection.Params.Add('Password=...'); // your Password
    TestSQLConnection.Params.Add('BlobSize=-1');
    TestSQLConnection.BeforeConnect := BeforeConnect;
    TestSQLConnection.Open;

    TestSimpleDataSet.Connection := TestSQLConnection;
    TestSimpleDataSet.DataSet.MaxBlobSize := 1;
    TestSimpleDataSet.DataSet.CommandType := ctQuery;

    try
      TestSimpleDataSet.DataSet.CommandText := 'drop table btest_str';
      TestSimpleDataSet.Execute;
    except
    end;
    try
      TestSimpleDataSet.DataSet.CommandText := 'drop table btest_lo';
      TestSimpleDataSet.Execute;
    except
    end;

    TestSimpleDataSet.DataSet.CommandText :=
      'CREATE TABLE btest_str (' +
      '  id INTEGER PRIMARY KEY,' +
      '  Name VARCHAR(50)' +
      ')';
    TestSimpleDataSet.Execute;

    TestSimpleDataSet.DataSet.CommandText :=
      'CREATE TABLE btest_lo (' +
      '  id INTEGER PRIMARY KEY,' +
      '  Picture OID' +
      ')';
    TestSimpleDataSet.Execute;

    TestSimpleDataSet.Close;
    TestSimpleDataSet.DataSet.Close;
    TestSimpleDataSet.DataSet.CommandText := 'select * from btest_str';
    TestSQLConnection.StartTransaction(TD);
    TestSimpleDataSet.Open;
    TestSQLConnection.Rollback(TD);
    TestSimpleDataSet.Append;
    TestSimpleDataSet.FieldByName('ID').AsInteger := 1;
    TestSimpleDataSet.FieldByName('Name').AsString := 'Test';
    TestSimpleDataSet.Post;
    TestSimpleDataSet.ApplyUpdates(-1);

    TestSimpleDataSet.Close;
    TestSimpleDataSet.DataSet.Close;
    TestSimpleDataSet.DataSet.CommandText := 'select * from btest_lo';
    TestSQLConnection.StartTransaction(TD);
    TestSimpleDataSet.Open;
    TestSQLConnection.Rollback(TD);
    TestSimpleDataSet.Append;
    TestSimpleDataSet.FieldByName('ID').AsInteger := 1;
    TBlobField(TestSimpleDataSet.FieldByName('Picture')).LoadFromFile('D:\Documents and Settings\BorisM\Desktop\ak-mag100.bmp');
    TestSimpleDataSet.Post;
    TestSimpleDataSet.ApplyUpdates(-1);

    TestSQLConnection.Close;

  finally
    TestSimpleDataSet.Destroy;
    TestSQLConnection.Destroy;
  end;
end;

And please notify us if you get any errors when executing this code. If this code is executed without any errors then please modify this code so that it reproduces your issue and provide us your changes in the code.