Amazon Redshift and cutted name in ODBC

[mariot]

Hi,

I connect to Amazon Redshift throught ODBC (using official Amazon Redshift ODBC driver), but table names (and other object names) in query are cutted to 63 chars. Amazon Redshift support names with 127 chars.

Do you can confirm this problem with UniDAC or it is problem in ODBC driver?

Example of table with long name:
create table tab4567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 atr4567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567 int NOT NULL);

Query:
SELECT c.relname FROM pg_catalog.pg_class c WHERE c.relkind = 'r' and c.relname like 'tab456789%'

Thanks,
Mario Tkacik

[mariot]

I discover, that following query (with using CAST function) return names with correct length:
SELECT CAST(c.relname as char(127)) FROM pg_catalog.pg_class c WHERE c.relkind = 'r' and c.relname like 'tab456789%'
But that's only workaround.

Note: I tested it in UniDAC version 7.0.2.

[azyk]

Thank you for the information. Currently, we are working on this issue fix and will let you know the results.

[azyk]

The error cause is not UniDAC, but a bug in the official Amazon Redshift ODBC Driver. We can send you a night build of our Devart ODBC Driver for Amazon Redshift, in which this error is already fixed. To get it, write us using the contact form at our site: https://www.devart.com/company/contactform.html

[mariot]

I asked in Amazon forum and somebody respond that in Python ODBC client it is OK:

Which client are you using with the ODBC driver? This may be an issue with the specific client rather than the driver.

For example, here is very simple Python ODBC client
import pyodbc
import sys
from sys import argv

sql = argv[1]
cnxn = pyodbc.connect('DSN=cluster-name;PWD=MyP4ssw0rd;UID=username')
cursor = cnxn.cursor()
cursor.execute(sql)
while 1:
row = cursor.fetchone()
if not row:
break
rowstring=str(row)
first=True
for element in row:
if first:
first=False
else:
sys.stdout.write(',')
sys.stdout.write(str(element))
sys.stdout.write('\n')

Using this I receive the full table name to 127 characters.
$ python ~/pyodbc_test.py "SELECT c.relname FROM pg_catalog.pg_class c WHERE c.relkind = 'r' and c.relname like 'tab456789%';"
tab4567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
python ~/pyodbc_test.py "SELECT * FROM svv_table_info s WHERE s.table like 'tab456789%';"
harrjose,public,418332,tab4567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567,Y,EVEN,None,0,None,0,4,0.0010,0,None,99.90,1,None,None

[MaximG]

As we wrote earlier, this behavior is not related to UniDAC. It refers to the features of Amazon Redshift ODBC Driver. The UniQuery component inherits from TDataSet. To get the correct data a query returns, it is necessary to obtain metadata from the server for each field (a field type and a size of the returned data).
Despite the fact that Amazon Redshift allows you to specify the names of database objects up to 127 characters long, Amazon Redshift ODBC Driver returns the size of 63 characters for the relname field when receiving metadata. Unfortunately, we cannot influence this behavior.
Try to contact the technical support of Amazon Redshift ODBC Driver. Also, you can try using a full-featured trial versio of Devart ODBC Driver for Amazon Redshift at https://www.devart.com/odbc/redshift/download.html