none
JDBC Sql doesn't recognize 'TO_TIMESTAMP' RRS feed

  • Question

  • We are trying to implement an incremental crawl with TimeStamp but are having errors.  Our SQL statement is:

     <parameter name="JDBCSQL" type="string">
          <description><![CDATA[This or JDBCSQLFile must be provided. SQL query to crawl against.  Note, any valid SQL is valid here.  <br>Use %TIMESTAMP% where last crawl time gets inserted as a datetime value.  <br>Use %TIMESTAMPSEC% where last crawl time gets inserted as number of seconds since epoch <br> Examples: <br> Oracle: SELECT * FROM tableName WHERE dateField > TO_TIMESTAMP('%TIMESTAMP%','yyyy-MM-DD"T"hh24:mi:ss')&nbsp; <br>Note that the time stamp format used must be as indicated here. <br>MS SQL Server: SELECT * from tableName WHERE dateField > convert(datetime,'%TIMESTAMP%',126)<br>select * from employees <br>Default: (none)]]></description>
          <value><![CDATA[SELECT  [Category] ,[CompanyID] ,[CompanyName] ,[UpdatedDateTime] , 'vSearchCompanies' as [site], '010500000000000515000000A065CF7E784B9B5FE77C8770142D3F0000' as [sddl] FROM [Axon_Fast].[dbo].[vSearchCompanies] WHERE UpdatedDateTime > TO_TIMESTAMP('%TIMESTAMP%','yyyy-MM-DD"T"hh24:mi:ss')]]></value>
        </parameter>

    This is the error:

    JDBCAdapter] Caused by: 'TO_TIMESTAMP' is not a recognized built-in function name.
    JDBCAdapter] Adapter thread will terminate....
    CCTKDocumentFeeder] Publisher 0: Last document in queue has been read
    CCTKDocumentFeeder] Publisher 0: Shutting down: com.fastsearch.esp.cctk.publishers.CCTKDocumentFeed

    Has anyone seen this error before?

    Thanks in advance!

    Thursday, February 16, 2012 5:27 PM

Answers

  • Are you feeding this to an Oracle DB?  TO_TIMESTAMP is specific to Oracle (AFAIK).

    For MS SQLServer DB's, try the CONVERT function.


    Garth Grimm
    Avery Ranch Consulting
    www.averyranchconsulting.com

    • Marked as answer by f803 Friday, February 17, 2012 7:22 PM
    Friday, February 17, 2012 1:31 AM

All replies

  • Are you feeding this to an Oracle DB?  TO_TIMESTAMP is specific to Oracle (AFAIK).

    For MS SQLServer DB's, try the CONVERT function.


    Garth Grimm
    Avery Ranch Consulting
    www.averyranchconsulting.com

    • Marked as answer by f803 Friday, February 17, 2012 7:22 PM
    Friday, February 17, 2012 1:31 AM
  • Thank you!, it is MS SQLServer and the CONVERT function worked.


    Frank Chin

    Friday, February 17, 2012 7:23 PM
  • Actually, I spoke too soon, now I'm getting a format error with date:

    11:22:17,443 INFO  [CCTKDocumentFeeder] FASTSearchSubmit/CallbackType:Secured
    11:22:17,443 INFO  [JDBCConnector] Starting publisher thread 0
    11:22:17,505 INFO  [JDBCAdapter] Opened JDBC Connection
    11:22:17,552 INFO  [JDBCAdapter] Running PRESQL
    11:22:17,552 INFO  [JDBCAdapter] Finished running PRESQL
    11:22:17,614 INFO  [JDBCAdapter] Running SQL: SELECT  [Category] ,[CompanyID] ,[CompanyName] ,[Updat
    chCompanies' as [site], '010500000000000515000000A065CF7E784B9B5FE77C8770142D3F0000' as [sddl] FROM
    SearchCompanies] WHERE UpdatedDateTime > CONVERT('2012-02-13T22:11:15','yyyy-MM-DD"T"hh24:mi:ss')
    11:22:17,677 ERROR [JDBCAdapter] Error when crawling the source system: Unable to execute JDBC crawl
    11:22:17,677 ERROR [JDBCAdapter] Caused by: Error while executing SQL: SELECT  [Category] ,[CompanyI
    UpdatedDateTime] , 'vSearchCompanies' as [site], '010500000000000515000000A065CF7E784B9B5FE77C877014
    FROM [Axon_Fast].[dbo].[vSearchCompanies] WHERE UpdatedDateTime > CONVERT('2012-02-13T22:11:15','yyy
    ')
    11:22:17,677 ERROR [JDBCAdapter] Caused by: Error while executing SQL SELECT statement:SELECT  [Cate
    [CompanyName] ,[UpdatedDateTime] , 'vSearchCompanies' as [site], '010500000000000515000000A065CF7E78
    0000' as [sddl] FROM [Axon_Fast].[dbo].[vSearchCompanies] WHERE UpdatedDateTime > CONVERT('2012-02-1
    -DD"T"hh24:mi:ss')
    11:22:17,692 ERROR [JDBCAdapter] Caused by: Incorrect syntax near '2012-02-13T22:11:15'.


    Frank Chin

    Friday, February 17, 2012 7:26 PM
  • Got it to work with:  CONVERT(DATETIME,'%TIMESTAMP%')

    Thank you,

    Frank Chin


    Frank Chin

    Friday, February 17, 2012 7:49 PM