MySQL, SQL Server, ODBC, DSN, Excel, PowerQuery

• MySQL and ODBC Install

  • On Server: Download and Install MySQL and Workbench

    MySQL download page with links to MySQL Installer for Windows, MySQL Workbench and various connectors including Connector/ODBC:

    MySQL Community
    MySQL Community: Downloads

    Go to the MySQL Community Server link on the Community: Downloads page for the Community 64-bit version of MySQL. We use the 64-bit MSI installer:

    Windows (x86, 64-bit), MSI Installer
    Reference article on Kinsta re: How to Set Up a MySQL Community Server on Your Machine.

    You also need MySQL Workbench, linked on the Downloads page:

    Windows (x86, 32 & 64-bit) MySQL Workbench Installer MSI

    Install MySQL Workbench on the Server computer, and on at least one Client computer that will be used for remote management.

  • On Client: Download and Install MySQL ODBC Connector

    Back to MySQL Community Download page to get ODBC connector:

    MySQL Community Downloads

    For the ODBC Connector We use the 64-bit MSI installer:

    Windows (x86, 64-bit) MSI Installer

    The ODBC Connector must be installed on each Client computer.

  • On Server: Using MySQL Workbench, add each User to MySQL allowed Remote Access.

    Permissions and roles like OpISuper; can also add users for OpIManager, OpIDataOwner, and OpIClient.

    Server Parameters:

    On Server, in MySQL Workbench > Navigator Pane > Administration > Users and Privileges:

    • In Users and Privileges pane > click Add Account to add a user e.g. PowerOpI Login Names OpISuper, OpIManager, OpIDataOwner, OpIClient.
    • All parameters as appropriate to PowerOpI Login Name and PowerOpI role. E.g., for Login Name OpISuper and values for role OpISuper:
      • Login Name: OpISuper; Authentication Type: caching_sha2_password; Limit to Hosts Matching: %; Password: <OpISuper password>;
      • Account Limits:Max Queries, Max Updates, Max Connections = 1000; Concurrent Connections = 40
      • Administrative Roles: DBA, MaintenanceAdmin, ProcessAdmin, UserAdmin, SecurityAdmin, DBManager, DBDesigner, ReplicationAdmin, BackupAdmin; Global Privileges: as needed, similar to SQLServerSecurity tool tblOpIRolesGRANT.
      • Schema Privileges: (for e.g., “Select “ALL””: Object Rights like CONTROL: SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW
      • DDL Rights: All, Other Rights: All

    Firewall:

    This may be set up by the MySQL installer, but check it:

    • MySQL Server
      C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe
      Port 3306, All Protocols, In/Out
    • MySQL Workbench
      Port 3306, TCP/UDP, In/Out
    • File Share
      SMB, CIFS: Port 445, TCP, In/Out
      RPC: Port 135, TCP, In/Out
      File/Print: Port 139, TCP, In/Out

  • On Client: Connect to MySQL

    Create connection to MySQL server.
    First, from Workbench (Local, then from separate Client)
    Second, from Excel PowerQuery
    Third, from an Excel Connection:

    • Connect from MySQL Workbench:
      • Create User
        • On Client MySQL Workbench: Navigator tab > Users and Privileges > Users and Privileges pane
        • In Users and Privileges pane > click Add Account to add a user e.g. PowerOpI Login Names OpISuper, OpIManager, OpIDataOwner, OpIClient.
        • All parameters as appropriate to PowerOpI Login Name and PowerOpI role. E.g., for Login Name OpISuper and values for role OpISuper:
          • Login Name: OpISuper; Authentication Type: caching_sha2_password; Limit to Hosts Matching: %; Password: <OpISuper password>;
          • Account Limits:Max Queries, Max Updates, Max Connections = 1000; Concurrent Connections = 40
          • Administrative Roles: DBA, MaintenanceAdmin, ProcessAdmin, UserAdmin, SecurityAdmin, DBManager, DBDesigner, ReplicationAdmin, BackupAdmin; Global Privileges: as needed, similar to SQLServerSecurity tool tblOpIRolesGRANT.
          • Schema Privileges: (for e.g., “Select “ALL””: Object Rights like CONTROL: SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW
          • DDL Rights: All, Other Rights: All
      • Create Connection
        • on Client MySQL Workbench Home pane > Click the "Plus Sign" just to right of MySQL Connections at left-center of pane.
          • Connection Name: like "COBALT-MySQL-OpISuper"
          • Connection Method: Standard (TCP/IP)
          • Parameters like
            • Hostname: COBALT, Port 3306
            • Username: OpISuper (use a PowerOpI username)
            • Password: Store in Vault… password for PowerOpI username
          • SSL: Use SSL: If available
        • Click button on MySQL Workbench Home pane, or right-click and choose Open Connection, to open the connection created.
    • Connect from Excel PowerQuery:
      • In Excel: Data tab > Get Data > From Database > From MySQL Database
        • Server: COBALT
        • Database: Use MySQL Schema name (e.g., "Sakila")
        • Advanced: SQL Query: (e.g. SELECT films.* FROM sakila.film_list AS films LIMIT 0, 1000; )
        • Login: Database > User name: OpISuper, Password OpISuper password
        • Select level to apply settings: cobalt (server name)
        • Load To: Table or Pivot etc

        • In Advanced Editor, M Code: example like

          let
              Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true, 
                  Query="SELECT films.* FROM sakila.film_list AS films LIMIT 0, 1000;", 
                  HierarchicalNavigation=true])
          in
              Source
          

        • Then use the PowerQuery Editor Load To... button to drive a PowerQuery Excel pivot report from MySQL. Note that this uses the PowerQuery data function MySQL.Database() which does not use a DSN. An alternative implementation could use Odbc.DataSource() which would use a DSN, or Odbc.Query() which would reference a DSN and contain an embedded SQL QUERY.
        • Alternatively, create a connection like MySQLTool\ToolClientRemote.xlsm qMySQL-FilmBuild that embeds the M-code, to drive a pivot report from MySQL. A connection string could reference a DSN, or embed connection parameters.

  • References

    • MySQL: ODBC Connection Parameters
    • MySQL: Connecting Without a Predefined DSN
    • MySQL: Creating a Connector/NET Connection String
    • SQL Server: Using Connection String Keywords with SQL Server Native Client
    • SQL Server: Microsoft ODBC Driver for SQL Server
    • SQL Server: DSN and Connection String Keywords and Attributes
