Tuesday, September 2, 2014

Conmecting perl on unix or linux

Connecting Perl on UNIX or Linux to Microsoft SQL Server - Perl DBI/DBD::ODBC Tutorial Part 3

This tutorial shows you how to access MS SQL Server databases from Perl on UNIX and Linux.

Connect Perl to SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014 and SQL Server Express

When developing this tutorial, we accessed SQL Server 2000–2014 and Express databases from Perl on UNIX and Linux.

Perl and Linux

To access SQL Server from Perl on Linux, we used Easysoft ODBC drivers with Perl DBI and DBD::ODBC on Debian GNU/Linux, RedHat and Ubuntu (Edgy Eft, Feisty Fawn, Gutsy Gibbon and Hardy Heron). Easysoft ODBC drivers should work with any recent 32-bit or 64-bit Linux distribution—CentOS, Fedora, Kubuntu, Mandrake/Mandriva, OpenSUSE/SUSE, RedHat Enterprise Linux (RHEL), Slackware and so on.
Access MS SQL Server 7.0, 2000, 2005, 2008, 2012, 2014 and Express from Perl on UNIX and Linux.

Pre-requisites

The pre-requisite software for this tutorial is:
  1. Perl We used Perl 5.8, but you only need the minimum version required by the DBI and DBD::ODBC modules, which is currently 5.6. Use perl --version to see what version of Perl you have installed.
  2. DBI module We used DBI 1.45 and DBI 1.607, but the samples in this tutorial should work with anything after 1.40. To see if you have a recent enough version of DBI installed, run:
      perl -e 'use DBI 1.40;'
    If you get an error like "DBI version 1.40 required--this is only version 1.30 at -e line 1.", you need to upgrade DBI.
    If you get an error saying "DBI cannot be found in @INC", you have probably not got DBI installed.
    Go to CPAN to get an up to date version of the DBI module.
  3. DBD::ODBC We used DBD::ODBC 1.11 and DBD::ODBC 1.17. You can use similar methods to the ones shown above to check whether DBD::ODBC is installed and to see what version you have.
    To check that you have the DBD::ODBC module installed:
      perl -e 'use DBD::ODBC;'
    If you have not got DBD::ODBC installed, see Enabling ODBC support in Perl with Perl DBI and DBD::ODBC for installation instructions.
    To show the DBD::ODBC version:
      perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
    To show all drivers DBI knows about and their versions:
      perl -MDBI -e 'DBI->installed_versions;'
  4. SQL Server ODBC driver We used our UNIX/Linux ODBC driver for SQL Server 7.0, 2000, 2005, 2008 and Express to connect Perl to a remote SQL Server database:
    1. Download the SQL Server ODBC driver for your Perl client platform. (Registration required.) If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge Client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft.
    2. Install and license the SQL Server ODBC driver on the machine where Perl is installed. See which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH depending on the driver, platform and linker).
    3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Perl. For example, this SQL Server ODBC data source connects to a SQL Server Express instance that serves the Northwind database: 
      [MSSQL-Perl]
Driver                  = Easysoft ODBC-SQL Server
Server                  = my_machine\SQLEXPRESS
User                    = my_domain\my_user
Password                = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database                = Northwind
    4. Use isql to test the new data source. For example: 
      cd /usr/local/easysoft/unixODBC/bin
./isql -v MSSQL-Perl
      At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

Assumptions

  1. Previous tutorials in this series This tutorial assumes you have read or understand all the concepts covered in the previous tutorials DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection and DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database.
  2. Operating system This tutorial was designed on UNIX and Linux, and we have assumed you are using UNIX or Linux too. However, all the Perl examples should work equally well on Windows so long as minor alterations for the command line are made.
  3. ODBC driver manager We have assumed that you are using the unixODBC driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

Accessing Microsoft SQL Server from Perl on UNIX or Linux

Testing Your Data Source

The following script selects and fetches some test data. Use it to check that you can successfully access your data source from a Perl script. Remember to replace the data source name and database username and password placeholders with appropriate values for your database.
#!/usr/bin/perl -w
use strict;

use DBI;

# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;

# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# This query generates a result set with one record in it.
my $sql = "SELECT 1 AS test_col";

