BUG-SQL datanode not working with Azure SQL Database

[FlorianJacta]

Description The problem raises when trying to connect to a Azure SQL database. This is the error: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][Gestionnaire de pilotes ODBC] Source de données introuvable et nom de pilote non spécifié (0) (SQLDriverConnect)')

How to correct it

• Change in SQLDataNode.py line 97 (__build_conn_string):

  return "mssql+pyodbc:///?odbc_connect=" + urllib.parse.quote_plus(
              f"DRIVER=FreeTDS;SERVER={host};PORT={port};DATABASE={database};UID={username};PWD={password};TDS_Version=8.0;"
          )

  ->

  return "mssql+pyodbc:///?odbc_connect=" + "DRIVER={ODBC Driver 18 for SQL Server};" + urllib.parse.quote_plus(
              f"SERVER={host};PORT={port};DATABASE={database};UID={username};")+f"PWD={password};TDS_Version=8.0;"

• Change the default port from 143 to 1433. so change in taipy.py in line 636 (configure_sql_data_node)

  db_port: int = 143,

  ->

  db_port: int = 1433,

How to improve it

• Make the 'db_host' parameter directly visible for the configure_sql_data_node function in taipy.py. Line 628-639

  def configure_sql_data_node(
  id: str,
  db_username: str,
  db_password: str,
  db_name: str,
  db_engine: str,
  read_query: str,
  write_table: str,
  db_port: int = 143,
  scope: Scope = DataNodeConfig._DEFAULT_SCOPE,
  **properties,
  ):

  ->

  def configure_sql_data_node(
  id: str,
  db_username: str,
  db_password: str,
  db_name: str,
  db_engine: str,
  read_query: str,
  write_table: str,
  db_port: int = 1433,
  db_host: str = 'localhost',
  scope: Scope = DataNodeConfig._DEFAULT_SCOPE,
  **properties,
  ):

• Make write_table optional if we don't want to write in it

How to reproduce Run the demo 'classification_csv_churn_v2' from 'taipy_0_13' branch present here: https://github.com/Avaiga/demos An error is raised because of the SQL datanode.

Expected behavior We shouldn't have an error.

Runtime environment Windows 11

[florian-vuillemot]

FreeTDS can also be. We should pass the driver directly as argument.