Client applications

How to work with SQL Server data in some example applications and programming languages:

Microsoft Access

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Access.

  2. Configure an ODBC data source.

  3. Choose one of the following ways to work with your SQL Server data in Access.

Linking a table

  1. Open your Microsoft Access database.

  2. Choose External Data.

  3. In the New Data Source list, choose From Other Sources > ODBC Database.

  4. In the Get External Data screen, choose Link to the data source by creating a linked table, and choose OK.

  5. In the Select Data Source dialog box, choose the Machine Data Source tab.

  6. Choose your Easysoft ODBC-SQL Server Driver ODBC data source from the Machine Data Source list, and then choose OK.

  7. In the Link Tables dialog box, choose the tables that you want to link to, and then choose OK.

Importing a table

  1. Open your Microsoft Access database.

  2. Choose External Data.

  3. In the New Data Source list, choose From Other Sources > ODBC Database.

  4. In the Get External Data screen, choose Import the source data into a new table in the current database, and choose OK.

  5. In the Select Data Source dialog box, choose the Machine Data Source tab.

  6. Choose your Easysoft ODBC-SQL Server Driver ODBC data source from the Machine Data Source list, and then choose OK.

  7. In the Import Objects dialog box, choose the tables you want to import, and then choose OK.

Microsoft Excel

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Excel.

  2. Configure an ODBC data source.

  3. Choose one of the following ways to work with your SQL Server data in Excel.

Data Connection Wizard

  1. Choose Data > Get Data > From Other Sources > From ODBC.

  2. Choose your Easysoft ODBC-SQL Server Driver data source from the list, and then choose OK.

  3. Enter the user name and password for your data store if applicable, otherwise, enter any text string to get past this stage. Choose Next.

  4. Choose the table that contains the data you want to retrieve, and then choose Load.

Microsoft Query

  1. Choose Data > Get Data > From Other Sources > From Microsoft Query.

  2. In the Choose Data Source dialog box, choose your SQL Server data source from the list, and then choose OK.

  3. In the Query Wizard, choose the columns that contain the data you want to retrieve, and then click Next.

  4. If you want to return a subset of the data, use the Filter Data screen to filter the results of your query (this is the equivalent of a SQL WHERE clause), and then choose Next.

  5. If you want to change the sort order of your data, use the Sort Order screen to sort the results of your query (this is the equivalent of a SQL ORDER BY clause), and then choose Next. Choose Finish to return your SQL Server data to Excel.

PowerPivot

  1. On the PowerPivot tab, choose Manage.

  2. In the PowerPivot window, choose Get External Data > From Other Sources.

  3. In the Connect to a Data Source list, choose Others (OLEDB/ODBC)

  4. In the Specify a Connection screen, enter a name for your connection in the space provided. Then choose Build.

  5. In the Data Link Properties box, choose your Easysoft ODBC-SQL Server Driver data source from the list, and then choose OK.

  6. Choose Next.

  7. Choose how to import your SQL Server data and then choose Finish.

  8. Choose Close to return the data to Excel.

Microsoft Power BI

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Power BI Desktop.

  2. Configure an ODBC data source.

  3. In Power BI Desktop, choose Get data from another source.

  4. In the Get Data dialog box, choose ODBC, and then choose Connect.

  5. In the From ODBC dialog box, choose your SQL Server data source, and then choose OK.

  6. Enter your database user name and password when prompted.

    If you make a mistake when entering the user name and password, cancel the connection process. Then in Power BI Desktop Options and Settings, edit the data source. Specify the correct user name or password in the data source credentials dialog box. Otherwise, Power BI Desktop will continue to use the cached incorrect credentials.

    If you do not normally need to enter a user name and password, enter some dummy strings in the spaces provided.
  7. In the Navigator dialog box, choose the tables you want to analyse in Power BI Desktop, and then choose Load.

    Your SQL Server data is now available to use in Power BI visualisations.

Oracle

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Oracle.

  2. Configure an ODBC data source.

  3. Follow the instructions for your Oracle platform.

