I'm going to migrate a very very old Microsoft Access application, during a certain time I need ton send and read  data in MS Access.
I've compiled this small script for windows, and it run on same machine as MS Access and this script listen on port 2000

use strict;
my @tableau;
$tableau[0] ='--localport=2000';
$tableau[1]='--debug';
require DBI::ProxyServer;
delete $ENV{IFS};
delete $ENV{CDPATH};
delete $ENV{ENV};
delete $ENV{BASH_ENV};
DBI::ProxyServer::main(@tableau);
exit(0);

my connection is here
my $dsn      = "DBI:Proxy:hostname=$IP;port=$PORT;dsn=DBI:ODBC:$ODBC";
my $dbh = DBI->connect($dsn, "", "") || die "Erreur de connexion à la base";
and
my $sth = $dbh->prepare($sql);
$sth->execute();



I tried to configure with config.yaml
Plugins:
    Database:
        connections:
        Access:
                          driver:   'Proxy:hostname=192.168.0.19;port=2000;dsn=DBI:ODBC:myodbconnection'


connection seem to be ok

how I can call db handle ?

if I use  something like

 my $line       = database('Access')->quick_select('CommandeBNP', {Ckey => params->{Ckey} } );

SQL language from  Dancer::Plugin::Database; is wrong and Access stop this this error




Thu Apr 11 15:08:55 2013] [error] [client 192.168.0.19] \tDancer::Object::new('Dancer::Error', 'code', 500, 'title', 'Runtime Error', 'message', 'DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: DBD::ODBC::st execute failed: [Microsoft][Pilote ODBC Microsoft Access] Erreur de syntaxe (opM-irateur absent) dans l\\'expression \\'NumContrat=Pa_RaM000 AND CodeClient=Pa_RaM001 \\'. (SQL-42000) at /<C:\\Users\\Hugues\\Desktop\\AccessProxy.exe>DBI/ProxyServer.pm line 341.^J', 'exception', 'DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: DBD::ODBC::st execute failed: [Microsoft][Pilote ODBC Microsoft Access]


Le 11/04/2013 14:27, Stefan Hornburg (Racke) a écrit :
On 04/11/2013 02:14 PM, Hugues Max wrote:

Hello
I need to write a Dancer Application where I need to connect to Microsoft Access 97/2003 Database... :'(

I use DBI::Proxy

my $dsn      = "DBI:Proxy:hostname=$IP;port=$PORT;dsn=DBI:ODBC:$ODBC";
my $dbh = DBI->connect($dsn, "", "") || die "Erreur de connexion à la base";
$dbh->{AutoCommit} = 1;
$dbh->{RaiseError} = 1;

it's not fast but it's works.

I tried to use  Dancer::Plugin::Database but Microsoft SQL is not compatible ( Limit doesn't exist, this is TOP etc.. )

You could still use Dancer::Plugin::Database for setting up and maintaining the database connection.


my %ClientParc ;
$sql =q/SELECT Spécifique, Quantité, PrixMaintenance FROM ClientParc WHERE NumContrat=? AND CodeClient=?/;
$stha = $dbha->prepare($sql);
$stha->execute($NumContrat, $CodeClient );
 while (my ($Specifique, $Quantite, $PrixMaintenance) = $stha->fetchrow_array()) {
$ClientParc{$Specifique} = {  Specifique      => $Specifique, Quantite        => $Quantite,     PrixMaintenance => $PrixMaintenance        }
}

 I create a hash %ClientParc and I send it to TTK
and I do

return template '/echeancier' => {
    ClientParc => \%ClientParc
}
in my view

[% FOREACH s IN ClientParc.values -%]

        [%- IF loop.first %]
                <div class="span5">             [% s.Quantite %] [% s.Specifique %] </div>
        [% ELSE %]
                <div class="span5 offset5">     [% s.Quantite %] [% s.Specifique %] </div>
        [% END %]
[% END %]

And it's works but

I got 2 questions
1 ) are they a better solution ?

Obviously make a better choice on your database backend :-).


2) for each page , I need to connect to MS Access proxy and this is slow ( 2 ou 3 sec for connection, but after queries are  enough fast ), 
are they a solution to maintain a DB connection  ?

How and where do you connect?

Regards
	Racke