Informix support contribution

EMartin · 2015-11-19 14:54:51

Hi @ab,

I implemented Informix DBMS support in SynDB.pas/SynODBC.pas/SynDBOleDB.pas. I tested against Informix IDS 11.70. Also I implemented functions to retrieve stored procedure parameter info.

SynDB.pas
- added support for getting stored procedure information. TSQLDBProcColumnDefine,
TSQLDBProcColumnDefineDynArray, TSQLDBConnectionProperties.GetProcedureParameters,
TSQLDBConnectionProperties.SQLGetParameter.
- added Informix DBMS (dInformix), tested against Informix 11.70

SynDBODBC.pas
- added Informix support

SynDBOleDB.pas
- added TOleDBInformixConnectionProperties

If you are okay, can you add this ?

Any improvement is welcome by anyone.

https://drive.google.com/file/d/0Bx7LPc … sp=sharing

Best regards.

Esteban.

ab · 2015-11-19 17:03:50

I've included the patch as http://synopse.info/fossil/info/2151abcdfc

This was just perfect!
Even the coding style, documentation and comments were just fitting with the framework expectations!

Thanks a lot for sharing.

EMartin · 2015-11-19 18:23:10

Your welcome, your framework makes integration easy.

And I sorry I had to make corrections.

https://drive.google.com/file/d/0Bx7LPc … sp=sharing

Thanks.

Esteban

ab · 2015-11-19 19:23:57

No problem.

With a little bit of refactoring.
See http://synopse.info/fossil/info/65396db07b

EMartin · 2015-11-19 20:20:15

Sorry @ab, I forgot this file:

https://drive.google.com/file/d/0Bx7LPc … sp=sharing

or this little modification in SynDBZeos.pas:

class function TSQLDBZEOSConnectionProperties.URI(aServer: TSQLDBDefinition;
  const aServerName: RawUTF8; const aLibraryLocation: TFileName;
  aLibraryLocationAppendExePath: boolean): RawUTF8;
const
  /// ZDBC provider names corresponding to SynDB recognized SQL engines
  ZEOS_PROVIDER: array[TSQLDBDefinition] of RawUTF8 = (
    '','','oracle:','mssql:','','mysql:','sqlite:',
    'firebird-2.5:','','postgresql-9:','','');
begin
  result := URI(ZEOS_PROVIDER[aServer],aServerName,
    aLibraryLocation,aLibraryLocationAppendExePath);
end;

In ZEOS_PROVIDER I added the last ''

Thanks.

Esteban

EMartin · 2015-11-19 20:40:54

Ok, the last correction, with the refactoring using repeat ... until I changed the fCurrentRow use:

procedure TODBCConnectionProperties.GetProcedureParameters(const aProcName: RawUTF8;
  out Parameters: TSQLDBProcColumnDefineDynArray);
var Schema, Package, Proc: RawUTF8;
    F: TSQLDBProcColumnDefine;
    n,DataType: integer;
    status: SqlReturn;
    FA: TDynArray;
    Stmt: TODBCStatement;
begin
  ...
      if Stmt.fCurrentRow=0 then // Step was not called
        Stmt.Step;                                                    
      repeat
        F.ColumnName := Trim(Stmt.ColumnUTF8(3)); // Column*() should be in order
        case Stmt.ColumnInt(4) of
          SQL_PARAM_INPUT:        F.ColumnParamType := paramIn;
          SQL_PARAM_INPUT_OUTPUT: F.ColumnParamType := paramInOut;
          else                    F.ColumnParamType := paramOut;
        end;
        DataType := Stmt.ColumnInt(5);
        F.ColumnTypeNative := Trim(Stmt.ColumnUTF8(6));
        F.ColumnLength := Stmt.ColumnInt(7);
        F.ColumnScale := Stmt.ColumnInt(8);
        F.ColumnPrecision := Stmt.ColumnInt(9);
        F.ColumnType:= ODBCColumnToFieldType(DataType,F.ColumnPrecision,F.ColumnScale);
        FA.Add(F);
      until not Stmt.Step;
  ...
end;

Tested against Oracle, MS SQL Server and Informix.

Esteban.

ab · 2015-11-19 20:51:11

Patched as http://synopse.info/fossil/info/dbd2b6cf12

EMartin · 2015-11-20 10:18:10