Connecting SQL Server to Oracle on Windows

  1. Create a DG4ODBC init file on your Oracle machine. To do this, change to the %ORACLE_HOME%\hs\admin directory. Create a copy of the file initdg4odbc.ora. Name the new file initMSSQL.ora.

    In these instructions, replace %ORACLE_HOME% with the location of your Oracle HOME directory. For example, C:\app\product\21c\homes\OraDB21Home1.
  2. Ensure these parameters and values are present in your init file:

    HS_FDS_CONNECT_INFO = "SQL Server"

    Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.

  3. Comment out the line that enables DG4ODBC tracing. For example:

    #HS_FDS_TRACE_LEVEL = <trace_level>
  4. Add an entry to %ORACLE_HOME%\network\admin\listener.ora that creates a SID_NAME for DG4ODBC. For example:

    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC=
         (SID_NAME=MSSQL)
         (ORACLE_HOME=%ORACLE_HOME%)
         (PROGRAM=dg4odbc)
       )
     )
  5. Add a DG4ODBC entry to %ORACLE_HOME%\network\admin\tnsnames.ora that specifies the SID_NAME created in the previous step. For example:

    MSSQL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
        (CONNECT_DATA =
          (SID = MSSQL)
        )
        (HS = OK)
      )

    Replace oracle_host with the host name of your Oracle machine.

  6. Start (or restart) the Oracle Listener:

    cd %ORACLE_HOME%\bin
    lsnrctl stop
    lsnrctl start
  7. Connect to your Oracle database in SQL*Plus.

  8. In SQL*Plus, create a database link for SQL Server. For example:

    CREATE PUBLIC DATABASE LINK MSSQLLink
        CONNECT TO "dbuser" IDENTIFIED BY "dbpassword"
        USING 'MSSQL';

    Replace dbuser and dbpassword with your backend user name and password, if applicable.

  9. Try querying and updating your SQL Server data. For example:

    SELECT "Surname" FROM "Customers"@MSSQLLink;
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')');
    END;
    /
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('UPDATE "Customers" SET "Surname" = ''Jones'' WHERE "CompanyName" = ''PowerGroup''');
    END;
    /
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('DELETE from "Customers" WHERE CompanyName = ''PowerGroup''');
    END;
    /

Notes

  • If you have problems connecting to SQL Server from Oracle, enable DG4ODBC tracing and check the trace files written to the %ORACLE_HOME%\hs\trace directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initSQL Server.ora and then start or restart the Oracle listener. If the trace directory does not exist, create it.

  • If you enable ODBC Driver Manager tracing, but do not get a log file in the location you specify, try looking in the top-level folder (for example, C:\SQL.log). Alternatively, in ODBC Data Source Administrator, change the trace file location to the Windows TEMP directory.

Connecting SQL Server to Oracle on Linux and UNIX

  1. Create a DG4ODBC init file on your Oracle machine. To do this, change to the $ORACLE_HOME\hs\admin directory. Create a copy of the file initdg4odbc.ora. Name the new file initMSSQL.ora.

    In these instructions, replace $ORACLE_HOME with the location of your Oracle HOME directory. For example, /u01/app/oracle/product/21c/dbhome_1.
  2. Ensure these parameters and values are present in your init file:

    HS_FDS_CONNECT_INFO = "SQL Server"

    Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.

  3. Comment out the line that enables DG4ODBC tracing. For example:

    #HS_FDS_TRACE_LEVEL = <trace_level>
  4. Add an entry to $ORACLE_HOME/network/admin/listener.ora that creates a SID_NAME for DG4ODBC. For example:

    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC=
         (SID_NAME=MSSQL)
         (ORACLE_HOME=$ORACLE_HOME)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
                /usr/local/easysoft/lib)
       )
     )

    Replace oracle_home_directory with the value of $ORACLE_HOME. For example, /u01/app/oracle/product/21c/dbhome_1.

  5. Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example:

    MSSQL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
        (CONNECT_DATA =
          (SID = MSSQL)
        )
        (HS = OK)
      )

    Replace oracle_host with the host name of your Oracle machine.

  6. Start (or restart) the Oracle Listener:

    cd $ORACLE_HOME/bin
    ./lsnrctl stop
    ./lsnrctl start
  7. Connect to your Oracle database in SQL*Plus.

  8. In SQL*Plus, create a database link for SQL Server. For example:

    CREATE PUBLIC DATABASE LINK MSSQLLink
        CONNECT TO "dbuser" IDENTIFIED BY "dbpassword"
        USING 'MSSQL';

    Replace dbuser and dbpassword with your backend user name and password, if applicable.

  9. Try querying and updating your SQL Server data. For example:

    SELECT "Surname" FROM "Customers"@MSSQLLink;
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')');
    END;
    /
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('UPDATE "Customers" SET "Surname" = ''Jones'' WHERE "CompanyName" = ''PowerGroup''');
    END;
    /
    
    DECLARE
      num_rows integer;
    BEGIN
    num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSSQLLink
    ('DELETE from "Customers" WHERE CompanyName = ''PowerGroup''');
    END;
    /

