.net crystal reports 13 ODBC PostgreSQL
Hi!
We use Crystal Reports. Initially built for MS SQL database on OLEDBSQL driver. Data is obtained primarily through stored procedures. They were recently transferred to the MSOLEDBSQL driver by replacing the used driver at runtime ("Provider" property: "MSOLEDBSQL", etc.), the rpt reports themselves did not correct. Works great.
Now we need to transfer part of the reports to PostgreSQL. ODBC was chosen as the driver. And here we ran into a problem.
What succeeded:
- At runtime, change the driver to ODBC ("Connection String" property: $"PROVIDER=MSDASQL; DRIVER={<!-- -->{PostgreSQL Unicode}}; SERVER={server}; DATABASE={database};", "Database DLL": " crdb_odbc.dll").
Primitive reports have earned. - In the designer, specify a new location for stored procedures on PostgreSQL (technically, of course, this is a function).
Although this moment is optional. We planned to make all the changes in the code, without editing the rpt files.
Problem. When crystal sees that a PosgreSQL function/SP is specified, it categorically marks all parameters (@) as NOT NULL (EnableNullValue = False). In a PostgreSQL function, the parameters are marked as nullable. Any attempts to pass Nothing/DbNull.Value end with errors like:
Setpoint and current value types do not match.
or
System.Runtime.InteropServices.COMException
HResult=0x80042009
Message = The types of the parameter field and the current values of the parameter field are not compatible.
If you pass any values (for example, an empty string), then the report starts working.
Question - how to make Crystal work with PostgreSQL functions and NULL parameters?