BUG Type "LO"

BUG Type "LO"

Postby Claudio » Tue 20 Apr 2010 13:28

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
Claudio
 
Posts: 25
Joined: Tue 17 Mar 2009 12:47

Postby bork » Wed 21 Apr 2010 12:47

Hello

We are investigating your issue. We will notify you as soon as we have any result.
bork
Devart Team
 
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Postby bork » Tue 27 Apr 2010 14:02

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).
bork
Devart Team
 
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Postby Claudio » Thu 10 Jun 2010 18:40

Hello,

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

Thanks.
Claudio
 
Posts: 25
Joined: Tue 17 Mar 2009 12:47

Postby bork » Fri 11 Jun 2010 12:37

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;
bork
Devart Team
 
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Postby Claudio » Fri 11 Jun 2010 16:23

hello,

I am with the following message:

invalid UTF8 character sequence 0x00


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

What can it be?

Thanks
Claudio
 
Posts: 25
Joined: Tue 17 Mar 2009 12:47

Postby bork » Mon 14 Jun 2010 09:42

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';
bork
Devart Team
 
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Postby Claudio » Tue 15 Jun 2010 16:08

Sorry I wrote wrong, so I use "UseUnicode.

Thanks.
Claudio
 
Posts: 25
Joined: Tue 17 Mar 2009 12:47

Postby bork » Thu 17 Jun 2010 14:21

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.
bork
Devart Team
 
Posts: 643
Joined: Fri 12 Mar 2010 07:55


Return to dbExpress driver for PostgreSQL