Notes

  • If you have problems connecting to SQL Server from Oracle, enable DG4ODBC tracing and check the trace files written to the $ORACLE_HOME/hs/trace directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initSQL Server.ora and then start or restart the Oracle listener. If the trace directory does not exist, create it.

LibreOffice

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as LibreOffice.

  2. Configure an ODBC data source.

  3. Choose File > New > Database.

  4. Choose Connect to an existing database.

  5. Choose ODBC in the list, and then choose Next.

  6. Choose Browse, double-click your data source, and then choose Next.

  7. If your database requires a database user name, enter it in the User name box. If this user needs to supply a password choose the Password required check box.

  8. Choose Finish.

  9. Save the database when prompted.

    The database opens in a new Base window. From here you can access your data.

  10. In the left pane of the database window, choose the Tables icon to display a hierarchy of tables. Enter the database password if prompted, and then choose OK.

  11. To retrieve the data in a table, in the Tables pane, double-click a table.

  12. Choose the Queries icon to create a query.

    Use any of the methods listed in the Tasks pane to create a query.

Go

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Go.

  2. Configure an ODBC data source.

  3. Install the odbc package for Go:

    go mod init test
    go get github.com/alexbrainman/odbc
  4. Create and then use Go to run this script, which retrieves some SQL Server data:

    package main
    
    import (
    	_ "github.com/alexbrainman/odbc"
    	"database/sql"
            "log"
    )
    
    func main() {
            // Replace the DSN value with the name of your ODBC data source.
            db, err := sql.Open("odbc",
                    "DSN=SQL Server")
            if err != nil {
                    log.Fatal(err)
            }
    
            var (
                    name string
            )
    
            rows, err := db.Query("SELECT Surname FROM Customers")
            if err != nil {
                    log.Fatal(err)
            }
            defer rows.Close()
            for rows.Next() {
                    err := rows.Scan(&name)
                    if err != nil {
                            log.Fatal(err)
                    }
                    log.Println(name)
            }
            err = rows.Err()
            if err != nil {
                    log.Fatal(err)
            }
    
            defer db.Close()
    }

Node.js

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Node.js.

  2. Configure an ODBC data source.

  3. Install the odbc module for Node.js:

    npm install odbc
  4. Create and then use Node.js to run this script, which retrieves some SQL Server data:

    const odbc = require('odbc');
    // Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver
    // data source.
    const connection = odbc.connect('DSN=SQL Server', (error, connection) => {
        connection.query('SELECT Surname FROM Customers', (error, result) => {
            if (error) { console.error(error) }
            console.log(result);
        });
    });
  5. This script retrieves the tables and views in your Easysoft ODBC-SQL Server Driver data source:

    const odbc = require('odbc');
    const connection = odbc.connect('DSN=SQL Server', (error, connection) => {
        connection.tables(null, null, null, null, (error, result) => {
            if (error) { return; }
            const util = require('util');
            console.log(util.inspect(result, {maxArrayLength: null, depth:null}))
        });
    });
  6. This script retrieves the names of the columns in these tables and views:

    const odbc = require('odbc');
    const connection = odbc.connect('DSN=SQL Server', (error, connection) => {
        connection.columns(null, null, null, null, (error, result) => {
            if (error) { return; }
            const util = require('util');
            console.log(util.inspect(result, {maxArrayLength: null, depth:null}))
        });
    });
  7. These scripts insert, update, and then delete some SQL Server data:

    const odbc = require("odbc");
    const connection = odbc.connect("DSN=SQL Server", (error, connection) => {
        connection.query("INSERT INTO
        Customers (
            Surname,
            GivenName,
            City,
            Phone,
            CompanyName
        )
    VALUES
        (
            'Devlin',
            'Michaels',
            'Kingston',
            '2015558966',
            'PowerGroup'
        )", (error, result) => {
            if (error) { console.error(error) }
            console.log(result);
        });
    });
    
    const odbc = require("odbc");
    const connection = odbc.connect("DSN=SQL Server", (error, connection) => {
        connection.query("UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = 'PowerGroup'", (error, result) => {
            if (error) { console.error(error) }
            console.log(result);
        });
    });
    
    const odbc = require("odbc");
    const connection = odbc.connect("DSN=SQL Server", (error, connection) => {
        connection.query("DELETE FROM Customers WHERE CompanyName = 'PowerGroup'", (error, result) => {
            if (error) { console.error(error) }
            console.log(result);
        });
    });

