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();
        }
    }

asked 01 Mar '12, 14:33

Chris_Dynac's gravatar image

Chris_Dynac
21114
accept rate: 0%

edited 02 Mar '12, 07:02

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

you can try to cast('bob' as varchar(50))

(02 Mar '12, 05:39) Martin

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

  • this is bound to the OLEDB interface or
  • the column "parent_account_id" is using a LONG VARCHAR itself, or this is the smallest common type that can contain both values.
permanent link

answered 02 Mar '12, 06:59

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 02 Mar '12, 07:02

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×10

question asked: 01 Mar '12, 14:33

question was seen: 1,516 times

last updated: 02 Mar '12, 11:20