Hi @ab, the bug correction in SynDBODBC.pas (thread #6) it has not been applied, without this modification the SQLGetProcedureParameters return an empty dataset when querying on Informix stored procedure parameters.

Thanks.

Esteban

ab · 2015-11-20 12:35:33

I sent the patch before seeing your #6 post.

Please try http://synopse.info/fossil/info/2a0a34c5bc

EMartin · 2015-11-20 13:35:05

Sorry @ab, but the main correction in SynDBODBC.pas is on:

bug code:

procedure TODBCConnectionProperties.GetProcedureParameters(const aProcName: RawUTF8;
  out Parameters: TSQLDBProcColumnDefineDynArray);
...
  if Stmt.fCurrentRow>0 then // Step done above
  repeat
...
end;

ok code:

procedure TODBCConnectionProperties.GetProcedureParameters(const aProcName: RawUTF8;
  out Parameters: TSQLDBProcColumnDefineDynArray);
...
  if Stmt.fCurrentRow=0 then // Step was not called
    Stmt.Step;
  repeat
...
end;

Thanks.

ab · 2015-11-20 13:58:57

The Step is done above - see the whole post.

if status=SQL_SUCCESS then
          Stmt.Step;

EMartin · 2015-11-20 14:51:47

Its not working but thanks to this I found the real bug (mine), this works with your modifications:

The correction was add the ODBC.Check(...) and BindColumns, after each SQLProcedureColumnsA.

The correct code to put in SynODBC.pas:

procedure TODBCConnectionProperties.GetProcedureParameters(const aProcName: RawUTF8;
  out Parameters: TSQLDBProcColumnDefineDynArray);
var Schema, Package, Proc: RawUTF8;
    P: TSQLDBProcColumnDefine;
    PA: TDynArray;
    n,DataType: integer;
    status: SqlReturn;
    Stmt: TODBCStatement;
begin
  inherited; // first try from SQL, if any (faster)
  if Parameters<>nil then
    exit; // already retrieved directly from engine
  SQLSplitProcedureName(aProcName,Schema,Package,Proc);
  Proc := SynCommons.UpperCase(Proc);
  Package := SynCommons.UpperCase(Package);
  Schema := SynCommons.UpperCase(Schema);
  if (Package <> '') then
    Proc := Package + '.' + Proc;
  try
    // get column definitions
    Stmt := TODBCStatement.Create(MainConnection);
    try
      Stmt.AllocStatement;
      status := ODBC.SQLProcedureColumnsA(Stmt.fStatement,nil,0,
        pointer(Schema),SQL_NTS,pointer(Proc),SQL_NTS,nil,0);
      ODBC.Check(Stmt.Connection,nil,status,SQL_HANDLE_STMT,Stmt.fStatement);
      Stmt.BindColumns;
      if (status<>SQL_SUCCESS) or (not Stmt.Step) then begin
        // e.g. driver does not support schema
        Stmt.DeallocStatement;
        Stmt.AllocStatement;
        status := ODBC.SQLProcedureColumnsA(Stmt.fStatement,nil,0,
          nil,0,pointer(Proc),SQL_NTS,nil,0);
        ODBC.Check(Stmt.Connection,nil,status,SQL_HANDLE_STMT,Stmt.fStatement);
        Stmt.BindColumns;
        if status=SQL_SUCCESS then
          Stmt.Step;
      end;
      PA.Init(TypeInfo(TSQLDBColumnDefineDynArray),Parameters,@n);
      fillchar(P,sizeof(P),0);
      if Stmt.fCurrentRow>0 then // Step done above
      repeat
        P.ColumnName := Trim(Stmt.ColumnUTF8(3)); // Column*() should be in order
        case Stmt.ColumnInt(4) of
          SQL_PARAM_INPUT:        P.ColumnParamType := paramIn;
          SQL_PARAM_INPUT_OUTPUT: P.ColumnParamType := paramInOut;
          else                    P.ColumnParamType := paramOut;
        end;
        DataType := Stmt.ColumnInt(5);
        P.ColumnTypeNative := Trim(Stmt.ColumnUTF8(6));
        P.ColumnLength := Stmt.ColumnInt(7);
        P.ColumnScale := Stmt.ColumnInt(8);
        P.ColumnPrecision := Stmt.ColumnInt(9);
        P.ColumnType:= ODBCColumnToFieldType(DataType,P.ColumnPrecision,P.ColumnScale);
        PA.Add(P);
      until not Stmt.Step;
      SetLength(Parameters,n);
    finally
      Stmt.Free; // TODBCStatement release
    end;
  except
    on Exception do
      Parameters := nil;
  end;
end;

Thanks.

Last edited by EMartin (2015-11-20 15:08:11)

ab · 2015-11-20 15:31:07

Now I think I get it!

I've made another fix for TODBCConnectionProperties.GetFields and GetProcedureParameters methods.
See http://synopse.info/fossil/info/5fdc9eae5c

EMartin · 2015-11-20 15:50:31

This works !!

A question, why after the first execution of SQLColumnsA/SQLProcedureColumnsA ODB.Check is not executed ? Just for curiosity.

Thanks.

ab · 2015-11-20 17:49:11

Because Status = SQL_SUCCESS so ODBC.Check() is a non-op.

itSDS · 2015-11-24 19:00:11

cause of Informix - addon SynDBFireDAC cannot be compiled:

I changed code in Line 209

const
  /// FireDAC DriverID values corresponding to SynDB recognized SQL engines
  FIREDAC_PROVIDER: array[dOracle..high(TSQLDBDefinition)] of RawUTF8 = (
    'Ora','MSSQL','MSAcc','MySQL','SQLite','IB','','PG','DB2','');

EMartin · 2015-11-24 20:21:42

and SynDBUniDAC in line 190:

const
  /// UniDAC provider names corresponding to SynDB recognized SQL engines
  UNIDAC_PROVIDER: array[dOracle..high(TSQLDBDefinition)] of RawUTF8 = (
    'Oracle','SQL Server','Access','MySQL','SQLite','InterBase',
    'NexusDB','PostgreSQL','DB2','');

I don't use FireDAC or UniDAC.

ab · 2015-11-27 10:51:12

Should be fixed by http://synopse.info/fossil/info/38420e284a

Thanks for the feedback.

EMartin · 2015-12-09 14:32:53

Hi @ab, I made corrections to SynDB.pas over 2143 revision.

https://drive.google.com/open?id=0Bx7LP … mJuWHVCckE

Can you update this ?

Thanks.

ab · 2015-12-09 15:33:02

Should be included within http://synopse.info/fossil/info/3b61badd2c

Thanks for the feedback!

EMartin · 2016-01-25 19:49:05

Hi @ab, I have these modifications:

SynDB.pas
- added GetProcedureNames and SQLGetProcedure for listing stored procedure names from current connection
- addes GetViewNames and SQLGetViewNames for listing view names from current connection
- bug fix getting stored procedure parameters on Firebird 3
- small refactoring in TSQLDBConnectionProperties.ExceptionIsAboutConnection
- added support for dInformix and dMSSQL in TSQLDBConnectionProperties.ExceptionIsAboutConnection
- added error codes in TSQLDBConnectionProperties.ExceptionIsAboutConnection for dOracle
- avoid GPI in TSQLDBConnection.GetLastErrorWasAboutConnection when fErrorMessage is empty

SynDBODBC.pas
- added GetProcedureNames for listing stored procedure names from current connection
- addes GetViewNames and SQLGetViewNames for listing view names from current connection
- added ODBCInstalledDriversList for listing installed ODBC drivers (not implemented for Linux)
- overrided GetDatabaseNameSafe over ODBC connection string

TODO:
- ODBCInstalledDriversList implementation for Linux pending ... I don't know Linux (emartin)

https://drive.google.com/open?id=0Bx7LP … mhyVm5lZG8

If you are agree, can you apply these changes ?

Thanks.

Esteban.

ab · 2016-01-26 10:45:30

I've one doubt about ODBCInstalledDriversList().
AFAIK the list you are retrieving for a Win64 executable (CPU64 defined) is the list of 32-bit ODBC drivers.
I guess this is not what we expect.
Perhaps we do not need to search in WOW6432Node.

I've included (with some fixes) your patches as http://synopse.info/fossil/info/fce4366fc8 and http://synopse.info/fossil/info/929025f385
Thanks a lot for the feedback!

EMartin · 2016-01-26 11:04:33

You are right !!! My app is Win32 and I lose the point of view

Thanks.

Esteban

ab · 2016-01-26 13:23:43

I got rid of unneeded WOW6432Node reference for ODBCInstalledDriversList() function.

See http://synopse.info/fossil/info/9d2aeee5b8

mORMot Open Source

#1 2015-11-19 14:54:51

Informix support contribution

#2 2015-11-19 17:03:50

Re: Informix support contribution

#3 2015-11-19 18:23:10

Re: Informix support contribution

#4 2015-11-19 19:23:57

Re: Informix support contribution

#5 2015-11-19 20:20:15

Re: Informix support contribution

#6 2015-11-19 20:40:54

Re: Informix support contribution

#7 2015-11-19 20:51:11

Re: Informix support contribution

#8 2015-11-20 10:18:10

Re: Informix support contribution

#9 2015-11-20 12:35:33

Re: Informix support contribution

#10 2015-11-20 13:35:05

Re: Informix support contribution

#11 2015-11-20 13:58:57

Re: Informix support contribution

#12 2015-11-20 14:51:47

Re: Informix support contribution

#13 2015-11-20 15:31:07

Re: Informix support contribution

#14 2015-11-20 15:50:31

Re: Informix support contribution

#15 2015-11-20 17:49:11

Re: Informix support contribution

#16 2015-11-24 19:00:11

Re: Informix support contribution

#17 2015-11-24 20:21:42

Re: Informix support contribution

#18 2015-11-27 10:51:12

Re: Informix support contribution

#19 2015-12-09 14:32:53

Re: Informix support contribution

#20 2015-12-09 15:33:02

Re: Informix support contribution

#21 2016-01-25 19:49:05

Re: Informix support contribution

#22 2016-01-26 10:45:30

Re: Informix support contribution

#23 2016-01-26 11:04:33

Re: Informix support contribution

#24 2016-01-26 13:23:43

Re: Informix support contribution

Board footer