Bind variable in SELECT column list?

[Dave Rabelink]

After testing TD 7.4.1 I have seen one particular error within the gazillion SQL statements in the code.

Here the example (the actual statement is different but the example here is just to explain the usage):

Code: Select all

SELECT	product_key, product_name, :sPRODUCT_COL_DESCR
FROM	products
INTO	:nProductKey, :sProductName, :sProductDescription

As you see, there is a bind variable in the column list (sPRODUCT_COL_DESCR).
I was surprised to find this implementation which is very old code.
To my knowledge this is wrong indeed. For as I know you can not use bind variables in this way.

It seems it was implemented to have a column name defined in a string variable which is set before the execution.
So the query could get the product description from different columns based on business logic.

This never got an SQL error before as this is used for years in previous TD versions. But now TD 7.4.1 throws an SQL error.

So, am I wrong in thinking you can not use binds in the column list?
And if it is allowed, then TD 7.4.1 does not accept in anymore.

I adapted the code so that the column name is now set in the SQL statement itself without a bind to fix the error.

Any comments on this?

(we are using Oracle by the way)

[FRBhote]

XX

[Harald]

That would be a show stopper for many applications. For example, bind variables are often used in a "insert as select" statement:

insert into tablename (id, field1, ...)
as select coalesce(max(id) +1, 1), :MyField, ... from tablename

If that does not longer work, it's a serious defect.

Kind regards
Harald

[a_sivababu]

I feel this is commonly used in old centrua coding. it should ideally work as it simply replace the value or expression in Select list through their coding and pass it on to DB compiler to compile and execute it. I think it should continue to support.

just to touch on other issues though it is not relevant to this ticket.
I also facing some strange issues in SQL Server 17 ODBC driver connection. I generally keep commit statement at the end of all important population and transaction but now, I should give the commit immediately after the any DML statements as the system is hanging if I do any select statement before commit. This issue is coming if you migrate the code from old version to new version.

There are other minor issues in TD 7.4.1 but can be managed with work around.

Thanks!
Siva A

[Dave Rabelink]

Thanks for the feedback.

So to investigate further why I get the error on TD 7.4.1, I tried to mimic the table structure (datatypes) and the SQL statement used and put that in a sample.
Also tested some SQL statements which are mentioned in this topic.

My conclusion is: the "normal" usage of binds in columns seem still to work in TD 7.4.1.
I did tests on TD 7.3.3 which does not fail. But running the test on TD 7.3.6 it shows the same error as in TD 7.4.1.
(So it could be broken somewhere between 7.3.3 and 7.4.1)

But in the specific situation here we use, I think, a special case:

- The column is a varchar2(2000 BYTE)
- The SQL statement is an UNION

So what is the case:

Multiple tables are joined (UNIONS) where only one table has a column of type varchar2(2000 BYTE).
The other tables do not have this column. So the UNION needs to specify the missing column in the SELECT statement.

I tried to create a sample application which is not the exact table structure and data we use here, but is using a similar construction.

This is the SQL statement in the sample application:

Code: Select all

SELECT  ID, 1000 AS CODE, VALUE, DESCRIPTION
FROM    SYSADM.TST_BINDCOL1

UNION

SELECT  ID, 1001 AS CODE, VALUE, :lsMyLongData
FROM    SYSADM.TST_BINDCOL2

INTO :nID, :nCode, :sValue, :lsDescription
ORDER BY ID, CODE

As can be seen, the column DESCRIPTION is of type varchar2(2000 BYTE) and is only available in table TST_BINDCOL1.
The second table which is joined has a bind variable at the place where the description should be.
The bind variable lsMyLongData is of TD type Long String and has no value (is empty).

Running the sample in TD 7.3.3 gives:

BindAsCol_TD733.png

The error presented when using TD 7.4.1 (and TD 7.3.6):

BindAsCol_SqlError.png

Here the sample:

BindVarAsColumn.zip

(Change the database settings in the Init function of the form to use your own database/user/password.
The button "Create table" will create the tables and insert some demo data.
The button "Select data" will execute the query and populate the child table on the form.)



I wonder if I need to report this to OpenText Gupta as it was solved by using "" in the select statement instead of using the bind variable.