• 
  

  ---

  Connection Paths

  In PowerOpI MySQLTool\ToolClientRemote.xlsm, working sample connection paths, each driving a pivot report, are illustrated. Setup includes connection strings, DSN file, SQL QUERY, Excel Connection.

  MySQLTool To use MySQLTool, you must first install it, and then verify or set up its configuration variables. MySQLTool can be downloaded from here. I suggest you install the tool in a directory at ..\Documents\PowerOpI\MySQLTool. By default the tool will be installed in a directory named MySQLTool. The tool must be replicated for each project, to a directory such as ..\Documents\PowerOpI\<projectName>.

  MySQLTool Configuration The configuration variables on MySQLTool\ToolClientRemote.xlsm and also ToolSource.xlsm will automatically reconfigure to reflect the installation location, but you must review and update the variables to reflect remote server locations, user names, schema names etc. on Excel tab "Variables". That page supports several profiles, chosen by the Choose Profile dropdown near cell C13. The default sets that to ProfileRemote2 to talk to a remote server in section Server2. Check and update those settings in column ProfileRemote2, and also check/update all settings in that column. The profile values are chosen into column ProfileSelect, and configure connection strings and SQL on tab DataSources.

  ODBC We connect to MySQL using ODBC; and ODBC can also be used to connect to SQL Server. Configuration examples are on provided in MySQLTool\ToolClientRemore.xlsm, described in the table below and following detail. Configurations for both MySQL and SQL Server are shown. To use ODBC, you must configure a DSN for each database and user Login. The DSN configuration steps are shown below in the detail descriptions for MySQL first, followed by descriptions for SQL Server.

  MySQL and SQL Server MySQLTool provides example connections for both MySQL and SQL Server database environments. You can enable or disable execution of these examples for one or the other or both databases, if you do not have both database environments installed.

  To disable examples for SQL Server: in ...MySQLTool\ToolClientRemote.xlsm, Table tblConnectionConfig, set field SkipBuildUpdate to Skip for the following SQL Server connections named in field ConnectionName: qPQM_SQLSvr_ ODBC_DSN, SQLServer-ODBC-DSN, and SQLServer-ODBC-Keywords. To re-enable, set field SkipBuildUpdate to Build.

  To disable examples for MySQL: in ...MySQLTool\ToolClientRemote.xlsm, Table tblConnectionConfig, set field SkipBuildUpdate to Skip for the following MySQL connections named in field ConnectionName: qMySQL-Films, qMySQL-FilmBuild, qMySQL-FilmODBC, and qMySQL-ODBC-Keywords-FilmODBC. To re-enable, set field SkipBuildUpdate to Build.

  Naming, Logins and Roles The convention used in these examples names each DSN as <databaseType (MySQL or SQLServer)><serverComputerName (COBALT in the examples)><DSN Scope (User,System, File)><UserLoginName>. The security page on this site describes default Roles set up for OpISuper, OpIManager, OpIDataOwner, and OpiClient; and creates Server Logins and Database Users by the same names. The MySQLTool examples use those names for both MySQL and SQL Server defaults, but once you have this setup working you should either modify passwords, or create your own Logins and disable or delete these defaults.

  List of Working Path Examples

  Method	Database	Query/Conn	DSN	Tab/Pivot	Query/Conn Name	Provider	SQLName
  ODBC	MySQL	PowerQuery	DSN: None	qMySQL-Films	qMySQL-Films	qMySQL-FilmsBuildPQM	(Not used in example)
  ODBC	MySQL	PowerQuery	DSN: None	qMySQL-FilmBuild	qMySQL-FilmBuild	qMySQL-FilmBuild	(Not used in example)
  ODBC	MySQL	Connection	DSN: External	qMySQL-FilmODBC	qMySQL-FilmODBC	MySQL-ODBC-DSN-OpISuper	qMySQL-FilmsBuildSQL
  ODBC	MySQL	Connection	DSN: External	qMySQL- Keywords-FilmODBC	qMySQL-ODBC-Keywords-FilmODBC	MySQL-ODBC-DSN-OpISuper	qMySQL-FilmsBuildSQL
  ODBC	SQLServer	PowerQuery	DSN: External	qPQM_SQLSvr_ ODBC_DSN	qPQM_SQLSvr_ ODBC_DSN	qPQM_SQLSvr_ ODBC_DSN	(Not used in example)
  ODBC	SQLServer	Connection	DSN: External	SQLServer-ODBC-DSN	SQLServer-ODBC-DSN	SQLServer-ODBC-DSN-OpISuper	qBuildPlan DBTables
  ODBC	SQLServer	Connection	DSN: Embedded	SQLServer-ODBC-Keywords	SQLServer-ODBC-Keywords	SQLServer-ODBC-Keywords-OpISuper	qBuildPlan DBTables

  
  

  ---

  Working Path Details

  • Create a DSN for each user of MySQL.

    • DSN for MySQL The DSN referenced is created in Windows tool ODBC Data Source Administrator (64-bit) (or 32-bit) from Windows Control Panel > All Control Panel Items >Administrative Tools > ODBC Data Sources (64-bit) or 32-bit, which can also be accessed at C:\Windows\System32\odbcad64.exe. You will need a DSN file set up for each MySQL Login User to be granted access to MySQL, and the DSN files must be distributed to client computers. Alternatively, you can use the ODBC Data Sources on client computers to set them up. When creating, set configuration values pertinent to the specified user, like these examples using default Login etc.:

      System DSN (System Tab) > Add...:

      Driver: MySQL 8.0 Unicode Driver

      Data Source Name: MySQL8U-COBALT-OpISuper-Sys

      Description: As you like

      TCP/IP Server: (choose button) COBALT Port: 3306

      User: OpISuper

      Password: abcd%12

      Database: sakila

      Connection "Details>>": if any are needed

      Click "Test" button and if it works, "OK" button.

    The "DSN: None" configuration below using PowerQuery DSN/ODBC mashup access won't need a DSN.

  • 
    

    ---

  • ODBC - MySQL - PowerQuery - DSN: None

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: qMySQL-Films

    tblConnectionConfig.ConnectionName = qMySQL-Films
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.qMySQL-FilmsPQM

    tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

    tblConnection.qMySQL-FilmsPQM =

    //  PowerQuery, M, Embedded SQL, 
    // SQL is inserted from tblSQL by name, via XLOOKUP. 
    let 
        Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true, 
            Query="/* Demo Query MySQL Table sakila.film_list */ 
    SELECT films.* 
    FROM sakila.film_list AS films 
    LIMIT 0, 1000;", HierarchicalNavigation=true]) 
    in 
        Source
    

  • 
    

    ---

  • ODBC - MySQL - PowerQuery - DSN: None

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: qMySQL-FilmBuild

    tblConnectionConfig.ConnectionName = qMySQL-FilmBuild
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.qMySQL-FilmBuild

    tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

    tblConnection.qMySQL-FilmBuild =

    let 
        Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true]), 
        sakila_film = Source{[Schema="sakila",Item="film"]}[Data], 
        sakila_film_category = Source{[Schema="sakila",Item="film_category"]}[Data], 
        sakila_film_actor = Source{[Schema="sakila",Item="film_actor"]}[Data], 
        sakila_actor = Source{[Schema="sakila",Item="actor"]}[Data], 
     
        sakila_film_renamedColumns = Table.RenameColumns(sakila_film,{{"film_id", 
            "film_film_id"}, {"last_update", "film_last_update"}}),  
        sakila_film_category_renamedColumns = Table.RenameColumns(sakila_film_category,
            {{"film_id", "category_film_id"},{"last_update","category_last_update"}, 
            {"sakila.film", "category_sakila.film"}}),  
        sakila_film_actor_renamedColumns = Table.RenameColumns(sakila_film_actor,
            {{"film_id", "film_actor_film_id"},{"last_update","film_actor_last_update"}}),  
        sakila_actor_renamedColumns = Table.RenameColumns(sakila_actor,{{"actor_id", 
            "actor_actor_id"},{"last_update","actor_last_update"},{"sakila.film_actor",
            "sakila.actor_actor"}}),  
      
        film_category_join = Table.Join(sakila_film_renamedColumns,{"film_film_id"},
            sakila_film_category_renamedColumns,{"category_film_id"},JoinKind.LeftOuter),  
        film_actor_join = Table.Join(film_category_join,{"category_film_id"},
            sakila_film_actor_renamedColumns,{"film_actor_film_id"},JoinKind.LeftOuter),  
        film_actor_name_join =       Table.Join(film_actor_join,{"actor_id"},
            sakila_actor_renamedColumns,{"actor_actor_id"},JoinKind.LeftOuter)  
      
    in  
        film_actor_name_join
    

  • 
    

    ---

  • ODBC - MySQL - Connection - DSN: External

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: qMySQL-FilmODBC

    tblConnectionConfig.ConnectionName = qMySQL-FilmODBC
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.MySQL-ODBC-DSN-OpISuper

    tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection)

    tblConnectionConfig.SQLName = tblSQL.qMySQL-FilmsBuildSQL

    tblConnection.MySQL-ODBC-DSN-OpISuper =

    DSN=MySQL8U-COBALT-OpISuper-Sys;
    

    tblSQL.qMySQL-FilmsBuildSQL =

    /* Demo Query MySQL Table sakila.film_list */
    SELECT films.*
    FROM sakila.film_list AS films
    LIMIT 0, 1000;
    

  • 
    

    ---

  • ODBC - MySQL - Connection - DSN: External

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: qMySQL-Keywords-FilmODBC

    tblConnectionConfig.ConnectionName = qMySQL-ODBC-Keywords-FilmODBC
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.MySQL-ODBC-DSN-OpISuper

    tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection)

    tblConnectionConfig.SQLName: tblSQL.qMySQL-FilmsBuildSQL

    tblConnection.MySQL-ODBC-DSN-OpISuper =

    DSN=MySQL8U-COBALT-OpISuper-Sys;
    

    tblSQL.qMySQL-FilmsBuildSQL =

    /* Demo Query MySQL Table sakila.film_list */
    SELECT films.*
    FROM sakila.film_list AS films
    LIMIT 0, 1000;
    

  • 
    

    ---

  • Create a DSN for each user who accesses SQL Server via ODBC.

    SQL Server First, to run the following examples of access to SQL Server via ODBC, an instance of SQL Server must be installed and working on a server computer, and a database must be configured within that server consistent with parameters in the following examples. The default settings in MySQLTool connect to [Cobalt\SQLExpress].[SQLServerRemoteDB].[dbo].<tablename>, by Server Login OpISuper with password abcd%12 as shown in MySQLTool > Tab Variables. Server Name could be expressed as IP Address e.g. 192.168.1.280. Installation and configuration instructions for SQL Server are available on the PowerOpI PowerQueryTool page and can be modified there to reflect your configurations of Server Name, Database Name, Login etc. Instruction for setting up security and logins on the PowerOpI Database Security page.

    Install or update ODBC driver ODBC Driver 18 for SQL Server as needed from Microsoft ODBC Driver page.

    Create DSNs for SQL Server Logins. The DSN referenced is created in Windows tool ODBC Data Source Administrator (64-bit) (or 32-bit) from Windows Control Panel > All Control Panel Items > Administrative Tools > ODBC Data Sources (64-bit) or 32-bit, which can also be accessed as C:\Windows\System32\odbcad64.exe. You will need a DSN file set up for each Login User to be granted access to SQL Server via ODBC, and the DSN files must be distributed to client computers. Alternatively, you can use the ODBC Data Sources on client computers to set it up. When creating, set configuration values pertinent to the specified user, like the following examples using defaults:

    • System DSN: SQLSV18-COBALT-OpISuper-Sys, Driver: ODBC Driver 18 for SQL Server. Add, and Configure...
    • Name: SQLSV18-COBALT-OpISuper-Sys
    • Description: SQLSV18-COBALT-OpISuper-Sys (used in Excel connection string, in code above)
    • Server: COBALT\SQLEXPRESS
    • LoginID: Choose "With SQL Server authentication using a login ID and password entered by the user"
    • Login ID: OpISuper
    • Password: <LoginID password>
    • Checkmark: Use ANSI quoted identifiers
    • Checkmark: Use ANSI nulls, paddings and warnings
    • Application intent: READWRITE
    • Checkmark: Transparent Network IP Resolution
    • Connection Encryption: Optional
    • Checkmark: Trust server certificate
    • Checkmark: Perform translation for character data
    • Connect retry count: 1
    • Connect retry interval (seconds): 10

    The "DSN: Embedded" configuration won't need a DSN but will need DSN values to be set up in ToolClientRemote.xlsm > Data Sources tab > tblConnection column SQLServer-ODBC-Keywords-OpISuper, to set up embedded DSN.

  • 
    

    ---

  • ODBC - SQLServer - PowerQuery - DSN: External

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: qPQM_SQLSvr_ODBC_DSN

    tblConnectionConfig.ConnectionName = qPQM_SQLSvr_ODBC_DSN
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.qPQM_SQLSvr_ODBC_DSN

    tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

    tblConnection.qPQM_SQLSvr_ODBC_DSN =

    let
        Source = Odbc.DataSource("dsn=SQLSV18-COBALT-OpISuper-Sys", 
            [HierarchicalNavigation=true]),
        SQLServerRemoteDB_Database = Source{[Name="SQLServerRemoteDB",
            Kind="Database"]}[Data],
        dbo_Schema = SQLServerRemoteDB_Database{[Name="dbo",Kind="Schema"]}[Data],
    
        xlAlloc = dbo_Schema{[Name="xlAlloc",Kind="Table"]}[Data],
        xlBOM = dbo_Schema{[Name="xlBOM",Kind="Table"]}[Data], 
        xlDates = dbo_Schema{[Name="xlDates",Kind="Table"]}[Data], 
        xlBuilds = dbo_Schema{[Name="xlBuilds",Kind="Table"]}[Data], 
        xlElements = dbo_Schema{[Name="xlElements",Kind="Table"]}[Data], 
        xlPR = dbo_Schema{[Name="xlPR",Kind="Table"]}[Data], 
    
        AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},
            JoinKind.LeftOuter), 
        ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},
            JoinKind.LeftOuter), 
        ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},
            JoinKind.LeftOuter), 
        ABDBE=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},
            JoinKind.LeftOuter), 
        ABDBEP=Table.Join(ABDBE,{"akPurchReq"},xlPR,{"prPurchReq"},
            JoinKind.LeftOuter), 
     
        ABDBEPQ=Table.AddColumn(ABDBEP,"QtyExt", each [akQty] * [bmSubQty]), 
        ABDBEPQC=Table.AddColumn(ABDBEPQ,"CostExt", each [QtyExt] * [bmSubCost])
     
    in 
        ABDBEPQC
    

  • 
    

    ---

  • ODBC - SQL Server - Connection - DSN: External

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: SQLServer-ODBC-DSN

    tblConnectionConfig.ConnectionName = SQLServer-ODBC-DSN
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.SQLServer-ODBC-DSN-OpISuper

    tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection.)

    tblConnectionConfig.SQLName = tblSQL.qBuildPlanDBTables

    tblConnection.SQLServer-ODBC-DSN-OpISuper =

    ODBC;
    DSN=SQLSV18-COBALT-OpISuper-Sys;
    

    tblSQL.qBuildPlanDBTables =

    /* QUERY Build Plan DB Tables */
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    
      SELECT [SQLServerRemoteDB].[dbo].[xlAlloc].*, [SQLServerRemoteDB].[dbo].[xlBOM].*, 
        [SQLServerRemoteDB].[dbo].[xlDates].*, [SQLServerRemoteDB].[dbo].[xlBuilds].*, 
        [SQLServerRemoteDB].[dbo].[xlElements].*, [SQLServerRemoteDB].[dbo].[xlPR].*, 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] 
            AS QtyExt, 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubCost] AS CostExt
      FROM [SQLServerRemoteDB].[dbo].[xlAlloc]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBOM] ON 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akAllocRevKey] = 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyIn]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlDates] ON 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyOut] = 
        [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBuilds] ON 
        [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey] = 
        [SQLServerRemoteDB].[dbo].[xlBuilds].[bdBuildRevKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlElements] ON 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKey] = 
        [SQLServerRemoteDB].[dbo].[xlElements].[emElementItemKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlPR] ON 
        CAST([SQLServerRemoteDB].[dbo].[xlAlloc].[akPurchReq] AS VARCHAR) = 
        CAST([SQLServerRemoteDB].[dbo].[xlPR].[prPurchReq] AS VARCHAR);
    

  • 
    

    ---

  • ODBC - SQLServer - Connection - DSN: Embedded

    Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

    Tab / Pivot Table: SQLServer-ODBC-Keywords

    tblConnectionConfig.ConnectionName = SQLServer-ODBC-Keywords
    (Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

    tblConnectionConfig.Provider = tblConnection.SQLServer-ODBC-Keywords-OpISuper

    tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection.)

    tblConnectionConfig.SQLName = tblSQL.qBuildPlanDBTables

    tblConnection.SQLServer-ODBC-Keywords-OpISuper =

    ODBC;
    DRIVER=ODBC Driver 18 for SQL Server;
    SERVER=COBALT\SQLEXPRESS;
    WSID=TRILITHIUM;
    UID=OpISuper;
    PWD=abcd%12;
    Encrypt=Optional;
    Trusted_Connection=No;
    TrustServerCertificate=Yes;
    APP=Microsoft® Windows® Operating System;
    

    Values above separated to multiple lines for readability only. To use the above as connection string, concatenate all to a single string. Separators are ";" NO CRLF or space:

    ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=COBALT\SQLEXPRESS;WSID=TRILITHIUM; UID=OpISuper;PWD=abcd%12;Encrypt=Optional;Trusted_Connection=No; TrustServerCertificate=Yes; APP=Microsoft® Windows® Operating System;

    tblSQL.qBuildPlanDBTables =

    /* QUERY Build Plan DB Tables */
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    
      SELECT [SQLServerRemoteDB].[dbo].[xlAlloc].*, [SQLServerRemoteDB].[dbo].[xlBOM].*, 
        [SQLServerRemoteDB].[dbo].[xlDates].*, [SQLServerRemoteDB].[dbo].[xlBuilds].*, 
        [SQLServerRemoteDB].[dbo].[xlElements].*, [SQLServerRemoteDB].[dbo].[xlPR].*, 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] 
            AS QtyExt, 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] * 
            [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubCost] AS CostExt
      FROM [SQLServerRemoteDB].[dbo].[xlAlloc]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBOM] ON 
        [SQLServerRemoteDB].[dbo].[xlAlloc].[akAllocRevKey] = 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyIn]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlDates] ON 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyOut] = 
        [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBuilds] ON 
        [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey] = 
        [SQLServerRemoteDB].[dbo].[xlBuilds].[bdBuildRevKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlElements] ON 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKey] = 
        [SQLServerRemoteDB].[dbo].[xlElements].[emElementItemKey]
      LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlPR] ON 
        CAST([SQLServerRemoteDB].[dbo].[xlAlloc].[akPurchReq] AS VARCHAR) = 
        CAST([SQLServerRemoteDB].[dbo].[xlPR].[prPurchReq] AS VARCHAR);
    

