[dancer-users] Better Solution ?

[Hugues Max]

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
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/dancer-users/attachments/20130411/6fd31ca0/attachment.htm>