0

SQL Declare no longer supported?

I have a query that was working using the MS SQL JDBC driver. It has a declare for a parameter used throughout the query. I have recently upgraded to 4.6 and also switched the driver to the JTDS. 

So the question is what is now allowed in the query for MS SQL using the JTDS driver?

16replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Marc Paige -- does the query work if you switch back to the other MS SQL JDBC driver?  I would like to isolate the issue a bit to determine if it is the driver (likely) or the Incorta version (less likely).

    Like
    • Dan Brock I will check. The reason for switching to JTDS was that tables were not loading. Switching made that work. I will update once I perform the test.

      Like
    •  Dan Brock I created a parallel data source using the JDBC driver to the same database, created a new schema and table using the original query with the declare in place and tested using the execute button. I received the following error:

      INC_005002001:Failed to Connect to [dw_star_jdbc] due to [Incorrect syntax near the keyword 'declare'.] with properties [[database, sqlserver], [user, Incorta], [connectionPool, 30], [connection, jdbc:sqlserver://hgopdentdw01:1433;databaseName=DW_STAR;]]

      So, no, using the JDBC driver did not work either.

      Like
    • Marc Paige Hmmm.  And this DECLARE statement worked on version 4.5, correct?  Let me see what our support engineers have to say.

      Like
    • Dan Brock Yes.

      Like
    • Marc Paige What version of Java are you using?  OpenJDK 11?  Oracle Java8?  Other?

      Like
    • Dan Brock java version "1.8.0_201"

      Like
    • Marc Paige Oracle's distribution of Java or OpenJDK?

      Like
    • Dan Brock Oracle.

      Like
  • Marc Paige 

    I think this is a problem with JTDS driver but I'd recommend trying an alternative approach.

    I agree that Declare variable statement is very much useful when it is needed more than a once. Please also log a ticket for our support team to analyze it further.

     

    Original

    DECLARE @RptDt  DATETIME

    SET @RptDt  = GETDATE()-1

    SELECT *

      FROM Apac.EMPLOYEE

    WHERE HIRE_DT > @RPTDT

    UNION ALL

    SELECT *

      FROM Amer.EMPLOYEE 

    WHERE HIRE_DT > @RPTDT

     

    Alternative

    SELECT *

      FROM Apac.EMPLOYEE

    WHERE HIRE_DT > GETDATE()-1

    UNION ALL
    SELECT *

      FROM Amer.EMPLOYEE

    WHERE HIRE_DT > GETDATE()-1

    Like
    • Siva , thanks. I have already done the alternative and it was painful. This is not limited to the JTDS driver. It doesn't work with the jdbc driver either in 4.6. This seems to be a new issue with 4.6.

      Like
    • Marc Paige 

      Declare statements should work against regular SQL Server driver. I tested on 4.6 version and it seems to be working.

      Shall we connect tomorrow? perhaps, it might be a different issue in your environment with JDBC driver.

      Like
    • Siva Kowsika Ok, thanks. I thought I had tried both with a zoom session with Dan. The problem is that the jdbc connector has issues with loading. Switching to the jtds driver fixed those issues.

      Like
  • Marc Paige have you created a ticket with support on this issue?

    Like
    • Dan Brock Not yet. I am trying to figure out how to generalize the problem statement.

      Like
    • Dan Brock I submitted a ticket referencing this Q&A.

      Like
Like Follow
  • Status Answered
  • 2 wk agoLast active
  • 16Replies
  • 30Views
  • 3 Following