Perl

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Perl.

  2. Configure an ODBC data source.

  3. Check whether your Perl distribution supports ODBC:

    perl -e 'use DBD::ODBC;'
  4. Do one of the following:

    • If you get no output, your Perl distribution supports ODBC. Skip to the next step.

    • If you get:

      Can't locate DBD/ODBC.pm

      you need to install DBD::ODBC before you can use the Easysoft ODBC-SQL Server Driver to connect to SQL Server.

  5. Create and then use Perl to run this script, which retrieves some SQL Server data:

    use strict;
    use DBI;
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sql = "SELECT Surname FROM Customers";
    
    my $sth = $dbh->prepare($sql)
        or die "Can't prepare statement: $DBI::errstr";
    
    $sth->execute();
    
    my($Col);
    
    # Fetch and display the result set values.
    while(($Col) = $sth->fetchrow()){
       print("$Col\n");
    }
    
    $dbh->disconnect if ($dbh);
  6. This script retrieves the tables and views in your Easysoft ODBC-SQL Server Driver data source:

    use strict;
    use DBI;
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sth = $dbh->table_info()
        or die "Can't prepare statement: $DBI::errstr";
    
    my @row;
    
    while (@row = $sth->fetchrow_array) {
        print join(", ", @row), "\n";
    }
    
    $dbh->disconnect if ($dbh);
  7. This script retrieves the names of the columns in these tables and views:

    use strict;
    use DBI;
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sth = $dbh->column_info('','','','')
        or die "Can't prepare statement: $DBI::errstr";
    
    my @row;
    while (@row = $sth->fetchrow_array) {
        print join(", ", @row), "\n";
    }
    
    $dbh->disconnect if ($dbh);
  8. These scripts insert, update, and then delete some SQL Server data:

    use strict;
    use DBI;
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sth = $dbh->prepare(q/INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)/)
        or die "Can't prepare statement: $DBI::errstr";
    
    $sth->execute('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup');
    
    $dbh->disconnect if ($dbh);
    
    use strict;
    use DBI;
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sth = $dbh->prepare('UPDATE Customers SET Surname = \'Jones\' WHERE CompanyName = ?')
        or die "Can't prepare statement: $DBI::errstr";
    
    $sth->execute('PowerGroup');
    
    $dbh->disconnect if ($dbh);
    
    use strict;
    use DBI;
    my $dbh = DBI-> connect('dbi:ODBC:SQL Server');
    
    my $sth = $dbh->prepare('DELETE FROM Customers WHERE CompanyName = ?')
        or die "Can't prepare statement: $DBI::errstr";
    
    $sth->execute('PowerGroup');
    
    $dbh->disconnect if ($dbh);

Further information

PHP

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as PHP.

  2. Configure an ODBC data source.

  3. Check whether your PHP distribution supports ODBC. In php.ini, make sure there is no comment character (;) before the extension_dir and extension=odbc settings (;extension_dir=directory becomes extension_dir=directory and ;extension=odbc becomes extension=odbc).

  4. Create and then use PHP to run this script, which retrieves some SQL Server data:

    <?php
      // Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
      //  If your database requires a user name and password, supply them in the odbc_connect_call.
      $con = odbc_connect("SQL Server", "", "");
      $stmt = odbc_exec($con, "SELECT * FROM Customers");
      // You may need to change the capitalisation of Surname to all upper case or all lower case.
      while ($row = odbc_fetch_array($stmt)) {
          echo "Surname = ", $row["Surname"], "\n";
      }
      odbc_close($con);
    ?>
  5. This script retrieves the tables and views in your Easysoft ODBC-SQL Server Driver data source:

    <?php
    $con = odbc_connect("SQL Server", "", "");
    $tables = odbc_tables($con);
    while (($row = odbc_fetch_array($tables))) {
        print_r($row);
    }
    odbc_close($con);
    ?>
  6. This script retrieves the names of the columns in these tables and views:

    <?php
    $con = odbc_connect("SQL Server", "", "");
    $columns = odbc_columns($con);
    while (($row = odbc_fetch_array($columns))) {
        print_r($row);
    }
    odbc_close($con);
    ?>
  7. These scripts insert, update, and then delete some SQL Server data:

    <?php
      $cnx = odbc_connect("SQL Server", "", "");
      $stmt = odbc_prepare($cnx, "INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)");
      $success = odbc_execute($stmt, array('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup'));
      odbc_close($cnx);
    ?>
    
    <?php
      $cnx = odbc_connect("SQL Server", "", "");
      $stmt = odbc_prepare($cnx, "UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = ?");
      $success = odbc_execute($stmt, array('PowerGroup'));
      odbc_close($cnx);
    ?>
    
    <?php
      $cnx = odbc_connect("SQL Server", "", "");
      $stmt = odbc_prepare($cnx, "DELETE FROM Customers WHERE CompanyName = ?");
      $success = odbc_execute($stmt, array('PowerGroup'));
      odbc_close($cnx);
    ?>

