SELECT NAME FROM xxx WHERE xxx UNION ALL SELECT 'BOB' FROM xxx WHERE xxx Results in an OLE DB data type of LONGVARCHAR instead of the VARCHAR(50) for the column NAME Is there anyway to correct this in the database without affecting the sql statement as it runs against other databases and there are many other instances of this type of code. EDIT: C# code that demonstrates the problem string queryString = @" select parent_account_id from chart_of_accounts union all select 'Bob' from chart_of_accounts"; using (OleDbConnection connection = new OleDbConnection("Provider=SAOLEDB.12;UID=xxx;PWD=xxx;BF=xxx;Persist Security Info=True;")) { OleDbCommand command = connection.CreateCommand(); command.CommandText = queryString; try { connection.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { // break here and was able to view the internal schema structure which indicated LONGVARCHAR Console.WriteLine("\t{0}\t{1}", reader[0].ToString(), reader[1]); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.ReadKey(); } } |
|
I don't think this is a general problem - the result type of a column in a UNION query should be derived in a reasonable way from the column types of the underlying query blocks, and a string literal will be specified with its minimal type - which can be inspected with the EXPRTYPE function: select exprtype('SELECT ''BOB''', 1) returns "char(3)" in my case. So, with a sample query against the system catalog, 'BOB' is converted in a meaningful way: SELECT EXPRTYPE( 'SELECT table_name FROM systable WHERE table_id = 1 UNION ALL SELECT ''BOB''', 1) returns "char(128)" - which is the type of systable.table_name. And the similar query against another column of that table SELECT EXPRTYPE( 'SELECT view_def FROM systable WHERE table_id = 1 UNION ALL SELECT ''BOB''', 1) returns "long varchar" - which is the type of systable.view_def. Therefore, I would think that
Thanks for replying. The type of parent_account_id is varchar(50) and if in both select statements I have parent_account_id the type returned is SQL_VARCHAR. I ran that second exprtype using the my the union statement above and it returns a LONG VARCHAR.
(02 Mar '12, 10:41)
Chris_Dynac
Replies hidden
So...Apparently I was wrong about the data type I am using. The column is NVARCHAR(80) not VARCHAR(80). The exprType returns varchar(80) when the column data type is a varchar but long varchar when the data type if nvarchar. Go figure.
(02 Mar '12, 10:49)
Chris_Dynac
Replies hidden
Does this happen, too, when you run the query in DBSIQL (or dbisqlc)? What v12 build are you using? (I've tested with 12.0.1.3554.)
(02 Mar '12, 10:52)
Volker Barth
Hm, so it seems to be due to mixing CHAR and NCHAR. However, when I use the sample queries above with the NCHAR string literal "N'BOB'" instead of the CHAR string literal "BOB", I get LONG NVARCHAR as type - which seems to be the typical type returned by functions like UNISTR... So using a NCHAR string literal might help.
(02 Mar '12, 11:20)
Volker Barth
|
you can try to cast('bob' as varchar(50))