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]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.