Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Setting SQL_MODE for Mysql database only (Pentaho stripping comments)

  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Default Setting SQL_MODE for Mysql database only (Pentaho stripping comments)

    Hi There

    I'm working with some jobs at the moment that have to run on both Mysql and MS SQL. All is fine and the lovely feature of Mysql called SQL_MODE allows me to use

    Code:
    SET sql_mode = 'MSSQL';
    I can even write

    Code:
    /*! SET sql_mode = 'MSSQL'*/;
    in a comment hint so that it is only executed in Mysql and when the same SQL is run on MS SQL it is safely ignored.

    My problem comes to running this in Pentaho (either in the Advanced section of Database Connection dialog or in SQL job itself). It works fine if I just put SET sql_mode = 'MSSQL' - but if I use it in the second form in the comment hint then Pentaho thinks it's a comments and strips the comment out.

    I've found the code that does this in SqlScriptParser.java - sure I could patch it, but I am wondering if there is a better way to do this that anyone can think of?

    Originally I was going to make this pre-run-SQL a variable (so I could not have it for when we run on MSSQL) but the Database Connection method does not support variable substitution.

    Any ideas much appreciated!

    Steve

  2. #2
    Join Date
    Sep 2009
    Posts
    5

    Default

    Ok - just replying to my own post in case someone else has the same problem (funny how posting to forum triggers different thinking).

    I trawled through the mysql jdbc manual and I found that you can set session variables on the jdbc connection string too, so all I need to do (as my jdbc string is already parameterized) is something like:

    Code:
    jdbc:mysql://localhost:3306/MyDatabaseName?sessionVariables=sql_mode='MSSQL'

Tags for this Thread

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.