Hi Paul, Thanks for your detailed information. Out of 3 approaches mentioned above we have already tried 3rd one and it is still the same error. I will keep on looking for other two options and will let you know any updates. thx
Regards Prabhakar On Tue, Mar 31, 2020 at 3:48 AM Paul Rogers <par0...@yahoo.com.invalid> wrote: > Hi Prabhakar, > > Not being much of a JDBC expert, I did some poking around. It seems that > Drill's open-source JDBC driver is based on Apache Calcite's Avatica > framework. Avatica does not appear include JDBC DataSource support, it is > just a simple, basic JDBC driver. > > My attempts to Google how to use such a basic Driver with Websphere did > not produce many results. I found the click-this, type-that instructions > (from 2007!) but did not see anything about how to handle a basic driver. > > So, seems that there several approaches: > > 1. Extend the Drill JDBC driver to include DataSource support. > 2. Find a Websphere or third-party solution to wrap "plain" JDBC drivers. > 3. Try MapR's commercial JDBC driver created by Simba. [1] Looks like this > driver works on Windows. The documentation [2] does not list DataSource > support, however. > > > Contributions are welcome to solve item 1. You are probably more of a WS > expert than I, so perhaps you can research item 2. You can also check > whether the MapR Driver give you what you need. > > Also, if any others out there have more JDBC experience, it would be great > if someone could add a bit more context. For example, how is this issue > handled for other JDBC drivers? What would it take for Drill to add > DataSource support? > > > Thanks, > - Paul > [1] https://mapr.com/docs/61/Drill/drill_odbc_connector.html > [2] > https://mapr.com/docs/61/attachments/JDBC_ODBC_drivers/DrillODBCInstallandConfigurationGuide.pdf > > > > On Sunday, March 29, 2020, 9:26:52 PM PDT, Prabhakar Bhosaale < > bhosale....@gmail.com> wrote: > > Hi Paul, > > Any further inputs on JDBC driver for drill? thx > > Regards > Prabhakar > > On Thu, Mar 26, 2020 at 1:25 PM Prabhakar Bhosaale <bhosale....@gmail.com> > wrote: > > > Hi Paul, > > Please see my answers inline below > > > > Drill is supported on Windows only in embedded mode; we have no scripts > to > > run a server. Were you able to create your own solution? > > Prabhakar: We are using drill on windows only in embedded mode > > > > The exception appears to indicate that the Drill JDBC connection is being > > used inside a transaction, perhaps with other data sources, so a > two-phase > > commit is needed. However, Drill does not support transactions as > > transactions don't make sense for data sources such as HDFS or S3. > > > > > > Is there a way to configure WAS to use Drill just for read-only access > > without transactions? See this link: [1]. To quote: > > > > Non-transactional data source > > Specifies that the application server does not enlist the connections > from > > this data source in global or local transactions. Applications must > > explicitly call setAutoCommit(false) on the connection if they want to > > start a local transaction on the connection, and they must commit or roll > > back the transaction that they started. > > > > Prabhakar: I tried making the datasource as non-transactional data > source. > > But still it gave same error > > > > Can you run a test? Will SQLLine connect to your Drill server? If so, > then > > you know that you have the host name correct, that the ports are open, > and > > that Drill runs well enough on Windows for your needs. > > > > Prabhakar: I tried connecting drill using squirrel and it connected > > successfully to drill. Even we tried simple java code using this driver > > class and it successfully retrieved the data. So drill with its port and > > host is working fine. > > > > > > Our understanding is that webphere is expecting any JDBC driver to > > implement the javax.sql.ConnectionPoolDataSource class, But in drill > driver > > we are not sure whether this is implemented. > > > > Please refer > > > https://www.ibm.com/mysupport/s/question/0D50z000062kMU2CAM/classcastexception-comibmoptimconnectjdbcnvdriver-incompatible-with-javaxsqlconnectionpooldatasource?language=en_US > > > > Any help in this regard is highly appreciated. thx > > > > REgards > > Prabhakar > > > > On Thu, Mar 26, 2020 at 10:52 AM Paul Rogers <par0...@yahoo.com.invalid> > > wrote: > > > >> Hi Prabhakar, > >> > >> Drill is supported on Windows only in embedded mode; we have no scripts > >> to run a server. Were you able to create your own solution? > >> > >> The exception appears to indicate that the Drill JDBC connection is > being > >> used inside a transaction, perhaps with other data sources, so a > two-phase > >> commit is needed. However, Drill does not support transactions as > >> transactions don't make sense for data sources such as HDFS or S3. > >> > >> > >> Is there a way to configure WAS to use Drill just for read-only access > >> without transactions? See this link: [1]. To quote: > >> > >> Non-transactional data source > >> Specifies that the application server does not enlist the connections > >> from this data source in global or local transactions. Applications must > >> explicitly call setAutoCommit(false) on the connection if they want to > >> start a local transaction on the connection, and they must commit or > roll > >> back the transaction that they started. > >> > >> Can you run a test? Will SQLLine connect to your Drill server? If so, > >> then you know that you have the host name correct, that the ports are > open, > >> and that Drill runs well enough on Windows for your needs. > >> > >> By the way, the Apache mail agent does not support attachments. Can you > >> post the log somewhere else? Or, just past into an e-mail the lines > around > >> the failure. > >> > >> Thanks, > >> - Paul > >> > >> > >> [1] > >> > https://www.ibm.com/support/knowledgecenter/en/SSAW57_8.5.5/com.ibm.websphere.nd.multiplatform.doc/ae/udat_jdbcdatasorprops.html > >> > >> > >> > >> > >> On Wednesday, March 25, 2020, 8:53:31 PM PDT, Prabhakar Bhosaale < > >> bhosale....@gmail.com> wrote: > >> > >> Hi Charles, > >> > >> Thanks for the reply. The dril version is 1.16 and JDBC version is also > >> same. The drill is installed on windows in standalone mode. > >> > >> The challenge here is that, when we created the data provider and data > >> source on WAS, we have not given any hostname or port details of drill > >> server, so when test connection happens on WAS server, it is actually > not > >> connecting to drill. > >> > >> Please let me know if you need any additional information. Once again > >> thanks for your help > >> > >> Regards > >> Prabhakar > >> > >> On Tue, Mar 24, 2020 at 6:19 PM Charles Givre <cgi...@gmail.com> wrote: > >> > >> > HI Prabhakar, > >> > Thanks for your interest in Drill. Can you share your config info as > >> well > >> > as the versions of Drill and JDBC Driver that you are using? > >> > Thanks, > >> > -- C > >> > > >> > > >> > > On Mar 24, 2020, at 7:07 AM, Prabhakar Bhosaale < > >> bhosale....@gmail.com> > >> > wrote: > >> > > > >> > > Hi Team, > >> > > > >> > > we are trying to connect to apache drill from websphere 8.5.5.9. We > >> > created the the Data provider and data source as per standard process > of > >> > WAS. But when we try to test the connection, it gives following > error. > >> > > > >> > > "Test connection operation failed for data source retrievalds on > >> server > >> > ARCHIVE_SERVER at node ARCHIVALPROFILENode1 with the following > >> exception: > >> > java.lang.Exception: DSRA8101E: DataSource class cannot be used as > >> > one-phase: ClassCastException: org.apache.drill.jdbc.Driver > incompatible > >> > with javax.sql.ConnectionPoolDataSource " > >> > > > >> > > We are using SDK version 1.8 > >> > > Attaching the JVM log also for your reference. thx > >> > > > >> > > Any pointers or any documentation in this regards would be > >> appreciated. > >> > Please help. thx > >> > > > >> > > Regards > >> > > Prabhakar > >> > > <JVM_Error_Log.txt> > >> > > >> > > >> > > > > >