# Prepare the statement.
my $sth = $dbh->prepare($sql)
    or die "Can't prepare statement: $DBI::errstr";

# Execute the statement.
$sth->execute();

# Print the column name.
print "$sth->{NAME}->[0]\n";

# Fetch and display the result set value.
while ( my @row = $sth->fetchrow_array ) {
   print "@row\n";
}

# Disconnect the database from the database handle.
$dbh->disconnect;

Where to Go Now

After you have tested your data source you are ready to start fetching and manipulating the data in your database from Perl. We suggest you take a look at DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database or the excellent book "Programming the Perl DBI", which you can find at http://dbi.perl.org.
The rest of this tutorial concerns itself with MS SQL Server specific issues and common questions from people accessing MS SQL Server from Perl.

Calling Procedures

The following section shows you how to create and call SQL Server procedures that accept input and return output.
To use the sample scripts in this section you need a SQL Server database in which you can create and delete tables and procedures. The data source that you specify in the scripts needs to connect to this database.

Input Parameters

Input parameters are used to supply values to a procedure. For example, a procedure that executes a SELECT statement might use an input parameter to supply a value in a WHERE clause. You provide input parameter values when the procedure is called.
The following example script shows how to create a procedure that accepts one input parameter. The procedure adds 1 to the input parameter value and returns the new value. The script calls the procedure and displays the return value.
#!/usr/bin/perl -w
use strict;

use DBI;

# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# This procedure takes one input parameter. It adds 1 to the input
# parameter value and returns the new value.
$dbh->do(q/CREATE PROCEDURE PROC_RETURN_INPUT_PARAM (@inputval int) AS
          SET @inputval = @inputval+1;
          RETURN @inputval;/);

# The first placeholder (?) in this prepared statement is used to
# capture the return value of the called procedure. The second
# placeholder represents the value that's supplied as the procedure's
# input parameter.
my $sth1 = $dbh->prepare ("{? = call PROC_RETURN_INPUT_PARAM(?) }");

my $output;

my $i = 1;

# Bind value for the first placeholder. Because the prepared statement
# updates the bind value, bind_param_inout rather than bind_param needs
# to be used.
$sth1->bind_param_inout(1, \$output, 1);

# Bind value for second placeholder. This is value for the procedure's
# input parameter.
$sth1->bind_param(2, $i);

# Execute the prepared statement.
$sth1->execute();

# Output the procedure's return value.
print "$output\n";

$dbh->do(q/DROP PROCEDURE PROC_RETURN_INPUT_PARAM/);

$dbh->disconnect;
This example script inserts rows into a table with an identity column and uses @@IDENTITY to display the identity value used in each new row. The identity value is a unique, incremental value that SQL Server automatically generates when a new row is added to the table.
The procedure in the script accepts one input parameter: a value to insert into the new row. The procedure returns the row's identity value.
#!/usr/bin/perl -w
use strict;

use DBI;

# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# Create sample table in which to insert test data. The first column is an
# identity column. When a new row is added to the table, SQL Server
# provides a unique, incremental value for the column so long as that
# column value is not specified.
$dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (a INTEGER identity, b CHAR(1))/);

$dbh->do(q/CREATE PROCEDURE PROC_LAST_INSERTED_VALUE (@inputval char) AS
          INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
          RETURN @@IDENTITY;/);

# The first placeholder (?) in this prepared statement is used to
# capture the return value of the called procedure. The second
# placeholder represents the value that's supplied as the procedure's
# input parameter.
my $sth1 = $dbh->prepare ("{? = call PROC_LAST_INSERTED_VALUE(?) }");

my $output = 0;

my $char = q/a/;

# Insert two records into the table.
while ($char ne q/c/) {
   # Bind value for the first placeholder. Because the prepared statement
   # updates the bind value, bind_param_inout rather than bind_param needs
   # to be used.
   $sth1->bind_param_inout(1, \$output, 100);

   # Bind value for second placeholder. This is value for the procedure's
   # input parameter.
   $sth1->bind_param(2, $char);

   # Execute the prepared statement.
   $sth1->execute();

   # Display the last generated identity value.
   print "Identity value for last record = $output\n" if ($output != 0);

   $char++;
}

