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.. ) 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 ? 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 ? thanks Hugues.
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 -- LinuXia Systems => http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP => http://www.icdevgroup.org/ Interchange Development Team
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 <https://metacpan.org/module/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
On 04/11/2013 03:20 PM, Hugues Max wrote:
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 <https://metacpan.org/module/Dancer::Plugin::Database>;| is wrong and Access stop this this error
my $dbh = database('Access'); my $sth = $dbh->prepare($sql); $sth->execute(); database('Access') gives you DBI database handle object with some extra methods. Regards Racke -- LinuXia Systems => http://www.linuxia.de/ Expert Interchange Consulting and System Administration ICDEVGROUP => http://www.icdevgroup.org/ Interchange Development Team
I try this solution but I've got this error this is strange, depend on Apache configuration, data arrive and I've got this error in /var/log/httpd/error.log with cgi-script [Thu Apr 11 15:50:35 2013] [error] [client 192.168.0.19] DBD::Proxy::db connected failed: Can't store CODE items at /usr/local/lib64/perl5/Storable.pm line 339, at /usr/local/share/perl5/RPC/PlServer/Comm.pm line 91, referer: http://m.maison/dispatch.cgi/navbar_login/echeancier/181 not work at all with Plack::Handler Warning caught during route execution: DBD::Proxy::db connected failed: Can't store CODE items at /usr/local/lib64/perl5/Storable.pm line 339, at /usr/local/share/perl5/RPC/PlServer/Comm.pm line 91 /usr/local/lib64/perl5/Storable.pm around line 339 336 my $da = $@; # Don't mess if called from exception handler 337 my $ret; 338 # Call C routine mstore or net_mstore, depending on network order 339 eval { $ret = &$xsptr($self) }; 340 logcroak $@ if $@ =~ s/\.?\n$/,/; 341 $@ = $da; 342 return $ret ? $ret : undef; Le 11/04/2013 15:43, Stefan Hornburg (Racke) a écrit :
On 04/11/2013 03:20 PM, Hugues Max wrote:
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 <https://metacpan.org/module/Dancer::Plugin::Database>;| is wrong and Access stop this this error
my $dbh = database('Access'); my $sth = $dbh->prepare($sql); $sth->execute();
database('Access') gives you DBI database handle object with some extra methods.
Regards Racke
participants (2)
-
Hugues Max -
Stefan Hornburg (Racke)