Tag: Extract API

  • How to write back data to Microsoft SQL Server?

    How to write back data to Microsoft SQL Server?

    Preface

    This document assumes you have fully installed XAMPP, MySQL and Tableau Extensions per this blog: https://tableaufans.com/extension-api/tableau-extension-api-write-back-updated-source-code-for-tableau-2018-2/

     

    Step 1: Create a New SQL Server User

    Log in to the SQL Server you want to setup for write back with SSMS.  Create a new User called ‘Tableau’ with the Password ‘Tableau’.

    Step 2:  Add Write Back Table to SQL Server

    While still in SSMS, add a Table for writing back to called ‘tbl_WriteBack’ to a database of your choice.  I used a database named ‘Test_Dynamic’ for my purposes.  The table as am [ID] integer with an identity and serial increase and 2 nvarchar(50) for [ColumnName] and [TextValue].

    Step 3:  Give the Tableau User CRUD Permissions to the tbl_WriteBack

    Use SSMS to update the permissions for the ‘Tableau’ user to be able to CRUD the new table created in Step 4.

    Step 4: Duplicate the MySQL Writeback Code

    Copy the Entire Directory C:\xampp\htdocs\extensions-api-master\myExtensions\Tableaufans-WriteBack-2018.2 to C:\xampp\htdocs\extensions-api-master\myExtensions\SQL Server

    Step 5: Create new TREX file

    Copy the Tableaufans-WriteBack-2018.2.trex file in C:\Users\dspezia\Documents\My Tableau Repository\Extensions to the same directory and rename it SQL Server Write Back.  Edit the TREX file to path to the correct write back JS and HTML.  Edit the <URL> Element to:

    <url>http://localhost:8888/extensions-api-master/myExtensions/SQL Server/index.html</url>

     

    Step 6: Install SQL SVR for PHP

    Get the latest SQL Server for PHP 7 drivers from https://docs.microsoft.com/en-us/sql/connect/php/step-1-configure-development-environment-for-php-development?view=sql-server-2017.  Unpack the SQL Server drivers to C:\xampp\php\ext by running the SQLSRV53.EXE.  You only need the php_pdo_sqlsrv_7_ts_x86.dll and php_sqlsrv_7_ts_x86.dll so you can delete the other sqlsrv dlls that were installed.

    Step 7: Update the PHP Settings

    Update php.ini to point to the new DLLS for SQL Server.  Update php.ini in C:\xampp\php to include the 2 new extensions.  I added them at lines 877 and 878.

    extension=php_pdo_sqlsrv_7_ts_x86.dll
extension=php_sqlsrv_7_ts_x86.dll.

    If you don’t do it write you will see “PHP Fatal error:  Uncaught Error: Call to undefined function sqlsrv_connect() in C:\\xampp…” in the apache error log in xampp C:\xampp\apache\logs\error.log

    Step 8: Update the PHP Code

    Update the writeToDb.php code found in C:\xampp\htdocs\extensions-api-master\myExtensions\SQL Server\php.  I set mine up like this:

    <?php

$customerName = $_POST['name'];
$clusterName = $_POST['cluster'];
$userInput = $_POST['userinput'];
$checkBox = $_POST['checkbox'];
$selectedMeasure = $_POST['measure'];

$serverName = "localhost";
$options = array( "UID"=>"Tableau", "PWD"=>"Tableau", "Database"=>"Test_Dynamic");
$conn = sqlsrv_connect($serverName, $options);

if( $conn === false )
     {
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
     }

$query = "INSERT INTO [dbo].[tbl_WriteBack] (ColumnName,TextValue) VALUES(?, ?)";
$params1 = array($customerName,$userInput);
$result = sqlsrv_query($conn,$query,$params1);

sqlsrv_close($conn);

?>

     

    Step 9: Update the JS Code

    You need to update the refresh code to update the new SQL Server Data Source.  Open the JS file MarksSelection.js in C:\xampp\htdocs\extensions-api-master\myExtensions\SQL Server.  And change the Refresh Section line 467 to 476 to this:

    var datasourceName = dashboardDataSources[dataSource.id].name;

                      if (dashboardDataSources[dataSource.id].name == "DatasourceSQL")  {

                             //refreshDataSource(dashboardDataSources[dataSource.id]);

                             //alert("id: " + dashboardDataSources[dataSource.id]);

                             //alert("dataSource.id: " + dataSource.id);

                             refreshDataSource(dashboardDataSources[dataSource.id]);

                             console.log("refreshSQL() was called for Datasource Name: 'DatasourceSQL'");

                      }



                     //refreshDataSource(dashboardDataSources["DatasourceSQL"]);

    Step 10: Update the TWB

    Duplicate the TWB for MySQL Write Back.

    Add a Data Source to the tbl_WriteBack named “DatasourceSQL”

    Add a Viz to Display Data from the new WriteBack Table.  Replace the Pipeline after Planning Chart with the new Viz and replace the Old Ext with your new Ext.

    Step 11: Have Fun!

    Start your MySQL and Apache Server in XAMPP and have fun!