$dbh->do(q/DROP PROCEDURE PROC_LAST_INSERTED_VALUE/);
$dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

$dbh->disconnect;

Output Parameters

A procedure can return information by using either RETURN or output parameters. RETURN lets you return an integer value. Output parameters let you return other types of values from a procedure. For example, character strings and cursors.
Output parameters also let you return more than one value from a procedure. For example, in a procedure that contains multiple INSERT statements, you can use multiple output parameters to capture and return the numbers of rows affected by each statement.
The following sample script illustrates this technique. It uses output parameters to generate a cumulative total of the number of rows affected by statements in a procedure. The script contrasts this total with the one returned by the DBI rows method. The rows method returns the number of rows affected by the last row affecting statement. It's unaware that the example procedure contains more than one INSERT statement. The totals are different therefore. Note that this comment also applies to UPDATE and DELETE statements.
#!/usr/bin/perl -w
use strict;

use DBI;


# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.

my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
       or die "Can't connect to $data_source: $DBI::errstr";


# Create sample table in which to insert test data.

$dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (i INTEGER)/);


# This procedure takes one input parameter and two output parameters.
# The procedure inserts the input parameter value into a test table.
# The output parameters are used to return the number of rows affected
# by each INSERT statement.
$dbh->do(q/CREATE PROCEDURE PROC_INSERT_TABLES (@inputval int,
                                                @rowcount1 int OUTPUT,
                                                @rowcount2 int OUTPUT) AS
    BEGIN

    -- SET NOCOUNT ON here will prevent DBI rows from returning
    -- the row count.

    INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);

    -- @@ROWCOUNT returns the number of rows affected by the
    -- last statement. Store this number in the first output
    -- parameter.

 SET @rowcount1 = @@ROWCOUNT;
        INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval + 1);
 SET @rowcount2 = @@ROWCOUNT;
    END/);


# The first placeholder (?) in this prepared statement represents
# the value that's supplied as the procedure's input parameter.
# The second and third placeholders are used to capture the
# procedure's output parameter values.

my $sth1 = $dbh->prepare ("{call PROC_INSERT_TABLES(?, ?, ?)}");

my $i = 1;
my $sqlserverrowcount;
my $sqlserverrowcount2;


# Bind value for the first placeholder (the procedure's input parameter).
$sth1->bind_param(1, $i);


# $sqlserverrowcount and $sqlserverrowcount2 store the row count
# values returned by the procedure's output parameters.
$sth1->bind_param_inout(2, \$sqlserverrowcount, 1);
$sth1->bind_param_inout(3, \$sqlserverrowcount2, 1);


# Execute the prepared statement.
$sth1->execute();

# Produce a cumulative total for the number of rows affected
# by both INSERT statements.
$sqlserverrowcount = $sqlserverrowcount + $sqlserverrowcount2;

print "Rows affected (Cumulative \@ROWCOUNT) = $sqlserverrowcount\n";


# The rows method returns the number of rows affected by the last
# row affecting statement. It's unaware that the procedure contained
# more than one INSERT statement.
print "Rows affected (DBI rows method) = ", $sth1->rows, "\n";

$dbh->do(q/DROP PROCEDURE PROC_INSERT_TABLES/);
$dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

$dbh->disconnect;
Typical output from the above script is:
Rows affected (Cumulative @ROWCOUNT) = 2
Rows affected (DBI rows method) = 1
Note It is common practice to put "SET NOCOUNT ON" at the front of procedures, as this prevents DONE_IN_PROC TDS messages and speeds up procedures. If you do this, the DBI rows method will return -1, because the SQL Server ODBC driver does not get the count. The procedure in the example gets around this problem, as @@ROWCOUNT is still set.

Procedures Generating Multiple Result-Sets

