Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: JDBC SQL connection error with special character dot

  1. #1

    Default JDBC SQL connection error with special character dot

    Hi,
    i have a mistake with a connection on my sql database. i use jdbc driver and i have a special character in the name of my database. the name is like "c:\... " and i think there is a mistake with the ":" in the name.
    if i try with a database name "c\..." without ":" it works...

    i can't change the name of database so i hope there is a solution to work with special character ":"

    Thanks in advance for your help

    Gaëtan

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi Gaëtan,

    You could setup a Parameter for the job and then you that in the connection database field of the connection.

    Example :-

    Define a Parameter in the JOB properties (ie. DBNAME)
    In your connection - Use this parameter in the database field - ${DBNAME}
    Execute you job passing the parameter on the command line

    ./kitchen.sh your_job_name -paramBNAME=your_database_name (value without the ':')

    The last step might be different depending on how you execute your job.

    HTH.


    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3

    Default

    Hi,
    thanks for your help.
    i didn't know that you can create properties in job properties.
    so i try to define two parameters like that:
    Name:  Parameters.PNG
Views: 246
Size:  27.7 KB
    after, i configure my connection like that
    Name:  Configure_SQL_Connection.jpg
Views: 252
Size:  18.8 KB
    it works with the database "test" but it not works with the database "c:"

    i have the same mistake...
    my configuration is good?

  4. #4
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi,

    Try escaping the ":" - c\:\\ ( you want c:\ right?)
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  5. #5

    Default

    yes, i want "c:"
    it not works with "c\:\"
    i also tried "c%3A" because "%3A" is the URL encoded characters for ":" but it not works...

  6. #6

    Default

    excuse me, i want "c:"

  7. #7
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi,

    According to https://wiki.pentaho.com/display/EAI/.07+Variables

    you should be able for specify 'c$[3A]' to denote 'c:'.

    Give that a go.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  8. #8
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    According to https://docs.microsoft.com/en-us/sql...se-identifiers

    Your database name is not allowed to contain :
    Rules for Regular Identifiers

    The names of variables, functions, and stored procedures must comply with the following rules for Transact-SQL identifiers.

    • The first character must be one of the following:

      • A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
      • The underscore (_), at sign (@), or number sign (#).
        Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
        Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

    • Subsequent characters can include the following:

      • Letters as defined in the Unicode Standard 3.2.
      • Decimal numbers from either Basic Latin or other national scripts.
      • The at sign, dollar sign ($), number sign, or underscore.

    • The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. The words that are reserved depend on the database compatibility level. This level can be set by using the ALTER DATABASE statement.
    • Embedded spaces or special characters are not allowed.
    • Supplementary characters are not allowed.
      When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.

  9. #9

    Default

    hi,

    thanks for your help.
    Due to your advice, i progress in the resolution of my problem. it's not works yet but i progress!
    Now i can escaping ":" with this syntax "c$[3a]". PDI interpret this syntax like "c:" wich is the name of my database. but i always have the mistake below:
    Name:  Capture_2.jpg
Views: 250
Size:  35.3 KB

  10. #10

    Default

    It works!
    the good syntax is [c$[3a]\]
    thanks a lot for your help and have a good day!

    Gaëtan

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.