Further information

Python

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as Python.

  2. Configure an ODBC data source.

  3. Check whether your Python distribution supports ODBC.

    pip list

    If you don’t have pip installed:

    curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
    python get-pip.py
  4. Do one of the following:

    • If the output contains pyodbc, your Python distribution supports ODBC. Skip to the next step.

    • If the output does not contain pyodbc, use pip to install this module:

      pip install pyodbc
  5. Create and then use Python to run this script, which retrieves some SQL Server data:

    import pyodbc
    
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    sql = "SELECT Surname FROM Customers"
    cursor.execute(sql)
    rows = cursor.fetchall()
    # You may need to change the capitalisation of Surname to all upper case or all lower case.
    for row in rows:
         print(row.Surname)
    exit()
  6. This script retrieves the tables and views in your Easysoft ODBC-SQL Server Driver data source:

    import pyodbc
    
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    cursor.tables()
    rows = cursor.fetchall()
    for row in rows:
         print(row.table_name)
    exit()
  7. This script retrieves the names of the columns in these tables and views:

    import pyodbc
    
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    cursor.columns()
    rows = cursor.fetchall()
    for row in rows:
         print(row.table_name, row.column_name)
    exit()
  8. These scripts insert, update, and then delete some SQL Server data:

    import pyodbc
    
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    sql = "INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)"
    cursor.execute(sql, 'Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup')
    cursor.commit()
    exit()
    import pyodbc
    
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    sql = "UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = ?"
    cursor.execute(sql, 'PowerGroup')
    cursor.commit()
    exit()
    import pyodbc
    
    cnxn = pyodbc.connect("DSN=SQL Server")
    cursor = cnxn.cursor()
    sql = "DELETE FROM Customers WHERE CompanyName = ?"
    cursor.execute(sql, 'PowerGroup')
    cursor.commit()
    exit()

Further information

R

  1. Install the Easysoft ODBC-SQL Server Driver on same computer as R.

  2. Configure an ODBC data source.

  3. In R Console, check whether your R distribution supports ODBC.

    library("RODBC")
  4. Do one of the following:

    • If you get no output, you have the ODBC library for R. Skip to the next step.

    • If you get an "there is no package" error, install the ODBC library for R:

      install.packages("RODBC")
  5. Create and then use R to run this script, which retrieves some SQL Server data:

    library("RODBC")
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    ch <- odbcConnect("SQL Server")
    sqlQuery(ch, paste("SELECT Surname FROM Customers"))
    odbcClose(ch)
    quit()
  6. This script retrieves the tables and views in your Easysoft ODBC-SQL Server Driver data source:

    library("RODBC")
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    ch <- odbcConnect("SQL Server")
    sqlTables(ch)
    odbcClose(ch)
    quit()
  7. This script retrieves the names of the columns in the specified table or view:

    library("RODBC")
    # Replace SQL Server with the name of your Easysoft ODBC-SQL Server Driver data source.
    ch <- odbcConnect("SQL Server")
    # You may need to change the capitalisation of Customers to all upper case or all lower case.
    sqlColumns(ch, sqtable="Customers")
    odbcClose(ch)
    quit()
  8. These scripts insert, update, and then delete some SQL Server data:

    library("RODBC")
    ch <- odbcConnect("SQL Server")
    sqlQuery(ch, paste("INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES ('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup')"))
    odbcClose(ch)
    quit()
    
    library("RODBC")
    ch <- odbcConnect("SQL Server")
    sqlQuery(ch, paste("UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = 'PowerGroup'"))
    odbcClose(ch)
    quit()
    
    library("RODBC")
    ch <- odbcConnect("SQL Server")
    sqlQuery(ch, paste("DELETE FROM Customers WHERE CompanyName = 'PowerGroup'"))
    odbcClose(ch)
    quit()