Procedures can contain multiple SELECT statements. They are therefore capable of generating an unknown number of result sets. The following script shows how to handle multiple result set generating statements by using the boolean DBD::ODBC method odbc_more_results.
odbc_more_results lets your script check whether there is another result-set to be fetched. It returns false when no more results are available.
Note also that output parameters are not returned until odbc_more_results returns false.
To determine whether to return true or false, odbc_more_results calls SQLMoreResults. There are some situations where DBD::ODBC will automatically call SQLMoreResults without the need for odbc_more_results. If the previous SQL statement that executed was a non result-set generating statement, such as an INSERT statement, DBD::ODBC calls SQLMoreResults for you. This triggers the execution of the next statement in the procedure. If the previous statement generated a result set, your script needs to call SQLMoreResults explicitly by using odbc_more_results.
For example in the sample procedure, the first SELECT statement gets executed as a result of DBD::ODBC calling SQLMoreResults:
INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
SELECT i FROM PERL_SAMPLE_TABLE;
SELECT i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;
SELECT @result = i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;
The second and third SELECT statements get executed as a result of the script calling SQLMoreResults by using odbc_more_results.
#!/usr/bin/perl -w
use strict;

use DBI;


# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.

my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

$dbh->do(q/CREATE TABLE PERL_SAMPLE_TABLE (i INTEGER)/);

$dbh->do(q/CREATE PROCEDURE PROC_MULTIPLE_RESULT_SETS (@inputval int,
             @result int OUTPUT) AS
  BEGIN
  -- Insert the input parameter value into the test table.
  INSERT INTO PERL_SAMPLE_TABLE VALUES (@inputval);
  SELECT i FROM PERL_SAMPLE_TABLE;
  SELECT i FROM PERL_SAMPLE_TABLE WHERE i = @inputval;
  -- This SELECT statement returns one value. Capture
  -- the value with the procedure's output parameter.
         SELECT @result = i FROM PERL_SAMPLE_TABLE
  WHERE i = @inputval;
  END /);

my $sth1 = $dbh->prepare ("{call PROC_MULTIPLE_RESULT_SETS(?, ?)}");

my $i = 1;
my $output = 0;

while ($i < 4) {
    print "Iteration $i\n";
    print "============\n";

    $sth1->bind_param(1, $i);
    $sth1->bind_param_inout(2, \$output, 100);

    $sth1->execute();
    print "  Rows affected: ", $sth1->rows, "\n";

    # The execute will cause the insert in the procedure to insert a
    # $i into the table. DBD::ODBC will spot this is not a result-set
    # generating statement and call the ODBC API SQLMoreResults for you
    # thus causing the first select to run.
    #

    my $rs=1;
    do {

 # Fetch and display the result sets generated by the first two
 # SELECT statements in the procedure.

 print "  result-set ", $rs++, ":\n";
 while (my (@row) = $sth1->fetchrow_array) {
     print "\t", join (", ", @row) , "\n";
 }
 print "  (calling SQLMoreResults)\n";
    } while ($sth1->{odbc_more_results}); # Check to see if there's more
                                          # data to be fetched.

    # All result sets have been retrieved. The procedure will now
    # have returned the output parameter value.

    print "  result-set ", $rs++, "\n\t$output\n" if ($output != 0);

    $i++;
    print "\n";
}

$dbh->do(q/DROP PROCEDURE PROC_MULTIPLE_RESULT_SETS/);
$dbh->do(q/DROP TABLE PERL_SAMPLE_TABLE/);

$dbh->disconnect;
Typical output from the above script is:
Iteration 1
============
  Rows affected: 1
  result-set 1:
        1
  (calling SQLMoreResults)
  result-set 2:
        1
  (calling SQLMoreResults)
  result-set 3
        1

Iteration 2
============
  Rows affected: 1
  result-set 1:
        1
        2
  (calling SQLMoreResults)
  result-set 2:
        2
  (calling SQLMoreResults)
  result-set 3
        2

Iteration 3
============
  Rows affected: 1
  result-set 1:
        1
        2
        3
  (calling SQLMoreResults)
  result-set 2:
        3
  (calling SQLMoreResults)
  result-set 3
        3

PRINT Statement and Status Messages

PRINT statements let you return a user-defined message from a procedure. They help you troubleshoot a procedure. For example, PRINT statements let you check data values or embed trace messages to isolate problem areas in a procedure.
To capture PRINT statement output in a Perl script, you need to create a custom error handler. The standard ODBC diagnostic mechanism does not retrieve PRINT statement output.
SQL Server commands such as BACKUP and DBCC also generate status messages to report their progress. You also need to create an error handler if you want to intercept these messages.
To replace the default error handler, set the odbc_err_handler database handle attribute to a reference to a subroutine that will act as the replacement error handler. The following script shows you how to do this.
#!/usr/bin/perl -w
use strict;

