Hitachi Vantara Pentaho Community Forums
Results 1 to 19 of 19

Thread: How to escape semicolons in the SQL editor

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Default How to escape semicolons in the SQL editor

    I am trying to run a statement in the SQL editor that includes a semicolon, similar to this:

    INSERT INTO TABLE(COLUMN1,COLUMN2)
    VALUES ("THIS IS TEXT; 1", "THIS IS TEXT; 2");

    As you can imagine, the editor complains about the semicolons in the values. How can I escape them in order to avoid the error? (I tried \;, but didn't help)

    Thanks in advance for your help
    Last edited by ricvilla; 09-12-2007 at 01:17 PM.

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    It will work if you use matching quotes.

    Regards,
    Sven
    Last edited by sboden; 08-08-2007 at 02:29 AM.

  3. #3
    Join Date
    Aug 2007
    Posts
    4

    Default

    Quote Originally Posted by sboden View Post
    It will work if you use matching quotes.

    Regards,
    Sven
    Sorry, my example included mismatching quotes, but the real code doesn't. I still have the issue. Any help is appreciated. Thanks!

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Attach your example... the ';'s inside of quotes should be ignored (I ran a small sample and checked the code).

    Regards,
    Sven

  5. #5
    Join Date
    Aug 2007
    Posts
    4

    Default Full scope of the problem

    Ok, I was trying to simplify the scenario by using the SQL Editor, but you are right, the data goes in if done this way. This is the exact situation:

    I have a spoon job with a SQL script step in it. Data is fed via a table input from a different database. The script looks like this:

    ---------------------

    SET IDENTITY_INSERT email ON;
    INSERT INTO email
    (
    eml_docid
    , eml_docname
    , eml_docdesc
    , eml_from
    , eml_subject
    , eml_body
    , version
    )

    VALUES (?, '?', '?', '?', '?', '?', ?);

    SET IDENTITY_INSERT email OFF;

    --------------------

    The structure of the email table looks like this:

    -----------------------------

    eml_docid [int] IDENTITY (1, 1) NOT NULL ,
    version [int] NOT NULL ,
    eml_docname [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    eml_docdesc [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    eml_from [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    eml_subject [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    eml_body [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ------------------

    Whenever the value inserted into the eml_body field contains a semicolon, kettle reports the following error:

    2007/09/12 09:07:15 - storefront - launch DDL statement:
    2007/09/12 09:07:15 - storefront - INSERT INTO email
    (
    eml_docid
    , eml_docname
    , eml_docdesc
    , eml_from
    , eml_subject
    , eml_body
    , version
    )
    VALUES (2, 'Default', 'Default Email', '<Company Email Here>', 'Your <Your Company Name Here> Order # &ORNO'',''Thank you &CONT for your order!', 'Your
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Unexpected error in ' : be.ibridge.kettle.core.exception.KettleDatabaseException:
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Couldn't execute SQL: INSERT INTO email
    (
    eml_docid
    , eml_docname
    , eml_docdesc
    , eml_from
    , eml_subject
    , eml_body
    , version
    )
    VALUES (2, 'Default', 'Default Email', '<Company Email Here>', 'Your <Your Company Name Here> Order # &ORNO'',''Thank you &CONT for your order!', 'Your
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) :
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Invalid SQL statement or JDBC escape, terminating ''' not found.
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : be.ibridge.kettle.core.exception.KettleDatabaseException:
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Couldn't execute SQL: INSERT INTO email
    (
    eml_docid
    , eml_docname
    , eml_docdesc
    , eml_from
    , eml_subject
    , eml_body
    , version
    )
    VALUES (2, 'Default', 'Default Email', '<Company Email Here>', 'Your <Your Company Name Here> Order # &ORNO'',''Thank you &CONT for your order!', 'Your
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) :
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Invalid SQL statement or JDBC escape, terminating ''' not found.
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) :
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1967)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1913)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.execStatements(Database.java:2085)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.sql.ExecSQL.processRow(ExecSQL.java:151)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.trans.step.sql.ExecSQL.run(ExecSQL.java:236)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Caused by: java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found.
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1155)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:625)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1936)
    2007/09/12 09:07:15 - Execute SQL script.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : ... 4 more
    2007/09/12 09:07:15 - sfadmin - Statement canceled!
    2007/09/12 09:07:15 - sfadmin - Statement canceled!
    2007/09/12 09:07:15 - storefront - Statement canceled!
    2007/09/12 09:07:15 - storefront - Statement canceled!

    -----------------

    The interesting thing is that if we change the type of eml_body from TEXT to NVARCHAR, the problem is gone. Unfortunately, this is not something that we are free to change in a production environment.

    Thanks in advance for your help and feedback on this matter

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The database is having a problem with the double quotes inside the single quotes in the statement.
    Can't you simply use the "Table Output" step? That one should have no problem at all with the data.

  7. #7
    Join Date
    Aug 2007
    Posts
    4

    Default

    Quote Originally Posted by MattCasters View Post
    The database is having a problem with the double quotes inside the single quotes in the statement.
    Can't you simply use the "Table Output" step? That one should have no problem at all with the data.
    I fixed my original posting in order to avoid confusion (the quotes in the real script are fine). Here is the actual script, as explained in my previous post:

    SET IDENTITY_INSERT email ON;
    INSERT INTO email

    (
    eml_docid
    , eml_docname
    , eml_docdesc
    , eml_from
    , eml_subject
    , eml_body
    , version
    )

    VALUES (?, '?', '?', '?', '?', '?', ?);

    SET IDENTITY_INSERT email OFF;

  8. #8
    Join Date
    Aug 2007
    Posts
    6

    Smile Creating Stored Functions in SQL Editor

    Dear Colleagues,

    I think I have essentially the same problem that I read in this thread, namely: it is not possible to use multiple semicolons in one command that is to be sent to the connected database.

    I am connecting to a PostgreSQL database and would like to create a stored function via the SQL script editor. I encountered two problems:

    1. When creating a stored function (in PL/pgSQL), each statement is ended with a semi colon. However, the JDBC driver apparently looks for the first semi colon and then submits the SQL that was processed up to that point. This results in the submission of only part of the stored function DDL.

    2. The other problem is PL/pgSQL uses $$ in order to refer to a link label. However, the $ is interpreted as a
    Unix environment variable.

    Is there a way to suppress the direct parsing of each semicolon and/or each $ sign when the SQL is executed? Or can you recommend some other work around.

    Currently, I decided to simply create the stored function outside of Kettle and only use it via a select statement, but I would like to have all my code integrated in Kettle.

    Can you help me on this?

    Kind regards,

    Randy

  9. #9
    Join Date
    May 2006
    Posts
    4,882

    Default

    Post the thing that you want to execute... ;'s in strings are handled properly.

    Regards,
    Sven

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I don't think that this is the issue. The issue is that we're not recognizing the PL/SQL dialect (or any other for that matter).
    It's come up before on this forum and we always suggest to use a standard script that calls sqlplus, mysql, isql, psql or whatever to execute the SQL dialects. There is a feature request out there to allow you to specify the engine to execute with, but I couldn't find it right away.

    Any ideas about that Sven, maybe it's still on Javaforge?

    Cheers,

    Matt

  11. #11
    Join Date
    May 2006
    Posts
    4,882

    Default

    There was discussion on it, but I don't think it made it to a actual JIRA/javaforge tracker. If I remember correctly it was something about being tied to 1 database, and not having a real standard to be able to make it generic.

    Regards,
    Sven

  12. #12
    Join Date
    Aug 2007
    Posts
    6

    Default

    Dear Sven and Matt,

    Thank you for your replies.

    If I understood your feedback thus far, then I should proceed by: "... using a standard script that calls sqlplus, mysql, isql, psql to execute the SQL dialects" In my case, this would be that I write a operating system specific script that runs the psql program and provides it with an input file to be executed. Matt, is this was you are recommending me as the next course of action? If yes, then I can write the script, but how do I execute a this script from within Kettle? Which step type enables me to execute such a command line script on the local host?

    Best regards,

    Randy

  13. #13
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    A shell script job entry.

  14. #14
    Join Date
    Aug 2007
    Posts
    6

    Default It works now. Thank you!

    Dear Matt,

    In the meantime, I wrote a shell script and called it from Kettle as you suggested and it worked.

    Thank you for very much for your support!

    Kind regards,

    Randy

  15. #15
    Join Date
    Mar 2008
    Posts
    2

    Default

    Hello,

    My SQL statement has following syntax:

    BEGIN INSERT INTO DP_STICHTAGE (stichtag) VALUES (200803);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;END;

    Now I have got a problem with the semicolons.... please, can you give me an example, how I can solve the problem of cutting after the ; ?

  16. #16
    Join Date
    May 2006
    Posts
    4,882

    Default

    It should be used to execute "regular" SQL statements, not what you try to execute.

    Regards,
    Sven

  17. #17
    Join Date
    Jul 2007
    Posts
    247

    Default

    1. Your problem has nothing to do with semicolons but with the misuse of the SQL step (and please, don't hijack threads...)
    2. What you are trying to do is not supported.
    3. There's an easy solution to your problem - David had the same question a few days ago.
    4. Why are you afraid of using your real name in your profile?


    Regards,
    Ben

  18. #18
    Join Date
    Mar 2008
    Posts
    2

    Default

    Well, thank you for your help. I try another solution.

  19. #19
    Join Date
    Nov 1999
    Posts
    459

    Default PL/SQL: Add checkbox to SQL Job Entry "Send SQL as single statement"

    FYI: The feature request is:
    http://jira.pentaho.com/browse/PDI-2598

    and is fixed in the upcoming 4.0 :-)

    Cheers,
    Jens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.