Devart Forums

mgorga

Hello,
I sent an example and you were able to reproduce the error few weeks ago. Do you have an estimation about when it will be solved?

Best regards,
Mario

mgorga

Hello,
I am trying to execute a query that adds a datetime. The query is like this:
UPDATE MY_TABLE
SET SCHEDULEDATE = :MYDATETIME_PARAM + SCHEDULEDATE

The field SCHEDULEDATE and the param MYDATETIME_PARAM are datetime type. This query fails if I connect using direct mode with the error "datetime and datetime2 data types are incompatible with the add operator" but it is working if I connect using OleDB mode.

Moreover, I tested with dbexpsda40.dll version 7.2.4.0 and with this old version is working. I tested version 8.1.4.0 and version 9.0.1.0 and it fails. I tested compiling the code with 32 bits and 64 bits over Windows 10, with the same result. I am using Delphi XE7.

Best regards,
Mario

mgorga

Thank you,
Mario

mgorga

Good Morning,
I already sent the requested information.

Best regards,
Mario

mgorga

Hello,
I am executing a stored procedure that affects one row. I use this compare "SQLStoredProc.ExecProc > 0" to detect if it successfully executed. When the connection is made throw vendorLib "sqloledb" or "msoledbsql" I get value 1 as affected rows. Although, If I connect using Direct connection it returns 0.

I am using Sql Server 2017, dbexpsda40.dll version 8.0.2.0 and Delphi XE6. Is it necessary to configure something to get the return value of ExecProc?

Best regards,
Mario

mgorga

Dear Alex,
Do you have any estimated time to solve the issue? We will release a new version in few weeks and we need this information to apply or not a workaround.

Best regards,
Mario

mgorga

Sorry,
If I read a field of type CHAR o NCHAR on the select statement, the value is trimmed too. If I read a field of type Varchar2 the value it is not trimmed. I suppose it is because the first type has fixed length and in varchar2 the length is variable.
We import data from external databases and we need to support all type of database fields and to be able to import exactly the same data.

Best regards,
Mario

mgorga

Thank you,
it works for literals but with the parameter "coTrimFixedChar" if I read data from CHAR o NCHAR type the spaces are trimmed too. So we need to evaluate if we can change it.

Is it possible to change another parameter to get exactly the same behaviour using dbexpoda.dll version 6.5 than using version 4.20?

Best regards,
Mario

mgorga

Hello,
we are using Delphi 7 so the code has some differences. The main changes:
1. We use another DriverName and another connection string.
2. To be sure that the error is reproduced, it is necessary to use random literal. This forces Oracle to recalculate the execution plan of the query. We simulate it using "Test"+RandomNumber on the select.

The example modified:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  SqlExpr,
  CRSQLConnection;

var
  SQLConnection: TSQLConnection;
  SQLQuery: TSQLQuery;

begin
  Randomize;
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'Oracle Net (Core Lab)';
    SQLConnection.GetDriverFunc := 'getSQLDriverORANET';
    SQLConnection.LibraryName := 'dbexpoda.dll';
    SQLConnection.VendorLib := 'dbexpoda.dll';

    SQLConnection.Params.Values['Database'] := '127.0.0.1::XE';
    SQLConnection.Params.Values['User_Name'] := 'scott';
    SQLConnection.Params.Values['Password'] := 'tiger';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Connected := True;
    SQLConnection.ExecuteDirect('alter session set cursor_sharing=force');
    SQLQuery := TSQLQuery.Create(nil);
    try
      SQLQuery.SQLConnection := SQLConnection;
      SQLQuery.SQL.Text := Format('SELECT ''test%d'' as TEST FROM DUAL', [Random(100)]);
      SQLQuery.Open;
      Writeln(Format('Length %d', [Length(SQLQuery.Fields[0].AsString)]));
      Writeln(SQLQuery.Fields[0].AsString + '.');
      Writeln('OK');
    finally
      SQLQuery.Free;
    end;
  finally
    SQLConnection.Free;
    readln;
  end;
end.

Best regards,
Mario

mgorga

Dear,
we are using literals in some of our queries and we are getting some extra white spaces at the literal. For example:
SELECT 'test' as TEST
FROM DUAL;

Instead of getting "test", the expected value, we get "test " with some extra blanks at the end. This error only occurs if the parameter cursor_sharing of Oracle is configured as "FORCE" or "SIMILAR", and works with "EXACT". That problem occurs using dbexpoda.dll version 6.5.8.0 and works using version 4.20.0.8.

Now the only solution is to force "EXACT" in all connections. Then we need to execute "ALTER SESSION SET cursor_sharing='SIMILAR'" on connection time. This increase the connection time and we will be force to test again critical sections of our code.

What the difference between dll versions? We would like to know why it is happening before make any change in production environment. I can send a code example to reproduce the error if it is necessary.

Best regards,
Mario

Devart Forums

Search found 10 matches

Re: datetime and datetime2 data types are incompatible with the add operator

datetime and datetime2 data types are incompatible with the add operator

Re: ExecProc doesn't return number of rows affected using Sql Direct mode

Re: ExecProc doesn't return number of rows affected using Sql Direct mode

ExecProc doesn't return number of rows affected using Sql Direct mode

Re: Extra white space using cursor_sharing FORCE

Re: Extra white space using cursor_sharing FORCE

Re: Extra white space using cursor_sharing FORCE

Re: Extra white space using cursor_sharing FORCE

Extra white space using cursor_sharing FORCE