Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C#

_Sumit_Kate — Wed, 30 Oct 2024 11:47:54 GMT

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:

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_core__user_name_last2

,users_core__user_name_last3

)

OR (users_core359_96.`user_name_last` LIKE users_core__user_name_last4) LIMIT 1000

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)

Re: Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C#

NandiniN — Thu, 31 Oct 2024 16:00:29 GMT

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

topic Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C# in Warehousing & Analytics

Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C#

Re: Getting PARSE_SYNTAX_ERROR for DECLARE VARIABLE statement in ODBC C#