use DBI;
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# Catch and display status messages with this error handler.
sub err_handler {
   my ($sqlstate, $msg, $nativeerr) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s:]*\])+//;
   print $msg;
   print "===> state: $sqlstate msg: $msg nativeerr: $nativeerr\n";
   return 0;
}

$dbh->{odbc_err_handler} = \&err_handler;

$dbh->{odbc_exec_direct} = 1;

my $sql = q/CREATE PROCEDURE PROC_PRINT_MESSAGES AS
           PRINT 'START';
           SELECT 1;
           PRINT 'END';/;

$dbh->do($sql);
my $sth = $dbh->prepare("{ call PROC_PRINT_MESSAGES  }");
$sth->execute;
do {
    while (my @row = $sth->fetchrow_array) {
       if ($row[0] eq 1) {
          print "Valid select results with print statements\n";
       }
    }
} while ($sth->{odbc_more_results});

$dbh->do(q/drop procedure PROC_PRINT_MESSAGES/);

$dbh->disconnect;
The following line in the script forces DBD::ODBC to use SQLExecDirect instead of SQLPrepare then SQLExecute.
$dbh->{odbc_exec_direct} = 1;
This prevents certain versions of the Windows MS SQL Server ODBC driver (you use this driver if you are using the ODBC-ODBC Bridge to access SQL Server) from generating errors when the procedure the script contains is executed. The errors are similar to the following:
(SQL-42S02)(DBD: Execute immediate failed err=-1) at 
myscript.pl line 6. DBD::ODBC::st execute failed: 
[Microsoft][ODBC SQL Server Driver]Invalid cursor state 
(SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1) at 
myscript.pl line 12. DBD::ODBC::st fetchrow_array failed: 
(DBD: no select statement currently executing err=-1) at 
myscript.pl line 14.

Multiple Active Statements

In SQL Server 2005 and later, there is a feature called MARS (Multiple Active Result Sets), which allows multiple active SELECT statements. If you are using our SQL Server ODBC driver and want to use MARS, add this line to your SQL Server data source in odbc.ini:
Mars_Connection = Yes
Alternatively, add "MARS_Connection=Yes" to the connection string. For example:
my $dbh = DBI->connect("dbi:ODBC:DSN=$data_source;MARS_Connection=Yes;")
    or die "Can't connect to $data_source: $DBI::errstr";
