You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by limabean <dr...@gmail.com> on 2018/09/06 13:59:31 UTC
How to create tables with JDBC, read with ODBC?
Scenario:
64-bit ODBC driver cannot read data created from the Java Thin driver.
Ignite 2.6.
Running a single node server on Centos to test this.
First:
Using Intellij to remotely run the sample code from the Ignite Getting
started page here on SQL:
First Ignite SQL Application
https://apacheignite.readme.io/docs/getting-started
This all works fine. Tables created, data inserted, data read. All as
expected.
Next:
Using the ODBC 64-bit driver from Windows 10 to connect to the still running
Ignite server to read the same tables (City, Person). This does not work.
The ODBC driver appears to be able to get meta data - it gets the table
names from the PUBLIC schema and it understands the fields / field counts in
each table. However, the ODBC driver is unable to perform any select
operations on the tables. See the following stack trace as an example of the
errors I am seeing:
[13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
persistenceEnabled=false]
[13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed to
execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed
to parse query. Table not found; SQL statement:
SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
-----------------------
I have tried various things on the Java side to make the Public schema
explicit, such as this:
conn = DriverManager.getConnection("jdbc:ignite:thin://10.60.1.101/PUBLIC");
// conn.setSchema("PUBLIC");
but this does not help with the ODBC problem. The Java stuff still works
fine. Select statements in Java can be written like this and they still
work:
stmt.executeQuery("SELECT p.name, c.name " +
" FROM PUBLIC.Person p, City c " +
" WHERE p.city_id = c.id"))
Any advice on how this should be done (sample code?) is much appreciated.
Thank you.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to create tables with JDBC, read with ODBC?
Posted by David Robinson <dr...@gmail.com>.
Hi,
Here is my ODBC specification.
The schema is specified as Public and this looks (identical?) like the
example in the documentation:
On Thu, Sep 6, 2018 at 11:06 AM Вячеслав Коптилин <sl...@gmail.com>
wrote:
> Hi,
>
> > I have tried various things on the Java side to make the Public schema
> explicit, such as this:
> If I'm not mistaken the schema can be specified as a parameter of ODBC
> connection string.
> Please take a look at this page:
> https://apacheignite-sql.readme.io/docs/connection-string-and-dsn#section-connection-string-format
>
> Also, you can find an example here:
> https://github.com/apache/ignite/blob/master/modules/platforms/cpp/examples/odbc-example/src/odbc_example.cpp
>
> Thanks,
> S.
>
> чт, 6 сент. 2018 г. в 16:59, limabean <dr...@gmail.com>:
>
>> Scenario:
>> 64-bit ODBC driver cannot read data created from the Java Thin driver.
>>
>> Ignite 2.6.
>> Running a single node server on Centos to test this.
>>
>> First:
>> Using Intellij to remotely run the sample code from the Ignite Getting
>> started page here on SQL:
>> First Ignite SQL Application
>> https://apacheignite.readme.io/docs/getting-started
>>
>> This all works fine. Tables created, data inserted, data read. All as
>> expected.
>>
>> Next:
>> Using the ODBC 64-bit driver from Windows 10 to connect to the still
>> running
>> Ignite server to read the same tables (City, Person). This does not
>> work.
>>
>> The ODBC driver appears to be able to get meta data - it gets the table
>> names from the PUBLIC schema and it understands the fields / field counts
>> in
>> each table. However, the ODBC driver is unable to perform any select
>> operations on the tables. See the following stack trace as an example of
>> the
>> errors I am seeing:
>>
>>
>> [13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
>> persistenceEnabled=false]
>> [13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed to
>> execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
>> sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
>> class org.apache.ignite.internal.processors.query.IgniteSQLException:
>> Failed
>> to parse query. Table not found; SQL statement:
>> SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
>>
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
>>
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
>>
>>
>> -----------------------
>>
>> I have tried various things on the Java side to make the Public schema
>> explicit, such as this:
>> conn = DriverManager.getConnection("jdbc:ignite:thin://10.60.1.101/PUBLIC");
>>
>> // conn.setSchema("PUBLIC");
>>
>> but this does not help with the ODBC problem. The Java stuff still works
>> fine. Select statements in Java can be written like this and they still
>> work:
>>
>> stmt.executeQuery("SELECT p.name, c.name " +
>> " FROM PUBLIC.Person p, City c " +
>> " WHERE p.city_id = c.id"))
>>
>>
>> Any advice on how this should be done (sample code?) is much appreciated.
>> Thank you.
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: How to create tables with JDBC, read with ODBC?
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hi,
> I have tried various things on the Java side to make the Public schema
explicit, such as this:
If I'm not mistaken the schema can be specified as a parameter of ODBC
connection string.
Please take a look at this page:
https://apacheignite-sql.readme.io/docs/connection-string-and-dsn#section-connection-string-format
Also, you can find an example here:
https://github.com/apache/ignite/blob/master/modules/platforms/cpp/examples/odbc-example/src/odbc_example.cpp
Thanks,
S.
чт, 6 сент. 2018 г. в 16:59, limabean <dr...@gmail.com>:
> Scenario:
> 64-bit ODBC driver cannot read data created from the Java Thin driver.
>
> Ignite 2.6.
> Running a single node server on Centos to test this.
>
> First:
> Using Intellij to remotely run the sample code from the Ignite Getting
> started page here on SQL:
> First Ignite SQL Application
> https://apacheignite.readme.io/docs/getting-started
>
> This all works fine. Tables created, data inserted, data read. All as
> expected.
>
> Next:
> Using the ODBC 64-bit driver from Windows 10 to connect to the still
> running
> Ignite server to read the same tables (City, Person). This does not work.
>
> The ODBC driver appears to be able to get meta data - it gets the table
> names from the PUBLIC schema and it understands the fields / field counts
> in
> each table. However, the ODBC driver is unable to perform any select
> operations on the tables. See the following stack trace as an example of
> the
> errors I am seeing:
>
>
> [13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
> persistenceEnabled=false]
> [13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed to
> execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
> sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
> class org.apache.ignite.internal.processors.query.IgniteSQLException:
> Failed
> to parse query. Table not found; SQL statement:
> SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
>
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
>
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
>
>
> -----------------------
>
> I have tried various things on the Java side to make the Public schema
> explicit, such as this:
> conn = DriverManager.getConnection("jdbc:ignite:thin://10.60.1.101/PUBLIC");
>
> // conn.setSchema("PUBLIC");
>
> but this does not help with the ODBC problem. The Java stuff still works
> fine. Select statements in Java can be written like this and they still
> work:
>
> stmt.executeQuery("SELECT p.name, c.name " +
> " FROM PUBLIC.Person p, City c " +
> " WHERE p.city_id = c.id"))
>
>
> Any advice on how this should be done (sample code?) is much appreciated.
> Thank you.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to create tables with JDBC, read with ODBC?
Posted by Igor Sapego <is...@apache.org>.
Nice to hear.
Please, keep us updated about what QLIK thinks about the issue.
Thank you in advance
Best Regards,
Igor
On Mon, Sep 10, 2018 at 10:50 PM limabean <dr...@gmail.com> wrote:
> Thank you very much for the thorough discussion/explanation and pending fix
> for public schemas. Much appreciated !
>
> As an aside, I also contacted QLIK to see if they will fix their product
> behavior, which does not seem correct to me either.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to create tables with JDBC, read with ODBC?
Posted by limabean <dr...@gmail.com>.
Thank you very much for the thorough discussion/explanation and pending fix
for public schemas. Much appreciated !
As an aside, I also contacted QLIK to see if they will fix their product
behavior, which does not seem correct to me either.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to create tables with JDBC, read with ODBC?
Posted by Igor Sapego <is...@apache.org>.
I've filed a ticket: [1]
[1] - https://issues.apache.org/jira/browse/IGNITE-9515
Best Regards,
Igor
On Mon, Sep 10, 2018 at 2:56 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> Yes, I'm pretty confident that PUBLIC should work without quotes. I'm even
> not sure that it would work even with ordinary double quotes set.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пн, 10 сент. 2018 г. в 14:28, Igor Sapego <is...@apache.org>:
>
>> Ilya,
>>
>> If we won't bother with quotes, then many other tools will stop working,
>> as cache-names-schemas MUST be quoted, but they won't be. By the way,
>> even QLIK will not work with any other schema, except for PUBLIC.
>>
>> So for now, what I propose is not apply quotes to PUBLIC schema. This is
>> the only fix I can see here now.
>>
>> Best Regards,
>> Igor
>>
>>
>> On Fri, Sep 7, 2018 at 4:45 PM Ilya Kasnacheev <il...@gmail.com>
>> wrote:
>>
>>> Maybe we shouldn't bother to quote schemas, assuming that it's the duty
>>> of client?
>>>
>>> Unfortunately after reading ODBC docs I have no idea, but there's no
>>> hints that the result will be quoted.
>>>
>>> Regards,
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> пт, 7 сент. 2018 г. в 15:38, Igor Sapego <is...@gridgain.com>:
>>>
>>>> Well, ODBC applies quotes to all schemas. It makes sense to
>>>> check and not apply quotes to PUBLIC, but this won't help in
>>>> all other cases, when cache-name-schema is used.
>>>>
>>>> Best Regards,
>>>> Igor
>>>>
>>>>
>>>> On Fri, Sep 7, 2018 at 2:13 PM Ilya Kasnacheev <
>>>> ilya.kasnacheev@gmail.com> wrote:
>>>>
>>>>> Hello!
>>>>>
>>>>> It's actually very strange that we have quotes around PUBLIC since
>>>>> it's supposed to be used quote-free. I will take a look.
>>>>>
>>>>> Regards,
>>>>>
>>>>> --
>>>>> Ilya Kasnacheev
>>>>>
>>>>>
>>>>> пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
>>>>>
>>>>>> It happens, because ODBC returns schema name in quotes,
>>>>>> so seems like QLIK adds its own quotes around it, as it encounters
>>>>>> non standard characters (quotes).
>>>>>>
>>>>>> I think, it is a QLIK's error, as our ODBC driver explicitly states,
>>>>>> that no
>>>>>> additional quotes should be used around identifiers. And even if it
>>>>>> choose
>>>>>> to apply quotes to "PUBLIC" result obviously should not be a
>>>>>> ""PUBLIC"".
>>>>>>
>>>>>>
>>>>>> Best Regards,
>>>>>> Igor
>>>>>>
>>>>>>
>>>>>> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>>>>>>
>>>>>>> Although I specify lower case public in the odbc definition in
>>>>>>> Windows 10,
>>>>>>> the QLIK BI application, on its ODBC connection page, forces an
>>>>>>> upper case
>>>>>>> "PUBLIC" as you can see in the screen shot, and as far as I can tell
>>>>>>> there
>>>>>>> are no options to change that.
>>>>>>>
>>>>>>> QlikOdbcPanel.png
>>>>>>> <
>>>>>>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>>>
>>>>>>
Re: How to create tables with JDBC, read with ODBC?
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Yes, I'm pretty confident that PUBLIC should work without quotes. I'm even
not sure that it would work even with ordinary double quotes set.
Regards,
--
Ilya Kasnacheev
пн, 10 сент. 2018 г. в 14:28, Igor Sapego <is...@apache.org>:
> Ilya,
>
> If we won't bother with quotes, then many other tools will stop working,
> as cache-names-schemas MUST be quoted, but they won't be. By the way,
> even QLIK will not work with any other schema, except for PUBLIC.
>
> So for now, what I propose is not apply quotes to PUBLIC schema. This is
> the only fix I can see here now.
>
> Best Regards,
> Igor
>
>
> On Fri, Sep 7, 2018 at 4:45 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Maybe we shouldn't bother to quote schemas, assuming that it's the duty
>> of client?
>>
>> Unfortunately after reading ODBC docs I have no idea, but there's no
>> hints that the result will be quoted.
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 7 сент. 2018 г. в 15:38, Igor Sapego <is...@gridgain.com>:
>>
>>> Well, ODBC applies quotes to all schemas. It makes sense to
>>> check and not apply quotes to PUBLIC, but this won't help in
>>> all other cases, when cache-name-schema is used.
>>>
>>> Best Regards,
>>> Igor
>>>
>>>
>>> On Fri, Sep 7, 2018 at 2:13 PM Ilya Kasnacheev <
>>> ilya.kasnacheev@gmail.com> wrote:
>>>
>>>> Hello!
>>>>
>>>> It's actually very strange that we have quotes around PUBLIC since it's
>>>> supposed to be used quote-free. I will take a look.
>>>>
>>>> Regards,
>>>>
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
>>>>
>>>>> It happens, because ODBC returns schema name in quotes,
>>>>> so seems like QLIK adds its own quotes around it, as it encounters
>>>>> non standard characters (quotes).
>>>>>
>>>>> I think, it is a QLIK's error, as our ODBC driver explicitly states,
>>>>> that no
>>>>> additional quotes should be used around identifiers. And even if it
>>>>> choose
>>>>> to apply quotes to "PUBLIC" result obviously should not be a
>>>>> ""PUBLIC"".
>>>>>
>>>>>
>>>>> Best Regards,
>>>>> Igor
>>>>>
>>>>>
>>>>> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>>>>>
>>>>>> Although I specify lower case public in the odbc definition in
>>>>>> Windows 10,
>>>>>> the QLIK BI application, on its ODBC connection page, forces an upper
>>>>>> case
>>>>>> "PUBLIC" as you can see in the screen shot, and as far as I can tell
>>>>>> there
>>>>>> are no options to change that.
>>>>>>
>>>>>> QlikOdbcPanel.png
>>>>>> <
>>>>>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>>
>>>>>
Re: How to create tables with JDBC, read with ODBC?
Posted by Igor Sapego <is...@apache.org>.
Ilya,
If we won't bother with quotes, then many other tools will stop working,
as cache-names-schemas MUST be quoted, but they won't be. By the way,
even QLIK will not work with any other schema, except for PUBLIC.
So for now, what I propose is not apply quotes to PUBLIC schema. This is
the only fix I can see here now.
Best Regards,
Igor
On Fri, Sep 7, 2018 at 4:45 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Maybe we shouldn't bother to quote schemas, assuming that it's the duty of
> client?
>
> Unfortunately after reading ODBC docs I have no idea, but there's no hints
> that the result will be quoted.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пт, 7 сент. 2018 г. в 15:38, Igor Sapego <is...@gridgain.com>:
>
>> Well, ODBC applies quotes to all schemas. It makes sense to
>> check and not apply quotes to PUBLIC, but this won't help in
>> all other cases, when cache-name-schema is used.
>>
>> Best Regards,
>> Igor
>>
>>
>> On Fri, Sep 7, 2018 at 2:13 PM Ilya Kasnacheev <il...@gmail.com>
>> wrote:
>>
>>> Hello!
>>>
>>> It's actually very strange that we have quotes around PUBLIC since it's
>>> supposed to be used quote-free. I will take a look.
>>>
>>> Regards,
>>>
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
>>>
>>>> It happens, because ODBC returns schema name in quotes,
>>>> so seems like QLIK adds its own quotes around it, as it encounters
>>>> non standard characters (quotes).
>>>>
>>>> I think, it is a QLIK's error, as our ODBC driver explicitly states,
>>>> that no
>>>> additional quotes should be used around identifiers. And even if it
>>>> choose
>>>> to apply quotes to "PUBLIC" result obviously should not be a ""PUBLIC"".
>>>>
>>>>
>>>> Best Regards,
>>>> Igor
>>>>
>>>>
>>>> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>>>>
>>>>> Although I specify lower case public in the odbc definition in Windows
>>>>> 10,
>>>>> the QLIK BI application, on its ODBC connection page, forces an upper
>>>>> case
>>>>> "PUBLIC" as you can see in the screen shot, and as far as I can tell
>>>>> there
>>>>> are no options to change that.
>>>>>
>>>>> QlikOdbcPanel.png
>>>>> <
>>>>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
Re: How to create tables with JDBC, read with ODBC?
Posted by Ilya Kasnacheev <il...@gmail.com>.
Maybe we shouldn't bother to quote schemas, assuming that it's the duty of
client?
Unfortunately after reading ODBC docs I have no idea, but there's no hints
that the result will be quoted.
Regards,
--
Ilya Kasnacheev
пт, 7 сент. 2018 г. в 15:38, Igor Sapego <is...@gridgain.com>:
> Well, ODBC applies quotes to all schemas. It makes sense to
> check and not apply quotes to PUBLIC, but this won't help in
> all other cases, when cache-name-schema is used.
>
> Best Regards,
> Igor
>
>
> On Fri, Sep 7, 2018 at 2:13 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> It's actually very strange that we have quotes around PUBLIC since it's
>> supposed to be used quote-free. I will take a look.
>>
>> Regards,
>>
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
>>
>>> It happens, because ODBC returns schema name in quotes,
>>> so seems like QLIK adds its own quotes around it, as it encounters
>>> non standard characters (quotes).
>>>
>>> I think, it is a QLIK's error, as our ODBC driver explicitly states,
>>> that no
>>> additional quotes should be used around identifiers. And even if it
>>> choose
>>> to apply quotes to "PUBLIC" result obviously should not be a ""PUBLIC"".
>>>
>>> Best Regards,
>>> Igor
>>>
>>>
>>> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>>>
>>>> Although I specify lower case public in the odbc definition in Windows
>>>> 10,
>>>> the QLIK BI application, on its ODBC connection page, forces an upper
>>>> case
>>>> "PUBLIC" as you can see in the screen shot, and as far as I can tell
>>>> there
>>>> are no options to change that.
>>>>
>>>> QlikOdbcPanel.png
>>>> <
>>>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>
>>>
Re: How to create tables with JDBC, read with ODBC?
Posted by Igor Sapego <is...@gridgain.com>.
Well, ODBC applies quotes to all schemas. It makes sense to
check and not apply quotes to PUBLIC, but this won't help in
all other cases, when cache-name-schema is used.
Best Regards,
Igor
On Fri, Sep 7, 2018 at 2:13 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> It's actually very strange that we have quotes around PUBLIC since it's
> supposed to be used quote-free. I will take a look.
>
> Regards,
>
> --
> Ilya Kasnacheev
>
>
> пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
>
>> It happens, because ODBC returns schema name in quotes,
>> so seems like QLIK adds its own quotes around it, as it encounters
>> non standard characters (quotes).
>>
>> I think, it is a QLIK's error, as our ODBC driver explicitly states, that
>> no
>> additional quotes should be used around identifiers. And even if it choose
>> to apply quotes to "PUBLIC" result obviously should not be a ""PUBLIC"".
>>
>> Best Regards,
>> Igor
>>
>>
>> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>>
>>> Although I specify lower case public in the odbc definition in Windows
>>> 10,
>>> the QLIK BI application, on its ODBC connection page, forces an upper
>>> case
>>> "PUBLIC" as you can see in the screen shot, and as far as I can tell
>>> there
>>> are no options to change that.
>>>
>>> QlikOdbcPanel.png
>>> <
>>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>>
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
Re: How to create tables with JDBC, read with ODBC?
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
It's actually very strange that we have quotes around PUBLIC since it's
supposed to be used quote-free. I will take a look.
Regards,
--
Ilya Kasnacheev
пт, 7 сент. 2018 г. в 14:07, Igor Sapego <is...@apache.org>:
> It happens, because ODBC returns schema name in quotes,
> so seems like QLIK adds its own quotes around it, as it encounters
> non standard characters (quotes).
>
> I think, it is a QLIK's error, as our ODBC driver explicitly states, that
> no
> additional quotes should be used around identifiers. And even if it choose
> to apply quotes to "PUBLIC" result obviously should not be a ""PUBLIC"".
>
> Best Regards,
> Igor
>
>
> On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
>
>> Although I specify lower case public in the odbc definition in Windows 10,
>> the QLIK BI application, on its ODBC connection page, forces an upper case
>> "PUBLIC" as you can see in the screen shot, and as far as I can tell there
>> are no options to change that.
>>
>> QlikOdbcPanel.png
>> <
>> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>>
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: How to create tables with JDBC, read with ODBC?
Posted by Igor Sapego <is...@apache.org>.
It happens, because ODBC returns schema name in quotes,
so seems like QLIK adds its own quotes around it, as it encounters
non standard characters (quotes).
I think, it is a QLIK's error, as our ODBC driver explicitly states, that no
additional quotes should be used around identifiers. And even if it choose
to apply quotes to "PUBLIC" result obviously should not be a ""PUBLIC"".
Best Regards,
Igor
On Thu, Sep 6, 2018 at 6:43 PM limabean <dr...@gmail.com> wrote:
> Although I specify lower case public in the odbc definition in Windows 10,
> the QLIK BI application, on its ODBC connection page, forces an upper case
> "PUBLIC" as you can see in the screen shot, and as far as I can tell there
> are no options to change that.
>
> QlikOdbcPanel.png
> <
> http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to create tables with JDBC, read with ODBC?
Posted by limabean <dr...@gmail.com>.
Although I specify lower case public in the odbc definition in Windows 10,
the QLIK BI application, on its ODBC connection page, forces an upper case
"PUBLIC" as you can see in the screen shot, and as far as I can tell there
are no options to change that.
QlikOdbcPanel.png
<http://apache-ignite-users.70518.x6.nabble.com/file/t361/QlikOdbcPanel.png>
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to create tables with JDBC, read with ODBC?
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Please try using public (lower case) as schema, since quotes force case
sensitivity.
Regards,
--
Ilya Kasnacheev
чт, 6 сент. 2018 г. в 18:25, David Robinson <dr...@gmail.com>:
> I have no control over the format of the query coming through the ODBC
> driver.
>
> That is done automatically as far as I know by the[QLIK BI tool that is
> leveraging the ODBC driver
> to try to read data.
>
> Are you suggesting it is QLIK adding the extra quotes that is causing the
> problem with the H2 driver
> on the Ignite side?
>
> On Thu, Sep 6, 2018 at 11:07 AM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> SELECT COUNT(*) FROM ""PUBLIC"".CITY <-- I don't think you need any
>> quotes around PUBLIC.
>>
>> Regards,
>> Ilya.
>> --
>> Ilya Kasnacheev
>>
>>
>> чт, 6 сент. 2018 г. в 16:59, limabean <dr...@gmail.com>:
>>
>>> Scenario:
>>> 64-bit ODBC driver cannot read data created from the Java Thin driver.
>>>
>>> Ignite 2.6.
>>> Running a single node server on Centos to test this.
>>>
>>> First:
>>> Using Intellij to remotely run the sample code from the Ignite Getting
>>> started page here on SQL:
>>> First Ignite SQL Application
>>> https://apacheignite.readme.io/docs/getting-started
>>>
>>> This all works fine. Tables created, data inserted, data read. All as
>>> expected.
>>>
>>> Next:
>>> Using the ODBC 64-bit driver from Windows 10 to connect to the still
>>> running
>>> Ignite server to read the same tables (City, Person). This does not
>>> work.
>>>
>>> The ODBC driver appears to be able to get meta data - it gets the table
>>> names from the PUBLIC schema and it understands the fields / field
>>> counts in
>>> each table. However, the ODBC driver is unable to perform any select
>>> operations on the tables. See the following stack trace as an example of
>>> the
>>> errors I am seeing:
>>>
>>>
>>> [13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
>>> persistenceEnabled=false]
>>> [13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed
>>> to
>>> execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
>>> sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
>>> class org.apache.ignite.internal.processors.query.IgniteSQLException:
>>> Failed
>>> to parse query. Table not found; SQL statement:
>>> SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
>>> at
>>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
>>>
>>> at
>>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
>>>
>>> at
>>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
>>>
>>>
>>> -----------------------
>>>
>>> I have tried various things on the Java side to make the Public schema
>>> explicit, such as this:
>>> conn = DriverManager.getConnection("jdbc:ignite:thin://
>>> 10.60.1.101/PUBLIC");
>>> // conn.setSchema("PUBLIC");
>>>
>>> but this does not help with the ODBC problem. The Java stuff still works
>>> fine. Select statements in Java can be written like this and they still
>>> work:
>>>
>>> stmt.executeQuery("SELECT p.name, c.name " +
>>> " FROM PUBLIC.Person p, City c " +
>>> " WHERE p.city_id = c.id"))
>>>
>>>
>>> Any advice on how this should be done (sample code?) is much
>>> appreciated.
>>> Thank you.
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
Re: How to create tables with JDBC, read with ODBC?
Posted by David Robinson <dr...@gmail.com>.
I have no control over the format of the query coming through the ODBC
driver.
That is done automatically as far as I know by the[QLIK BI tool that is
leveraging the ODBC driver
to try to read data.
Are you suggesting it is QLIK adding the extra quotes that is causing the
problem with the H2 driver
on the Ignite side?
On Thu, Sep 6, 2018 at 11:07 AM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> SELECT COUNT(*) FROM ""PUBLIC"".CITY <-- I don't think you need any quotes
> around PUBLIC.
>
> Regards,
> Ilya.
> --
> Ilya Kasnacheev
>
>
> чт, 6 сент. 2018 г. в 16:59, limabean <dr...@gmail.com>:
>
>> Scenario:
>> 64-bit ODBC driver cannot read data created from the Java Thin driver.
>>
>> Ignite 2.6.
>> Running a single node server on Centos to test this.
>>
>> First:
>> Using Intellij to remotely run the sample code from the Ignite Getting
>> started page here on SQL:
>> First Ignite SQL Application
>> https://apacheignite.readme.io/docs/getting-started
>>
>> This all works fine. Tables created, data inserted, data read. All as
>> expected.
>>
>> Next:
>> Using the ODBC 64-bit driver from Windows 10 to connect to the still
>> running
>> Ignite server to read the same tables (City, Person). This does not
>> work.
>>
>> The ODBC driver appears to be able to get meta data - it gets the table
>> names from the PUBLIC schema and it understands the fields / field counts
>> in
>> each table. However, the ODBC driver is unable to perform any select
>> operations on the tables. See the following stack trace as an example of
>> the
>> errors I am seeing:
>>
>>
>> [13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
>> persistenceEnabled=false]
>> [13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed to
>> execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
>> sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
>> class org.apache.ignite.internal.processors.query.IgniteSQLException:
>> Failed
>> to parse query. Table not found; SQL statement:
>> SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
>>
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
>>
>> at
>> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
>>
>>
>> -----------------------
>>
>> I have tried various things on the Java side to make the Public schema
>> explicit, such as this:
>> conn = DriverManager.getConnection("jdbc:ignite:thin://10.60.1.101/PUBLIC");
>>
>> // conn.setSchema("PUBLIC");
>>
>> but this does not help with the ODBC problem. The Java stuff still works
>> fine. Select statements in Java can be written like this and they still
>> work:
>>
>> stmt.executeQuery("SELECT p.name, c.name " +
>> " FROM PUBLIC.Person p, City c " +
>> " WHERE p.city_id = c.id"))
>>
>>
>> Any advice on how this should be done (sample code?) is much appreciated.
>> Thank you.
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: How to create tables with JDBC, read with ODBC?
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
SELECT COUNT(*) FROM ""PUBLIC"".CITY <-- I don't think you need any quotes
around PUBLIC.
Regards,
Ilya.
--
Ilya Kasnacheev
чт, 6 сент. 2018 г. в 16:59, limabean <dr...@gmail.com>:
> Scenario:
> 64-bit ODBC driver cannot read data created from the Java Thin driver.
>
> Ignite 2.6.
> Running a single node server on Centos to test this.
>
> First:
> Using Intellij to remotely run the sample code from the Ignite Getting
> started page here on SQL:
> First Ignite SQL Application
> https://apacheignite.readme.io/docs/getting-started
>
> This all works fine. Tables created, data inserted, data read. All as
> expected.
>
> Next:
> Using the ODBC 64-bit driver from Windows 10 to connect to the still
> running
> Ignite server to read the same tables (City, Person). This does not work.
>
> The ODBC driver appears to be able to get meta data - it gets the table
> names from the PUBLIC schema and it understands the fields / field counts
> in
> each table. However, the ODBC driver is unable to perform any select
> operations on the tables. See the following stack trace as an example of
> the
> errors I am seeing:
>
>
> [13:30:19] ^-- default [initSize=256.0 MiB, maxSize=6.3 GiB,
> persistenceEnabled=false]
> [13:33:09,424][SEVERE][client-connector-#45][OdbcRequestHandler] Failed to
> execute SQL query [reqId=0, req=OdbcQueryExecuteRequest [schema=PUBLIC,
> sqlQry=SELECT COUNT(*) FROM ""PUBLIC"".CITY, timeout=0, args=[]]]
> class org.apache.ignite.internal.processors.query.IgniteSQLException:
> Failed
> to parse query. Table not found; SQL statement:
> SELECT COUNT(*) FROM ""PUBLIC"".CITY [42102-195]
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026)
>
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796)
>
> at
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652)
>
>
> -----------------------
>
> I have tried various things on the Java side to make the Public schema
> explicit, such as this:
> conn = DriverManager.getConnection("jdbc:ignite:thin://10.60.1.101/PUBLIC");
>
> // conn.setSchema("PUBLIC");
>
> but this does not help with the ODBC problem. The Java stuff still works
> fine. Select statements in Java can be written like this and they still
> work:
>
> stmt.executeQuery("SELECT p.name, c.name " +
> " FROM PUBLIC.Person p, City c " +
> " WHERE p.city_id = c.id"))
>
>
> Any advice on how this should be done (sample code?) is much appreciated.
> Thank you.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>