You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Ns G <ns...@gmail.com> on 2015/10/15 08:11:57 UTC
REG: Issue when creating a Phoenix table through JDBC
Hi All,
I have a requirement where in i need to create tables to JDBC (actually
Spring).
I have written the below code
protected Boolean executeScript(final Reader reader, String action, Long
serviceId) throws IOException, SQLException {
try {
long start = System.currentTimeMillis();
final BufferedReader bufferedReader = new
BufferedReader(reader);
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection connection = null;
String url = "jdbc:phoenix:<regionserver>";
connection = DriverManager.getConnection(url);
for (String sql = bufferedReader.readLine(); sql != null; sql =
bufferedReader.readLine()) {
String trimmedSql = sql.trim();
final boolean ignore = (trimmedSql.length() == 0 ||
trimmedSql.startsWith("--") || trimmedSql.startsWith("//") ||
trimmedSql.startsWith("/*"));
if (!ignore) {
if (trimmedSql.endsWith(";")) {
trimmedSql = trimmedSql.substring(0,
trimmedSql.length() - 1);
}
Statement stmt = null;
/* tables */
stmt = connection.createStatement();
if (action.equalsIgnoreCase("CREATE") &&
(trimmedSql.toUpperCase().startsWith("CREATE") ||
trimmedSql.toUpperCase().startsWith("UPSERT"))){
stmt.execute( MessageFormat.format(trimmedSql,
getSchemNameFromServiceId(serviceId)));
} else if (action.equalsIgnoreCase("DROP") &&
trimmedSql.toUpperCase().startsWith("DROP")){
stmt.execute( MessageFormat.format(trimmedSql,
getSchemNameFromServiceId(serviceId)));
}
stmt.close();
/* SPRING CODE */
/* if
(action.equalsIgnoreCase("CREATE") &&
(trimmedSql.toUpperCase().startsWith("CREATE") ||
trimmedSql.toUpperCase().startsWith("UPSERT"))){
jdbcTemplate.execute(
MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));
} else if (action.equalsIgnoreCase("DROP") &&
trimmedSql.toUpperCase().startsWith("DROP")){
jdbcTemplate.execute(
MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));*/
}
}
return true;
} catch (Exception e) {
return false;
}
}
My .sql file is under src/main/resources/queries folder.
My sql query is
CREATE TABLE TEST (ID UNSIGNED_LONG NOT NULL PRIMARY KEY, ID1 VARCHAR)
SALT_BUCKETS=32, COMPRESSION='SNAPPY';
I am getting below error
org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
Syntax error. Mismatched input. Expecting "STRING_LITERAL", got "<EOF>" at
line 1, column 113.
I saw that a similiar case was raised previously for 4.2 version, where in
the user didnt give a comma, But here the query works when executing
through sqlline.
Can anyone help me please?
Thanks,
Durga Prasad
Re: REG: Issue when creating a Phoenix table through JDBC
Posted by Ns G <ns...@gmail.com>.
Hi James,
I didn't try that. Now after that change it is working. Very trivial error
from my end.
Thanks,
Durga Prasad
On 15-Oct-2015 11:51 am, "James Taylor" <ja...@apache.org> wrote:
> Have you tried double single quotes for the escaping?
>
> On Wed, Oct 14, 2015 at 11:19 PM, Ns G <ns...@gmail.com> wrote:
>
>> Hi James,
>>
>> It is actually not a single sql statement. For simplicity I have just
>> mentioned one. I guess the issue is orginating because of the quotes we
>> need to surrond the word "SNAPPY". I have tried escaping them but no
>> success.
>>
>> Thanks
>> On 15-Oct-2015 11:46 am, "James Taylor" <ja...@apache.org> wrote:
>>
>>> Kind of a guess, but if you're parsing a single SQL statement, it
>>> shouldn't have a semicolon at the end. If that's part of the string, can
>>> you try removing it?
>>>
>>> On Wed, Oct 14, 2015 at 11:11 PM, Ns G <ns...@gmail.com> wrote:
>>>
>>>> Hi All,
>>>>
>>>> I have a requirement where in i need to create tables to JDBC (actually
>>>> Spring).
>>>>
>>>> I have written the below code
>>>>
>>>> protected Boolean executeScript(final Reader reader, String action,
>>>> Long serviceId) throws IOException, SQLException {
>>>> try {
>>>> long start = System.currentTimeMillis();
>>>> final BufferedReader bufferedReader = new
>>>> BufferedReader(reader);
>>>> Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
>>>> Connection connection = null;
>>>> String url = "jdbc:phoenix:<regionserver>";
>>>> connection = DriverManager.getConnection(url);
>>>> for (String sql = bufferedReader.readLine(); sql != null;
>>>> sql = bufferedReader.readLine()) {
>>>> String trimmedSql = sql.trim();
>>>> final boolean ignore = (trimmedSql.length() == 0 ||
>>>> trimmedSql.startsWith("--") || trimmedSql.startsWith("//") ||
>>>> trimmedSql.startsWith("/*"));
>>>> if (!ignore) {
>>>> if (trimmedSql.endsWith(";")) {
>>>> trimmedSql = trimmedSql.substring(0,
>>>> trimmedSql.length() - 1);
>>>> }
>>>> Statement stmt = null;
>>>> /* tables */
>>>> stmt = connection.createStatement();
>>>> if (action.equalsIgnoreCase("CREATE") &&
>>>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>>>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>>>> stmt.execute( MessageFormat.format(trimmedSql,
>>>> getSchemNameFromServiceId(serviceId)));
>>>> } else if (action.equalsIgnoreCase("DROP") &&
>>>> trimmedSql.toUpperCase().startsWith("DROP")){
>>>> stmt.execute( MessageFormat.format(trimmedSql,
>>>> getSchemNameFromServiceId(serviceId)));
>>>> }
>>>> stmt.close();
>>>> /* SPRING CODE */
>>>>
>>>> /* if
>>>> (action.equalsIgnoreCase("CREATE") &&
>>>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>>>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>>>> jdbcTemplate.execute(
>>>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));
>>>> } else if (action.equalsIgnoreCase("DROP") &&
>>>> trimmedSql.toUpperCase().startsWith("DROP")){
>>>> jdbcTemplate.execute(
>>>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));*/
>>>> }
>>>> }
>>>> return true;
>>>> } catch (Exception e) {
>>>> return false;
>>>> }
>>>>
>>>> }
>>>>
>>>> My .sql file is under src/main/resources/queries folder.
>>>>
>>>> My sql query is
>>>>
>>>> CREATE TABLE TEST (ID UNSIGNED_LONG NOT NULL PRIMARY KEY, ID1
>>>> VARCHAR) SALT_BUCKETS=32, COMPRESSION='SNAPPY';
>>>>
>>>> I am getting below error
>>>>
>>>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
>>>> Syntax error. Mismatched input. Expecting "STRING_LITERAL", got "<EOF>" at
>>>> line 1, column 113.
>>>>
>>>> I saw that a similiar case was raised previously for 4.2 version, where
>>>> in the user didnt give a comma, But here the query works when executing
>>>> through sqlline.
>>>>
>>>> Can anyone help me please?
>>>>
>>>> Thanks,
>>>> Durga Prasad
>>>>
>>>
>>>
>
Re: REG: Issue when creating a Phoenix table through JDBC
Posted by James Taylor <ja...@apache.org>.
Have you tried double single quotes for the escaping?
On Wed, Oct 14, 2015 at 11:19 PM, Ns G <ns...@gmail.com> wrote:
> Hi James,
>
> It is actually not a single sql statement. For simplicity I have just
> mentioned one. I guess the issue is orginating because of the quotes we
> need to surrond the word "SNAPPY". I have tried escaping them but no
> success.
>
> Thanks
> On 15-Oct-2015 11:46 am, "James Taylor" <ja...@apache.org> wrote:
>
>> Kind of a guess, but if you're parsing a single SQL statement, it
>> shouldn't have a semicolon at the end. If that's part of the string, can
>> you try removing it?
>>
>> On Wed, Oct 14, 2015 at 11:11 PM, Ns G <ns...@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I have a requirement where in i need to create tables to JDBC (actually
>>> Spring).
>>>
>>> I have written the below code
>>>
>>> protected Boolean executeScript(final Reader reader, String action, Long
>>> serviceId) throws IOException, SQLException {
>>> try {
>>> long start = System.currentTimeMillis();
>>> final BufferedReader bufferedReader = new
>>> BufferedReader(reader);
>>> Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
>>> Connection connection = null;
>>> String url = "jdbc:phoenix:<regionserver>";
>>> connection = DriverManager.getConnection(url);
>>> for (String sql = bufferedReader.readLine(); sql != null;
>>> sql = bufferedReader.readLine()) {
>>> String trimmedSql = sql.trim();
>>> final boolean ignore = (trimmedSql.length() == 0 ||
>>> trimmedSql.startsWith("--") || trimmedSql.startsWith("//") ||
>>> trimmedSql.startsWith("/*"));
>>> if (!ignore) {
>>> if (trimmedSql.endsWith(";")) {
>>> trimmedSql = trimmedSql.substring(0,
>>> trimmedSql.length() - 1);
>>> }
>>> Statement stmt = null;
>>> /* tables */
>>> stmt = connection.createStatement();
>>> if (action.equalsIgnoreCase("CREATE") &&
>>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>>> stmt.execute( MessageFormat.format(trimmedSql,
>>> getSchemNameFromServiceId(serviceId)));
>>> } else if (action.equalsIgnoreCase("DROP") &&
>>> trimmedSql.toUpperCase().startsWith("DROP")){
>>> stmt.execute( MessageFormat.format(trimmedSql,
>>> getSchemNameFromServiceId(serviceId)));
>>> }
>>> stmt.close();
>>> /* SPRING CODE */
>>>
>>> /* if
>>> (action.equalsIgnoreCase("CREATE") &&
>>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>>> jdbcTemplate.execute(
>>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));
>>> } else if (action.equalsIgnoreCase("DROP") &&
>>> trimmedSql.toUpperCase().startsWith("DROP")){
>>> jdbcTemplate.execute(
>>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));*/
>>> }
>>> }
>>> return true;
>>> } catch (Exception e) {
>>> return false;
>>> }
>>>
>>> }
>>>
>>> My .sql file is under src/main/resources/queries folder.
>>>
>>> My sql query is
>>>
>>> CREATE TABLE TEST (ID UNSIGNED_LONG NOT NULL PRIMARY KEY, ID1 VARCHAR)
>>> SALT_BUCKETS=32, COMPRESSION='SNAPPY';
>>>
>>> I am getting below error
>>>
>>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
>>> Syntax error. Mismatched input. Expecting "STRING_LITERAL", got "<EOF>" at
>>> line 1, column 113.
>>>
>>> I saw that a similiar case was raised previously for 4.2 version, where
>>> in the user didnt give a comma, But here the query works when executing
>>> through sqlline.
>>>
>>> Can anyone help me please?
>>>
>>> Thanks,
>>> Durga Prasad
>>>
>>
>>
Re: REG: Issue when creating a Phoenix table through JDBC
Posted by Ns G <ns...@gmail.com>.
Hi James,
It is actually not a single sql statement. For simplicity I have just
mentioned one. I guess the issue is orginating because of the quotes we
need to surrond the word "SNAPPY". I have tried escaping them but no
success.
Thanks
On 15-Oct-2015 11:46 am, "James Taylor" <ja...@apache.org> wrote:
> Kind of a guess, but if you're parsing a single SQL statement, it
> shouldn't have a semicolon at the end. If that's part of the string, can
> you try removing it?
>
> On Wed, Oct 14, 2015 at 11:11 PM, Ns G <ns...@gmail.com> wrote:
>
>> Hi All,
>>
>> I have a requirement where in i need to create tables to JDBC (actually
>> Spring).
>>
>> I have written the below code
>>
>> protected Boolean executeScript(final Reader reader, String action, Long
>> serviceId) throws IOException, SQLException {
>> try {
>> long start = System.currentTimeMillis();
>> final BufferedReader bufferedReader = new
>> BufferedReader(reader);
>> Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
>> Connection connection = null;
>> String url = "jdbc:phoenix:<regionserver>";
>> connection = DriverManager.getConnection(url);
>> for (String sql = bufferedReader.readLine(); sql != null; sql
>> = bufferedReader.readLine()) {
>> String trimmedSql = sql.trim();
>> final boolean ignore = (trimmedSql.length() == 0 ||
>> trimmedSql.startsWith("--") || trimmedSql.startsWith("//") ||
>> trimmedSql.startsWith("/*"));
>> if (!ignore) {
>> if (trimmedSql.endsWith(";")) {
>> trimmedSql = trimmedSql.substring(0,
>> trimmedSql.length() - 1);
>> }
>> Statement stmt = null;
>> /* tables */
>> stmt = connection.createStatement();
>> if (action.equalsIgnoreCase("CREATE") &&
>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>> stmt.execute( MessageFormat.format(trimmedSql,
>> getSchemNameFromServiceId(serviceId)));
>> } else if (action.equalsIgnoreCase("DROP") &&
>> trimmedSql.toUpperCase().startsWith("DROP")){
>> stmt.execute( MessageFormat.format(trimmedSql,
>> getSchemNameFromServiceId(serviceId)));
>> }
>> stmt.close();
>> /* SPRING CODE */
>>
>> /* if
>> (action.equalsIgnoreCase("CREATE") &&
>> (trimmedSql.toUpperCase().startsWith("CREATE") ||
>> trimmedSql.toUpperCase().startsWith("UPSERT"))){
>> jdbcTemplate.execute(
>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));
>> } else if (action.equalsIgnoreCase("DROP") &&
>> trimmedSql.toUpperCase().startsWith("DROP")){
>> jdbcTemplate.execute(
>> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));*/
>> }
>> }
>> return true;
>> } catch (Exception e) {
>> return false;
>> }
>>
>> }
>>
>> My .sql file is under src/main/resources/queries folder.
>>
>> My sql query is
>>
>> CREATE TABLE TEST (ID UNSIGNED_LONG NOT NULL PRIMARY KEY, ID1 VARCHAR)
>> SALT_BUCKETS=32, COMPRESSION='SNAPPY';
>>
>> I am getting below error
>>
>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
>> Syntax error. Mismatched input. Expecting "STRING_LITERAL", got "<EOF>" at
>> line 1, column 113.
>>
>> I saw that a similiar case was raised previously for 4.2 version, where
>> in the user didnt give a comma, But here the query works when executing
>> through sqlline.
>>
>> Can anyone help me please?
>>
>> Thanks,
>> Durga Prasad
>>
>
>
Re: REG: Issue when creating a Phoenix table through JDBC
Posted by James Taylor <ja...@apache.org>.
Kind of a guess, but if you're parsing a single SQL statement, it shouldn't
have a semicolon at the end. If that's part of the string, can you try
removing it?
On Wed, Oct 14, 2015 at 11:11 PM, Ns G <ns...@gmail.com> wrote:
> Hi All,
>
> I have a requirement where in i need to create tables to JDBC (actually
> Spring).
>
> I have written the below code
>
> protected Boolean executeScript(final Reader reader, String action, Long
> serviceId) throws IOException, SQLException {
> try {
> long start = System.currentTimeMillis();
> final BufferedReader bufferedReader = new
> BufferedReader(reader);
> Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
> Connection connection = null;
> String url = "jdbc:phoenix:<regionserver>";
> connection = DriverManager.getConnection(url);
> for (String sql = bufferedReader.readLine(); sql != null; sql
> = bufferedReader.readLine()) {
> String trimmedSql = sql.trim();
> final boolean ignore = (trimmedSql.length() == 0 ||
> trimmedSql.startsWith("--") || trimmedSql.startsWith("//") ||
> trimmedSql.startsWith("/*"));
> if (!ignore) {
> if (trimmedSql.endsWith(";")) {
> trimmedSql = trimmedSql.substring(0,
> trimmedSql.length() - 1);
> }
> Statement stmt = null;
> /* tables */
> stmt = connection.createStatement();
> if (action.equalsIgnoreCase("CREATE") &&
> (trimmedSql.toUpperCase().startsWith("CREATE") ||
> trimmedSql.toUpperCase().startsWith("UPSERT"))){
> stmt.execute( MessageFormat.format(trimmedSql,
> getSchemNameFromServiceId(serviceId)));
> } else if (action.equalsIgnoreCase("DROP") &&
> trimmedSql.toUpperCase().startsWith("DROP")){
> stmt.execute( MessageFormat.format(trimmedSql,
> getSchemNameFromServiceId(serviceId)));
> }
> stmt.close();
> /* SPRING CODE */
>
> /* if
> (action.equalsIgnoreCase("CREATE") &&
> (trimmedSql.toUpperCase().startsWith("CREATE") ||
> trimmedSql.toUpperCase().startsWith("UPSERT"))){
> jdbcTemplate.execute(
> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));
> } else if (action.equalsIgnoreCase("DROP") &&
> trimmedSql.toUpperCase().startsWith("DROP")){
> jdbcTemplate.execute(
> MessageFormat.format(trimmedSql, getSchemNameFromServiceId(serviceId)));*/
> }
> }
> return true;
> } catch (Exception e) {
> return false;
> }
>
> }
>
> My .sql file is under src/main/resources/queries folder.
>
> My sql query is
>
> CREATE TABLE TEST (ID UNSIGNED_LONG NOT NULL PRIMARY KEY, ID1 VARCHAR)
> SALT_BUCKETS=32, COMPRESSION='SNAPPY';
>
> I am getting below error
>
> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
> Syntax error. Mismatched input. Expecting "STRING_LITERAL", got "<EOF>" at
> line 1, column 113.
>
> I saw that a similiar case was raised previously for 4.2 version, where in
> the user didnt give a comma, But here the query works when executing
> through sqlline.
>
> Can anyone help me please?
>
> Thanks,
> Durga Prasad
>