Using this method will also enable MARS on the connection if you are connecting to SQL Server 2005+ with the Microsoft SQL Server Native Client ODBC driver via the ODBC-ODBC Bridge.
Microsoft SQL Server 2000 and earlier can support Multiple Active Statements if you use a server side cursor. If your version of either SQL Server or the Microsoft SQL Server ODBC driver does not support MARS, and you have no alternative other than to use Multiple Active Statements, you can enable this workaround by:
  • Setting setting SQL_ROWSET_SIZE to a value > 1 in your connect method. For example: 
    my $dbh = DBI->connect("dbi:ODBC:$data_source", $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";
$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
  • Setting the cursor type to SQL_CURSOR_DYNAMIC in your connect method. For example: 
    my $dbh = DBI->connect("dbi:ODBC:$data_source", $user, $password,
                       {odbc_cursortype => 2})
    or die "Can't connect to $data_source: $DBI::errstr";
The following script shows how to enable MARS from DBD::ODBC and provides workarounds for SQL Server 2000 and earlier, if you have no alternative other than to use Multiple Active Statements.
#!/usr/bin/perl -w
use strict;

use DBI;

#
# MS SQL Server does not by default allow Multiple Active Statements
# i.e. it does not allow you to create a second result set on another
# statement whilst a result-set is active on another statement.
# In MS SQL Server 2005 and later, there is a feature called Multiple
# Active Result Sets (MARS), which allows multiple active SELECT
# statements, but has some implications if you are also doing transactions.
# (See http://msdn.microsoft.com/en-us/library/ms345109.aspx.)
# To enable MARS from DBD::ODBC, add "MARS_Connection=Yes"
# to the connection string as in:
#
#  DBI->connect('dbi:ODBC:DSN=mydsn;MARS_Connection=Yes;');
#
# MS SQL Server 2000 and earlier can support Multiple Active Statements
# if you use a server side cursor, but the normal ways of enabling server
# side cursors are not available in DBD::ODBC. A workaround was found by
# setting SQL_ROWSET_SIZE to a value > 1. However,
# a) although this persuades MS SQL Server to create a server side cursor
#    server-side cursors are slower than static cursors.
# b) it is slightly dangerous, as if you do not consume all the result-set
#    or call finish, you can hang the MS SQL Server ODBC driver.
#    This is easily demonstrated via the PHP interface
#    /developer/languages/php/apache_odbc.html#appb2 but
#    we have never reproduced it in DBD::ODBC - perhaps because finish
#    is called for you.
# c) this workaround only works because DBD::ODBC does not use
#    SQLExtendedFetch - if that changed it would undoubtably break.
#
# A more reliable way of doing this is by setting the cursor type
# to SQL_CURSOR_DYNAMIC. You can do this in the connect method
# as below.

my $data_source = q/datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;

# Use this form of connect to get multiple active statements
# in SQL Server 2005 and later:
#
my $dbh = DBI->connect("dbi:ODBC:DSN=$data_source;MARS_Connection=Yes;")
    or die "Can't connect to $data_source: $DBI::errstr";

#
# SQL Server 2000 and earlier:
#
#my $dbh = DBI->connect("dbi:ODBC:$data_source", $user, $password,
#                       {odbc_cursortype => 2})
#    or die "Can't connect to $data_source: $DBI::errstr";

# Alternative method to get MAS for SQL Server 2000 and earlier:
#
# Uncomment the next line to use the workaround to get MAS.
# Better method is to use odbc_cursor_type.
#
#$dbh->{odbc_SQL_ROWSET_SIZE} = 2;

$dbh->do(q/create table "mas" (a integer)/);
$dbh->do(q/create table "mas2" (a integer)/);
$dbh->do(q/insert into "mas" values (1)/);
$dbh->do(q/insert into "mas2" values (2)/);

my $sth = $dbh->prepare(q/select * from "mas"/);

# If you are connecting to SQL Server 2000 or earlier and have not
# done one of the following:
#
#  a) Set a dynamic cursor (see first commented out connect
#     call, above).
#
#  b) Uncommented the SQL_ROWSET_SIZE workaround (see commented
#     out code above) then you will get an error similar to:
#
# "DBD::ODBC::st execute failed: [unixODBC][Microsoft
# [SQL Native Client] Connection is busy with results for
# another command (SQL-HY000) at mas.pl line 92."
#
# You also need to use one of these workarounds if you are using a
# non-SQL Native Client version of the Microsoft SQL Server ODBC
# driver.

$sth->execute;

my $sth2 = $dbh->prepare(q/select * from "mas2"/);

$sth2->execute;

my $col = $sth->fetchrow_array;
print "Column from first result-set, val = $col\n";
my $othercol = $sth2->fetchrow_array;
print "Column from second result-set, val = $othercol\n";

$sth->finish;
$sth2->finish;

$dbh->do(q/drop table "mas"/);
$dbh->do(q/drop table "mas2"/);

$dbh->disconnect;

Faster Inserting

These examples use a table containing 5 columns: an integer, a char(30), a varchar(255), a timestamp and a float.
The simplest code to populate this table is:
my $sql;
for (my $i = 0; $i <50000; $i++) {
    sql = "insert into perf values (" . $i . ", 'this is a char thirty'" .
           ",'this is a varchar 255', {ts '2008-08-01 11:12:13.123'}, "
    . $i * 1.1 . ")";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
}
Here we construct the SQL insert statement once for each row, and call SQLExecDirect once for each row. The problem with this code is that the database has to parse and prepare the insert statement once per row; this can be time consuming.
A much more efficient method is to use a parameterized insert. Here, parameters are using in place of the column data. Instead of the SQL shown above, we would use:
insert into perf values (?,?,?,?,?)
We prepare this SQL once (using the prepare method), bind the parameters with the bind_param method, and then just keep calling the execute method. For some columns, we change the data bound each time. For example:
my $sth = $dbh->prepare(q/insert into perf values (?,?,?,?,?)/);

$sth->bind_param(2, 'this is a char thirty');
$sth->bind_param(3, 'this is a varchar 255');
$sth->bind_param(4, '2008-08-01 11:12:13.123');

for (my $i = 0; $i < 50000; $i++)
{
    $sth->bind_param(1, $i);
    $sth->bind_param(5, $i*1.1);
    $sth->execute;
}
Another change we could make is to use arrays of parameters but doing this does not seem to make any real difference to performance.

Alternative Perl SQL Server Solution

The ODBC-ODBC Bridge is another solution from Easysoft that provides Perl to SQL Server connectivity from UNIX and Linux platforms.
The ODBC-ODBC Bridge is available on more platforms than our SQL Server ODBC driver.
The ODBC-ODBC Bridge is an ODBC driver for UNIX and Linux that accesses MS SQL Server by using the Windows MS SQL Server ODBC driver.
The components necessary to provide ODBC access to Microsoft SQL Server data from Perl on UNIX and Linux platforms.

What You Need to Install

The ODBC-ODBC Bridge allows ODBC applications on one machine to access ODBC drivers on a remote machine.
The ODBC-ODBC Bridge consists of a client ODBC driver (which you install on the machine where Perl is running) and a server (which you install on a Windows machine where the Microsoft SQL Server ODBC driver is installed).
Refer to the following example to see what you need to install on your client and server machines:
  • Client Machine This is the machine where you want to run your Perl. You need to have installed:
    • Perl The minimum version required for Perl’s DBI module.
    • Perl DBI. The database independent interface for Perl. You can get this from CPAN.
    • Perl DBD::ODBC. The ODBC driver for DBI. You can get this from CPAN. There are other ways of accessing MS SQL Server from Perl such as the XML-ODBC Server, but this tutorial concerns itself with DBI/DBD::ODBC.
    • The ODBC-ODBC Bridge Client The ODBC-ODBC Bridge Client is an ODBC driver that talks to the ODBC-ODBC Bridge Server and therefore your remote ODBC data source. You need to install the ODBC-ODBC Bridge Client and the unixODBC Driver Manager. ODBC-ODBC Bridge distributions include the unixODBC Driver Manager. There is no need to install the ODBC-ODBC Bridge Server on this machine. Refer to the ODBC-ODBC Bridge manual for installation instructions.
    For help on installing Perl DBI, DBD::ODBC and the ODBC-ODBC Bridge client see Enabling ODBC support in Perl with Perl DBI and DBD::ODBC.
  • Server machine This is the machine where you have (or can install) an ODBC driver for the database you want to access. You need to have installed:
    • ODBC driver For this tutorial, you need the Microsoft SQL Server ODBC driver, and it is available in Microsoft Data Access Components (MDAC), the Microsoft SQL Server Native Client and the Microsoft SQL Server 2008 Native Client. You do not need install the MS SQL Server ODBC driver on the same machine as your SQL Server database.
    • The ODBC-ODBC Bridge Server. The ODBC-ODBC Bridge Server is an ODBC application, which accepts connections from the ODBC-ODBC Bridge Client and relays them to your target ODBC data source. You need to license the ODBC-ODBC Bridge Server, and trial licenses may be obtained during the installation. Refer to the ODBC-ODBC Bridge manual for installation instructions. In the following sections, we refer to the Server machine as windows_server.

Data Sources

You will find a lot of very useful information on ODBC connection attributes and data sources in DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection.
An ODBC data source is a named resource that the application passes to the ODBC driver manager. The data source tells the driver manager which ODBC driver to load and which connection attributes to use. You do not have to create data sources, as DSN-less connections provide an alternative way to connect, and you will find details on this in the first tutorial.
With the ODBC-ODBC Bridge, there are two data sources to consider. The first data source is an ODBC-ODBC Bridge Client one on your client machine. This data source tells the driver manager to use the ODBC-ODBC Bridge Client and sets a number of attributes for the ODBC-ODBC Bridge Client. The attributes tell the ODBC-ODBC Bridge Client which server to connect to and which data source on the remote machine to use.
A typical ODBC-ODBC Bridge Client data source looks like this:
[mydatasource]
# Driver tells the driver manager which ODBC driver to use
Driver = ODBC-ODBC Bridge
# Description is a description of this data source
Description = connect to SQL Server on windows
# ServerPort is the name of the machine where the ODBC-ODBC Bridge Server is running
# and the port on which it is listening. The default port is 8888.
ServerPort = windows_server:8888

# LogonUser is a valid user account on the windows_server machine
# LogonAuth is LogonUser's password
LogonUser = my_windows_username
LogonAuth = LogonUsers_password

# TargetDSN is the name of the data source on windows_server to connect to
TargetDSN = windows_server_dsn
# TargetUser and TargetAuth are synonomous with the ODBC connection
# attributes UID and PWD and specify the user and password to use for
# the database login - if required.
#
TargetUser = db_username
TargetAuth = TargetUsers_password
We will describe these attributes in the following sections. When using Perl DBI, you do not need to specify TargetUser and TargetAuth in the data source, as these are passed in to the DBI->connect method.

Authentication

There are two levels of authentication you need to be aware of:
  1. ODBC-ODBC Bridge Server authentication By default, the ODBC-ODBC Bridge Server needs to authenticate the client and become the specified user on the windows_server machine. To do this, the ODBC-ODBC Bridge Client needs to specify the data source attributes LogonUser and LogonAuth. LogonUser is the name of a user who has permission to log on locally to the windows_server machine and LogonAuth is that user’s password. A user who has log on locally permissions is defined as a user who can log on at the console of windows_server. If LogonUser and LogonAuth are valid, the ODBC-ODBC Bridge Server becomes the LogonUser.
    Note You can turn off ODBC-ODBC Bridge Server authentication, but this has various implications (see the ODBC-ODBC Bridge manual).
  2. MS SQL Server authentication When you create a MS SQL Server data source in the Windows ODBC Administrator, you have the choice of:
    • With Integrated Windows authentication This is sometimes called trusted connections. In this situation, MS SQL Server examines the user on the Windows machine that is communicating with it and no SQL Server user name or password are required.
    • With SQL Server authentication using a login ID and password entered by the user In this scenario, MS SQL Server requires a database user name and password that your database administrator will have set up. If this is your situation, the ODBC connection attributes UID and PWD must be specified at the client end (you do this with the second and third arguments to DBI->connect).
At this stage, an example might help illustrate SQL Server authentication. Suppose you have an account on the windows_server machine; your username is "Fred Bloggs" and your password is "mypassword". Your database administrator has set up the SQL Server instance with SQL Server authentication active, and has set you up an account for the database with username "dbuser" and password "dbpassword". You need to set the OOB data source attributes LogonUser/LogonAuth to "Fred Bloggs" and "mypassword" and call DBI->connect with "dbuser"/"dbpassword".

ServerPort

The ServerPort attribute tells the ODBC-ODBC Bridge Client ODBC driver which server to connect to. ServerPort is the name or IP address of the server machine where the ODBC-ODBC Bridge Server Service is running and the port the Server is listening on. The port defaults to 8888 in the ODBC-ODBC Bridge Server configuration. This is not the port your database engine is listening on. Separate the server machine from the port with a colon (:) in the ServerPort attribute value. For example, windows_server:8888.
The ODBC-ODBC Bridge Server is configurable through an HTTP interface. Supposing your ODBC-ODBC Bridge Server is installed on windows.mycompany.local, you can access the ODBC-ODBC Bridge Server administration interface using the URL http://windows.mycompany.local:8890.
Note On Windows, the ODBC-ODBC Bridge Server installation sets the HTTP interface port to 8890, by default. This default setting can be changed during the ODBC-ODBC Bridge server installation and through the HTTP interface’s Configuration section.

Target Data Source

The TargetDSN attribute tells the ODBC-ODBC Bridge Client which data source on the remote server you want to access. This must be the name of a System data source as the ODBC-ODBC Bridge Server can only access System data sources. (To create a System data source, in the Microsoft ODBC Administrator, select the System DSN tab before clicking on Add).
For more information about creating a System DSN on Windows, see the ODBC-ODBC Bridge manual.
at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Easy Way to Handle Android Notifications

Android Notifications Android Toast class provides a handy way to show users alerts but problem is that these alerts are not persist...