I’ve gone through multiple web posts and this mail archive and I’m experiencing
similar problems with Sybase ASE which other users have had with Microsoft SQL
Server..
Both products use Transact SQL which introduces the concept of a schema (or
user) owned tables.
I can get use, show tables and describe working, but SQL statements fail
because I don’t think the SQL parser understands the schema / user dot
notation..
Here’s my setup and test scripts:
A. Storage Plugin named SYBASE:
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server_address:4100/tempdb",
"username": "my_login",
"password": "my_password",
"enabled": true
}
B. Sybase SQL Script executed in ISQL to create test tables:
use tempdb
go
create table phone_book
(
first_name varchar(10),
last_name varchar(20),
phone_number varchar(12)
)
go
insert phone_book values ('Bob','Marley','555-555-5555')
insert phone_book values ('Mary','Jane','111-111-1111')
insert phone_book values ('Bat','Man','911-911-9999')
go
C. From Apache Drill here’s my output:
INFO: Initiating Jersey application, version Jersey
apache drill 1.8.0
"drill baby drill"
0: jdbc:drill:zk=local> use SYBASE.tempdb;
+-------+------------------------------------------
| ok | summary
+-------+------------------------------------------
| true | Default schema changed to [SYBASE.tempdb]
+-------+------------------------------------------
1 row selected (5.938 seconds)
0: jdbc:drill:zk=local> show tables;
+----------------+------------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+----------------+------------------------------+
| SYBASE.tempdb | phone_book |
| SYBASE.tempdb | sysalternates |
| SYBASE.tempdb | sysattributes |
| SYBASE.tempdb | syscolumns |
| SYBASE.tempdb | syscomments |
| SYBASE.tempdb | sysconstraints |
etc.. etc.. etc..
+----------------+------------------------------+
42 rows selected (7.037 seconds)
0: jdbc:drill:zk=local> describe phone_book;
+---------------+--------------------+--------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+---------------+--------------------+--------------+
| first_name | CHARACTER VARYING | NO |
| last_name | CHARACTER VARYING | NO |
| phone_number | CHARACTER VARYING | NO |
+---------------+--------------------+--------------+
3 rows selected (4.901 seconds)
0: jdbc:drill:zk=local> select * from phone_book;
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the
SQL query.
sql SELECT *
FROM "tempdb"."phone_book"
plugin SYBASE
Fragment 0:0
[Error Id: db3ae9fe-1214-4590-bef0-cc299c9871d9 on xxxx.xxx.xxxxx.com:31010]
(state=,code=0)
D. My connection and plug is fine, but I can’t get any SQL to work.. I’ve
tried all of the following SQL statements which work in ISQL, but are not valid
in DRILL:
ISQL:
select * from phone_book
select * from tempdb..phone_book
select * from tempdb.guest.phone_book
DRILL:
select * from phone_book;
select * from SYBASE.tempdb..phone_book;
select * from SYBASE.tempdb.guest.phone_book;
Anyone have any solutions before I start digging through the github for Drill
and Calcite??
David Lee
Vice President | BlackRock
Phone: +1.415.670.2744 | Mobile: +1.415.706.6874
This message may contain information that is confidential or privileged. If you
are not the intended recipient, please advise the sender immediately and delete
this message. See
http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for
further information. Please refer to
http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more
information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see
http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.
© 2016 BlackRock, Inc. All rights reserved.
