Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C#
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-30-2024 04:47 AM
I am trying to declare variable in SQL statement and try to execute it via ODBC C# driver(System.Data.Odbc 8.0.0) but it fails with parse syntax error.
When I try the same query on SQL editor, it runs.
Query:
Below is the error.
Message: ERROR [42000] [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DECLARE': extra input 'DECLARE'. SQLSTATE: 42601 (line 1, pos 56) == SQL == DECLARE VARIABLE users_core__user_name_last = 'Young'; DECLARE VARIABLE users_core__user_name_last1 = 'Watson'; DECLARE VARIABLE users_core__user_name_last2 = 'Scott'; DECLARE VARIABLE users_core__user_name_last3 = 'Rogers'; DECLARE VARIABLE users_core__user_name_last4 = 'Smith%';SELECT DISTINCT users_core359.`user_name_first` AS `User_First_Name_21_70`, users_core359.`user_name_last` AS `User_Last_Name_22_70`, users_core359_96.`user_name_first` AS `User_First_Name_21_96`, users_core359_96.`user_name_last` AS `User_Last_Name_22_96` FROM users_core users_core359 LEFT JOIN users_core users_core359_96 ON users_core359.`user_mgr_id` = users_core359_96.`user_id` WHERE (users_core359.`user_name_last` IN (users_core__user_name_last, users_core__user_name_last1, users_co[Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DECLARE': extra input 'DECLARE'. SQLSTATE: 42601 (line 1, pos 56) == SQL == DECLARE VARIABLE users_core__user_name_last = 'Young'; DECLARE VARIABLE users_core__user_name_last1 = 'Watson'; DECLARE VARIABLE users_core__user_name_last2 = 'Scott'; DECLARE VARIABLE users_core__user_name_last3 = 'Rogers'; DECLARE VARIABLE users_core__user_name_last4 = 'Smith%';SELECT DISTINCT users_core359.`user_name_first` AS `User_First_Name_21_70`, users_core359.`user_name_last` AS `User_Last_Name_22_70`, users_core359_96.`user_name_first` AS `User_First_Name_21_96`, users_core359_96.`user_name_last` AS `User_Last_Name_22_96` FROM users_core users_core359 LEFT JOIN users_core users_core359_96 ON users_core359.`user_mgr_id` = users_core359_96.`user_id` WHERE (users_core359.`user_name_last` IN (users_core__user_name_last, users_core__user_name_last1, users_co, Stack Trace: at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, SQLRETURN retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-31-2024 09:00 AM
Hello @_Sumit_Kate
Not all databases support variable declaration in the same way. It is possible System.Data.Odbc
does not support it, in Databricks we use Simba ODBC drivers. But I would say let's check the official document too - https://learn.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcconnection.getschema
Just try adding the below config(s) to the connection string and see if it helps
-
EnableNativeParameterizedQuery=1
-
UseNativeQuery=2

