Hi all I am looking for ways to update an Access 97 database on Win2k from a Perl script similar to the code below. I have Perl AS build 618, DBI::VERSION 1.14.
I have no problems with steps 1 - 5 (drop/create a table, insert, select and fetch values).
My problem is shown in steps 6 - 8, in which I try to update a table row where a GUID field matches the value I supply.
Is someone knows a way to do it, could they please post code examples or point out the appropriate documentation. Also, why is the syntax for acceptable GUID values different between INSERT and WHERE statements?
As a workaround I could forgo using the GUID type and use a CHAR(36) field instead. However, the GUID field in Access database seens to offer some advantages (it is numeric). Can anyone comment on what the pros/cons of this workaround would be?
TIA
Rudi Farkas
rudif@bluemail.ch
rudif@lecroy.com

#! perl -w


use strict;
use warnings;
$|++;

use DBI;

print "DBI::VERSION $DBI::VERSION\n";


### 1 open

my $user = "";
my $password = "";
my $datasource = "dbi:ADO:TestGuids";

my $dbh = DBI->connect($datasource, $user, $password, {
        PrintError => 1,
        RaiseError => 0,
    })  || die "Can't connect to $datasource: $DBI::errstr";

my ($sth, $rc);


### 2 recreate table

$sth = $dbh->prepare( q{
        DROP TABLE Typelibs 
    });

$rc = $sth->execute;

### WORKAROUND FOR PROBLEM BELOW: LIBID CHAR(48),

$sth = $dbh->prepare( q{
        CREATE TABLE Typelibs (
            LIBID       GUID,
            Name        CHAR(48),
            Docstring   CHAR(255)   
        )
  });

$rc = $sth->execute;


### 3 insert some rows

$sth = $dbh->prepare( q{
        INSERT INTO Typelibs ( LIBID, Name, Docstring  ) 
        VALUES (    '67913000-C464-11D3-82CF-00C04F55136E', 
                    'MyTypelib', 
                    'For test only.'  )
    });
$rc = $sth->execute;

$sth = $dbh->prepare( q{
        INSERT INTO Typelibs ( LIBID, Name, Docstring  ) 
        VALUES (    '67913001-C464-11D3-82CF-00C04F55136E', 
                    'OtherTypelib', 
                    'For test also.'  )
    });
$rc = $sth->execute;


### 4 fetch the rows and print

$sth = $dbh->prepare( q{
      SELECT *
      FROM Typelibs
});
$rc = $sth->execute;

print "Field names: @{ $sth->{NAME} }\n";

while (my @arr = $sth->fetchrow_array) {
    print "@arr\n";
}


### 5 update a row

$sth = $dbh->prepare( q{
          UPDATE Typelibs 
          SET Docstring = 'Hello from Rudi.'
          WHERE Name = 'MyTypelib';
  });

$rc = $sth->execute;
print "$rc\n"; # returns 1



### 6 update a row

$sth = $dbh->prepare( q{
          UPDATE Typelibs 
          SET Docstring = 'Problem from Rudi.'
          WHERE LIBID = '67913001-C464-11D3-82CF-00C04F55136E';
  });

$rc = $sth->execute;
print "$rc\n";


### 7 update a row

$sth = $dbh->prepare( q{
          UPDATE Typelibs 
          SET Docstring = 'Problem from Rudi.'
          WHERE LIBID = '{GUID {67913001-C464-11D3-82CF-00C04F55136E}}
+';
  });

$rc = $sth->execute; 
print "$rc\n";   # rc == 0


### 8 update a row

$sth = $dbh->prepare( q/
          UPDATE Typelibs 
          SET Docstring = 'Problem from Rudi.'
          WHERE LIBID = {GUID {67913001-C464-11D3-82CF-00C04F55136E}};
  /);

$rc = $sth->execute; 
print "$rc\n";   # rc == 0

#[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing opera
+tor) in
#query expression 'LIBID = GUID 67913001-C464-11D3-82CF-00C04F55136E}'
+.


$dbh->disconnect;
[download]