• 
  

  ---

  Differences in SQL for MySQL vs. SQL Server

  Character Set There are numerous character set use differences between SQL Server and MySQL. Character set specification is a broad topic, so here we will limit discussion to differences to SQL strings created by PowerOpI tools to push tables or manage security.

  • Scripts for SQL Server specify entities enclosed in [square brackets] to protect use of special characters: [<databaseName>].[<schemaName>].[<tableName>].[<fieldName>]. MySQL drops [brackets] and specifies as <schemaName>.<tableName>.<fieldName>.
  • The following PowerOpI SQL Server syntax is not used in PowerOpI scripts for MySQL:
    • Drops SET ANSI NULLS
    • Drops SET QUOTED IDENTIFIER
    • Drops ON PRIMARY in CREATE TABLES
    • Drops RETURN; from all scripts
  • SQL Server CREATE OR ALTER VIEW in MySQL is CREATE OR REPLACE VIEW
  • MySQL uses CHAR instead of NVARCHAR or VARCHAR
  • Special characters are not used in MySQL column names: # is replaced by num, - and space characters by _.
  • MySQL DATETIME value format is YYYY-MM-DD vs SQL Server MM/DD/YYYY.
  • PowerOpI script names include specification of the targeted database type: e.g. TablesPushSQLServer-Create Tables.sql, TablesPushMySQL-Insert Values.sql.

  PowerOpI tools (ToolSource.xlsm et al.) provide an Excel function strQuoteName(<string> | <stringReference> [,<bracketChar> | <bracketCharReference>]), to replicate the equivalent SQL function, for use in writing SQL in Excel PowerOpI tools.

  The argument string and character references follow all the conventions for strings and string references, including use of " characters, and " characters within quoted string values. The optional bracketChar argument specifies the quote character to be used around the string argument. If left out, the default character will be used; that character is specified on tab Variables in PowerOpI *.xlsm tools, in the cell named strDefaultQuoteChar. If that bracketChar, or bracketCharReference, is specified as any one of ()[]{}<>, its partner character is used appropriately to bracket the string being quoted.

  Server Containerization Structure SQL Server provides a multi-level container structure within each SQL Server instance: [<serverName>].[<databaseName>].[<schemaName>].[<tableName>].[<fieldName>] where [<schemaName>] provides ability to sub-group tables within a database, and providing security permissions at the sub-group [<schemaName>]-level on visibility, access, update, etc.

  MySQL provides one less level of container structure: <serverName>.<schemaName>.<tableName>.<fieldName>. Terms <databaseName> and <schemaName> are interchangeable: there is no sub-grouping structure within a <databaseName> (or equivalently, a <schemaName>). So MySQL name references omit that part. Consequently there is one less level of object security specification.

  In the example SQL below I've made a table.field reference as SQLServerRemoteDB.xlBOM.bmSubQty because of SQLServer habits, but it could just as easily been called SQLServerRemoteSchema.xlBOM.bmSubQty. So name structure in MySQL is <schemaName>.<tableName>.<fieldName> with <schemaName> providing the functionality referred to in PowerOpI Tools as [<databaseName>].

  The Sakila database in the following example is included in the MySQL Workbench, and is followed by a QUERY discussed throughout this website for PowerOpI with SQL Server, PowerQuery and MSAccess, this time translated for use with MySQL.

  /* Demo Query MySQL Table sakila.film_list */
  SELECT films.*
  FROM sakila.film_list AS films
  LIMIT 0, 1000;
  
  /*
      ----------------------------------------------------------------------------
      ... or, a PM example...
  */
  
  /* QUERY Build Plan DB Tables */
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  
  SELECT SQLServerRemoteDB.xlAlloc.*, SQLServerRemoteDB.xlBOM.*, 
    SQLServerRemoteDB.xlDates.*, SQLServerRemoteDB.xlBuilds.*, 
    SQLServerRemoteDB.xlElements.*, SQLServerRemoteDB.xlPR.*, 
    SQLServerRemoteDB.xlAlloc.akQty 
      * SQLServerRemoteDB.xlBOM.bmSubQty AS QtyExt,
    SQLServerRemoteDB.xlAlloc.akQty * SQLServerRemoteDB.xlBOM.bmSubQty 
      * SQLServerRemoteDB.xlBOM.bmSubCost AS CostExt
  FROM SQLServerRemoteDB.xlAlloc
  LEFT OUTER JOIN SQLServerRemoteDB.xlBOM 
    ON SQLServerRemoteDB.xlAlloc.akAllocRevKey 
      = SQLServerRemoteDB.xlBOM.bmBOMItemKeyIn
  LEFT OUTER JOIN SQLServerRemoteDB.xlDates 
    ON SQLServerRemoteDB.xlBOM.bmBOMItemKeyOut 
      = SQLServerRemoteDB.xlDates.daSchedItemRevKey
  LEFT OUTER JOIN SQLServerRemoteDB.xlBuilds 
    ON SQLServerRemoteDB.xlDates.daSchedItemRevKey 
      = SQLServerRemoteDB.xlBuilds.bdBuildRevKey
  LEFT OUTER JOIN SQLServerRemoteDB.xlElements 
    ON SQLServerRemoteDB.xlBOM.bmBOMItemKey 
      = SQLServerRemoteDB.xlElements.emElementItemKey
  LEFT OUTER JOIN SQLServerRemoteDB.xlPR 
    ON CAST(SQLServerRemoteDB.xlAlloc.akPurchReq AS VARCHAR) 
      = CAST(SQLServerRemoteDB.xlPR.prPurchReq AS VARCHAR);