Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

FbConnection.GetSchema getting column information does not bring the column_size on varchar columns [DNET861] #792

Closed
firebird-automations opened this issue Jan 7, 2019 · 15 comments

Comments

@firebird-automations
Copy link

Submitted by: Fabricio Araujo (fabricioarj)

Attachments:
FbConn_BugViews.zip
FbErrorColumn_Views.zip

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.

Commits: 0811406

@firebird-automations
Copy link
Author

Modified by: Fabricio Araujo (fabricioarj)

environment: Visual Studio 2017
http://ASP.NET Core
NET Standard libraries

=>

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

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Can you provide a small code to reproduce this...

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: Fabricio Araujo (fabricioarj)

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.

@firebird-automations
Copy link
Author

Modified by: Fabricio Araujo (fabricioarj)

Attachment: FbConn_BugViews.zip [ 13316 ]

@firebird-automations
Copy link
Author

Modified by: Fabricio Araujo (fabricioarj)

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.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

The attachment does not contain any sources nor executable (which would be useless anyway).

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 6.5.0.0 [ 10887 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Closed [ 6 ]

resolution: Incomplete [ 4 ]

Fix Version: 6.5.0.0 [ 10887 ] =>

@firebird-automations
Copy link
Author

Commented by: Fabricio Araujo (fabricioarj)

Can I upload the source or I have to clone the issue?

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Upload here. No need to create another ticket.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Incomplete [ 4 ] =>

@firebird-automations
Copy link
Author

Commented by: Fabricio Araujo (fabricioarj)

Source to demonstrate the datatable resulting from the GetSchema call

@firebird-automations
Copy link
Author

Modified by: Fabricio Araujo (fabricioarj)

Attachment: FbErrorColumn_Views.zip [ 13320 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 6.5.0.0 [ 10887 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants