Issue Details (XML | Word | Printable)

Key: DNET-861
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Fabricio Araujo
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

FbConnection.GetSchema getting column information does not bring the column_size on varchar columns

Created: 07/Jan/19 03:19 AM   Updated: 14/Feb/19 08:33 AM
Component/s: ADO.NET Provider
Affects Version/s: 6.3.0.0, 6.4.0.0
Fix Version/s: 6.5.0.0

File Attachments: 1. Zip Archive FbConn_BugViews.zip (251 kB)
2. Zip Archive FbErrorColumn_Views.zip (1.65 MB)

Environment:
Visual Studio 2017
ASP.NET Core
NET Standard libraries
Firebird 2.5


 Description  « Hide
I have my own way to generate SQL to use with Dapper (Dapper.Contrib does not fit well
in what I'm doing) and I need to get column information to do so.

When I'm working with database tables directly, all works wonders when I use this call:
DataTable dtb = conn.GetSchema("COLUMNS", new string[] { null, null, ParamTableName });

When I use a view, things gets weird. Views (is not a trivial view, it has a few joins) with varchar columns comes with COLUMN_TYPE correct but COLUMN_SIZE comes NULL.
I traced the executed SQL of GetSchema but while it does not bring the type of the column (only the reference to the domain) it DOES BRING
the COLUMN_SIZE correctly

The other columns comes correctly (all those are INTEGER columns though).

Create the following view on the provided employee.fdb:

CREATE OR ALTER VIEW PHONE_LIST_EXAMPLE(
    EMP_NO,
    FIRST_NAME,
    LAST_NAME,
    PHONE_EXT,
    HIRE_DATE,
    DEPT_NO,
    JOB_CODE,
    JOB_GRADE,
    JOB_COUNTRY,
    SALARY,
    FULL_NAME,
    PHONE_NO)
AS
WITH CtePhoneList as (
SELECT
  E.Emp_No,
  E.First_Name,
  E.Last_Name,
  E.Phone_Ext,
  E.Hire_Date,
  E.Dept_No,
  E.Job_Code,
  E.Job_Grade,
  E.Job_Country,
  E.Salary,
  E.Full_Name ,
  d.Phone_No

FROM
  Employee E
  inner join Department d
    on e.Dept_No = d.Dept_No
)

Select
  Emp_No,
  First_Name,
  Last_Name,
  Phone_Ext,
  Hire_Date,
  Dept_No,
  Job_Code,
  Job_Grade,
  Job_Country,
  Salary,
  Full_Name ,
  Phone_No

from
  CtePhoneList;

And build the provided winforms application to get the column info from that view. If you try on the original phone list view, all column will come ok.
If you run the application on THAT view you'll see that varchar columns will come without column size.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Fabricio Araujo made changes - 07/Jan/19 03:21 AM
Field Original Value New Value
Environment Visual Studio 2017
ASP.NET Core
NET Standard libraries
Visual Studio 2017
ASP.NET Core
NET Standard libraries
Firebird 2.5
Jiri Cincura added a comment - 15/Jan/19 01:29 PM
Can you provide a small code to reproduce this...

Jiri Cincura made changes - 15/Jan/19 01:29 PM
Status Open [ 1 ] In Progress [ 3 ]
Fabricio Araujo added a comment - 16/Jan/19 01:14 AM
Create the following view on the provided employee.fdb:

CREATE OR ALTER VIEW PHONE_LIST_EXAMPLE(
    EMP_NO,
    FIRST_NAME,
    LAST_NAME,
    PHONE_EXT,
    HIRE_DATE,
    DEPT_NO,
    JOB_CODE,
    JOB_GRADE,
    JOB_COUNTRY,
    SALARY,
    FULL_NAME,
    PHONE_NO)
AS
WITH CtePhoneList as (
SELECT
  E.Emp_No,
  E.First_Name,
  E.Last_Name,
  E.Phone_Ext,
  E.Hire_Date,
  E.Dept_No,
  E.Job_Code,
  E.Job_Grade,
  E.Job_Country,
  E.Salary,
  E.Full_Name ,
  d.Phone_No

FROM
  Employee E
  inner join Department d
    on e.Dept_No = d.Dept_No
)

Select
  Emp_No,
  First_Name,
  Last_Name,
  Phone_Ext,
  Hire_Date,
  Dept_No,
  Job_Code,
  Job_Grade,
  Job_Country,
  Salary,
  Full_Name ,
  Phone_No

from
  CtePhoneList;

And build the provided winforms application to get the column info from that view. If you try on the original phone list view, all column will come ok.
If you run the application on THAT view you'll see that varchar columns will come without column size.


Fabricio Araujo made changes - 16/Jan/19 01:14 AM
Attachment FbConn_BugViews.zip [ 13316 ]
Fabricio Araujo made changes - 17/Jan/19 07:04 PM
Description I have my own way to generate SQL to use with Dapper (Dapper.Contrib does not fit well
in what I'm doing) and I need to get column information to do so.

When I'm working with database tables directly, all works wonders when I use this call:
DataTable dtb = conn.GetSchema("COLUMNS", new string[] { null, null, ParamTableName });

When I use a view, things gets weird. Views (is not a trivial view, it has a few joins) with varchar columns comes with COLUMN_TYPE correct but COLUMN_SIZE comes NULL.
I traced the executed SQL of GetSchema but while it does not bring the type of the column (only the reference to the domain) it DOES BRING
the COLUMN_SIZE correctly

The other columns comes correctly (all those are INTEGER columns though).
I have my own way to generate SQL to use with Dapper (Dapper.Contrib does not fit well
in what I'm doing) and I need to get column information to do so.

When I'm working with database tables directly, all works wonders when I use this call:
DataTable dtb = conn.GetSchema("COLUMNS", new string[] { null, null, ParamTableName });

When I use a view, things gets weird. Views (is not a trivial view, it has a few joins) with varchar columns comes with COLUMN_TYPE correct but COLUMN_SIZE comes NULL.
I traced the executed SQL of GetSchema but while it does not bring the type of the column (only the reference to the domain) it DOES BRING
the COLUMN_SIZE correctly

The other columns comes correctly (all those are INTEGER columns though).

Create the following view on the provided employee.fdb:

CREATE OR ALTER VIEW PHONE_LIST_EXAMPLE(
    EMP_NO,
    FIRST_NAME,
    LAST_NAME,
    PHONE_EXT,
    HIRE_DATE,
    DEPT_NO,
    JOB_CODE,
    JOB_GRADE,
    JOB_COUNTRY,
    SALARY,
    FULL_NAME,
    PHONE_NO)
AS
WITH CtePhoneList as (
SELECT
  E.Emp_No,
  E.First_Name,
  E.Last_Name,
  E.Phone_Ext,
  E.Hire_Date,
  E.Dept_No,
  E.Job_Code,
  E.Job_Grade,
  E.Job_Country,
  E.Salary,
  E.Full_Name ,
  d.Phone_No

FROM
  Employee E
  inner join Department d
    on e.Dept_No = d.Dept_No
)

Select
  Emp_No,
  First_Name,
  Last_Name,
  Phone_Ext,
  Hire_Date,
  Dept_No,
  Job_Code,
  Job_Grade,
  Job_Country,
  Salary,
  Full_Name ,
  Phone_No

from
  CtePhoneList;

And build the provided winforms application to get the column info from that view. If you try on the original phone list view, all column will come ok.
If you run the application on THAT view you'll see that varchar columns will come without column size.
Jiri Cincura added a comment - 18/Jan/19 10:01 AM
The attachment does not contain any sources nor executable (which would be useless anyway).

Jiri Cincura made changes - 18/Jan/19 04:27 PM
Fix Version/s 6.5.0.0 [ 10887 ]
Jiri Cincura made changes - 22/Jan/19 10:10 AM
Status In Progress [ 3 ] Closed [ 6 ]
Fix Version/s 6.5.0.0 [ 10887 ]
Resolution Incomplete [ 4 ]
Fabricio Araujo added a comment - 24/Jan/19 04:41 PM
Can I upload the source or I have to clone the issue?

Jiri Cincura added a comment - 24/Jan/19 05:10 PM
Upload here. No need to create another ticket.

Jiri Cincura made changes - 24/Jan/19 05:10 PM
Resolution Incomplete [ 4 ]
Status Closed [ 6 ] Reopened [ 4 ]
Fabricio Araujo added a comment - 24/Jan/19 05:20 PM
Source to demonstrate the datatable resulting from the GetSchema call

Fabricio Araujo made changes - 24/Jan/19 05:20 PM
Attachment FbErrorColumn_Views.zip [ 13320 ]
Jiri Cincura made changes - 14/Feb/19 08:33 AM
Status Reopened [ 4 ] Resolved [ 5 ]
Fix Version/s 6.5.0.0 [ 10887 ]
Resolution Fixed [ 1 ]