Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Connecting MS SQL Server Database

  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Default Connecting MS SQL Server Database

    I am using the property file for MSSQLSERVER2005 shipped with Weka 3.7.31 to work with SQL Server database2014, and I am using the JDBC driver for MS SQL Server: sqljdbc4.jar. I can successfully to connect to MS SQL Server database, but when I use Explorer to extract data from it, the following error occurs:
    Couldn’t read from database.
    Unknown data type: date, Add entry in weka/experiment/DatabaseUtils.props
    If the type contains blanks, either escape them with a backslash or use underscores instead of blanks.


    Can you tell me how to fix this problem?

    I have copied my DatabaseUtils.props to my home directory c:\wekafile\props\DatabaseUtils.props

    My Property file like below
    # Database settings for Microsoft SQL Server 2005 Express Edition
    #
    # General information on database access can be found here:
    # http://weka.wikispaces.com/Databases
    #
    # url: http://www.microsoft.com/
    # jdbc: http://msdn2.microsoft.com/en-us/data/aa937724.aspx
    # author: Fracpete (fracpete at waikato dot ac dot nz)
    # version: $Revision: 11885 $


    # JDBC driver (comma-separated list)
    jdbcDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver


    # database URL
    jdbcURL=jdbc:sqlserver://192.168.0.100:1433;databaseName=MY_DB


    # specific data types
    # string, getString() = 0; --> nominal
    # boolean, getBoolean() = 1; --> nominal
    # double, getDouble() = 2; --> numeric
    # byte, getByte() = 3; --> numeric
    # short, getByte()= 4; --> numeric
    # int, getInteger() = 5; --> numeric
    # long, getLong() = 6; --> numeric
    # float, getFloat() = 7; --> numeric
    # date, getDate() = 8; --> date
    # text, getString() = 9; --> string
    # time, getTime() = 10; --> date
    # timestamp, getTime() = 11; --> date


    varchar=0
    char=0
    text=0
    float=2
    decimal=2
    tinyint=3
    int=5
    integer=5
    date=8
    bit=1


    # other options
    CREATE_DOUBLE=DOUBLE PRECISION
    CREATE_STRING=VARCHAR(8000)
    CREATE_INT=INT
    CREATE_DATE=DATETIME
    DateFormat=yyyy-MM-dd HH:mm:ss
    checkUpperCaseNames=false
    checkLowerCaseNames=false
    checkForTable=true


    # All the reserved keywords for this database
    # Based on the keywords listed at the following URL (2009-04-13):
    # http://msdn.microsoft.com/en-us/libr...2(SQL.90).aspx
    Keywords=\
    ADD,\
    ALL,\
    ALTER,\
    AND,\
    ANY,\
    AS,\
    ASC,\
    AUTHORIZATION,\
    BACKUP,\
    BEGIN,\
    BETWEEN,\
    BREAK,\
    BROWSE,\
    BULK,\
    BY,\
    CASCADE,\
    CASE,\
    CHECK,\
    CHECKPOINT,\
    CLOSE,\
    CLUSTERED,\
    COALESCE,\
    COLLATE,\
    COLUMN,\
    COMMIT,\
    COMPUTE,\
    CONSTRAINT,\
    CONTAINS,\
    CONTAINSTABLE,\
    CONTINUE,\
    CONVERT,\
    CREATE,\
    CROSS,\
    CURRENT,\
    CURRENT_DATE,\
    CURRENT_TIME,\
    CURRENT_TIMESTAMP,\
    CURRENT_USER,\
    CURSOR,\
    DATABASE,\
    DBCC,\
    DEALLOCATE,\
    DECLARE,\
    DEFAULT,\
    DELETE,\
    DENY,\
    DESC,\
    DISK,\
    DISTINCT,\
    DISTRIBUTED,\
    DOUBLE,\
    DROP,\
    DUMP,\
    ELSE,\
    END,\
    ERRLVL,\
    ESCAPE,\
    EXCEPT,\
    EXEC,\
    EXECUTE,\
    EXISTS,\
    EXIT,\
    EXTERNAL,\
    FETCH,\
    FILE,\
    FILLFACTOR,\
    FOR,\
    FOREIGN,\
    FREETEXT,\
    FREETEXTTABLE,\
    FROM,\
    FULL,\
    FUNCTION,\
    GOTO,\
    GRANT,\
    GROUP,\
    HAVING,\
    HOLDLOCK,\
    IDENTITY,\
    IDENTITYCOL,\
    IDENTITY_INSERT,\
    IF,\
    IN,\
    INDEX,\
    INNER,\
    INSERT,\
    INTERSECT,\
    INTO,\
    IS,\
    JOIN,\
    KEY,\
    KILL,\
    LEFT,\
    LIKE,\
    LINENO,\
    LOAD,\
    NATIONAL,\
    NOCHECK,\
    NONCLUSTERED,\
    NOT,\
    NULL,\
    NULLIF,\
    OF,\
    OFF,\
    OFFSETS,\
    ON,\
    OPEN,\
    OPENDATASOURCE,\
    OPENQUERY,\
    OPENROWSET,\
    OPENXML,\
    OPTION,\
    OR,\
    ORDER,\
    OUTER,\
    OVER,\
    PERCENT,\
    PIVOT,\
    PLAN,\
    PRECISION,\
    PRIMARY,\
    PRINT,\
    PROC,\
    PROCEDURE,\
    PUBLIC,\
    RAISERROR,\
    READ,\
    READTEXT,\
    RECONFIGURE,\
    REFERENCES,\
    REPLICATION,\
    RESTORE,\
    RESTRICT,\
    RETURN,\
    REVERT,\
    REVOKE,\
    RIGHT,\
    ROLLBACK,\
    ROWCOUNT,\
    ROWGUIDCOL,\
    RULE,\
    SAVE,\
    SCHEMA,\
    SECURITYAUDIT,\
    SELECT,\
    SESSION_USER,\
    SET,\
    SETUSER,\
    SHUTDOWN,\
    SOME,\
    STATISTICS,\
    SYSTEM_USER,\
    TABLE,\
    TABLESAMPLE,\
    TEXTSIZE,\
    THEN,\
    TO,\
    TOP,\
    TRAN,\
    TRANSACTION,\
    TRIGGER,\
    TRUNCATE,\
    TSEQUAL,\
    UNION,\
    UNIQUE,\
    UNPIVOT,\
    UPDATE,\
    UPDATETEXT,\
    USE,\
    USER,\
    VALUES,\
    VARYING,\
    VIEW,\
    WAITFOR,\
    WHEN,\
    WHERE,\
    WHILE,\
    WITH,\
    WRITETEXT


    # The character to append to attribute names to avoid exceptions due to
    # clashes between keywords and attribute names
    KeywordsMaskChar=_


    #flags for loading and saving instances using DatabaseLoader/Saver
    nominalToStringLimit=50
    idColumn=auto_generated_id



    Please help me to solve this problem..
    Thanks in advance

  2. #2
    Join Date
    Aug 2006
    Posts
    1,741

    Default

    Hmm. I can't see any issues with your DatabaseUtils.props file. Did you restart Weka after making changes to the file? To verify that the file has been picked up by Weka - does SQLViewer in the Explorer show your jdbc connection URL pre-populated in the URL field? Also check that the "date=8" entry doesn't contain any spaces or other strange characters that might prevent it from being matched.

    Cheers,
    Mark.

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

    Default

    Could this relate to the date datatype or the datetime2 datatype that were added in SQL Server 2008?

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.