Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

ODBC without autocommit

by ig (Vicar)
on Oct 08, 2009 at 22:13 UTC ( [id://800112]=perlquestion: print w/replies, xml ) Need Help??

ig has asked for the wisdom of the Perl Monks concerning the following question:

The Jade ODBC driver provided with Greentree ERP system does not support the autocommit option and, therefore, DBI refuses to connect. I tried Win32::ODBC but that crashed. It works fine from Access and vbscript but I would like to be able to use it from perl.

Does anyone have experience or a solution for accessing Greentree data from perl or using any ODBC driver that is read-only and does not support autocommit?

Replies are listed 'Best First'.
Re: ODBC without autocommit
by mje (Curate) on Oct 09, 2009 at 08:17 UTC

    What happens when you call DBI->connect? What errors do you get back?

[reply]

      With trace on DBD::ODBC aborts the connect when the attempt to set autocommit on fails.

      post_connect/SQLSetConnectAttr(SQL_AUTOCOMMIT)

      This is from dbdimp.c in DBD::ODBC. If I comment this out, and a bit of DBI.pm that also attempts to set autocommit, then the connection completes and I can read data. The comment in dbdimp.c where autocommit is set says: DBI spec requires AutoCommit on, and comments in DBI concur. It is, unfortunately, not easy to change them because, unless they parse and understand the SQL they submit to the server, they can't distinguish what changes the database (needing a transaction and where autocommit is relevant) from what only reads the database, particularly in the case of stored procedures. I may try to find a way that a connect option prevents all attempts to set autocommit, but still I would have hacked versions of DBI and DBD::ODBC, so I'm hoping there is some other existing solution.

[reply]

        There is no solution to this that I know of that does not involve changing DBD::ODBC or changing the ODBC Driver. If it is important to you then the best way would be to add a DBD::ODBC connect attribute which either ignores HYC000 errors or disables all calls to set the commit attribute (the latter is probably better). However, this would also need to trap someone specifically calling begin_work, commit, rollback. I am happy to accept such a patch from you which you could post in rt.cpan.org.

[reply]
Re: ODBC without autocommit
by Anonymous Monk on Oct 09, 2009 at 08:34 UTC
    turn autocommit off?
[reply]

      That won't necessarily help if his driver does not support the auto commit connection attribute as DBD::ODBC will still call SQLSetConnectAttr. We need to see the error.

[reply]

        Here's a level 15 trace:

            DBI 1.607-ithread default trace level set to 0x0/15 (pid 4524) at 
+test.pl line 9
    -> DBI->connect(dbi:ODBC:Greentree, super, ****, HASH(0x30c024))
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.010000 pid=4524 ru
+id=0 euid=0
       install_driver: DBD::ODBC version 1.23 loaded from C:/strawberr
+y/perl/site/lib/DBD/ODBC.pm
    New 'DBI::dr' (for DBD::ODBC::dr, parent='', id=undef)
    dbih_setup_handle(DBI::dr=HASH(0x1e2dbb4)=>DBI::dr=HASH(0x1e2dc14)
+, DBD::ODBC::dr, 0, Null!)
    dbih_make_com(Null!, 0, DBD::ODBC::dr, 92, 0) thr#1a207d4
    dbih_setup_attrib(DBI::dr=HASH(0x1e2dc14), Err, Null!) SCALAR(0x1a
+822dc) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1e2dc14), State, Null!) SCALAR(0x
+1a8227c) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1e2dc14), Errstr, Null!) SCALAR(0
+x1a822bc) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1e2dc14), TraceLevel, Null!) 0 (a
+lready defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1e2dc14), FetchHashKeyName, Null!
+) 'NAME' (already defined)
    <- install_driver= DBI::dr=HASH(0x1e2dbb4)
    >> connect     DISPATCH (DBI::dr=HASH(0x1e2dbb4) rc2/3 @5 g0 ima80
+01 pid#4524) at C:/strawberry/perl/site/lib/DBI.pm line 638
    !! warn: 0 CLEARED by call to connect method
    -> connect for DBD::ODBC::dr (DBI::dr=HASH(0x1e2dbb4)~0x1e2dc14 'G
+reentree' 'super' **** HASH(0x1a8462c)) thr#1a207d4
    New 'DBI::db' (for DBD::ODBC::db, parent=DBI::dr=HASH(0x1e2dc14), 
+id=undef)
    dbih_setup_handle(DBI::db=HASH(0x1e2e124)=>DBI::db=HASH(0x1e2e074)
+, DBD::ODBC::db, 1a8b4a4, Null!)
    dbih_make_com(DBI::dr=HASH(0x1e2dc14), 1e0d49c, DBD::ODBC::db, 508
+, 0) thr#1a207d4
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), Err, DBI::dr=HASH(0x1e2
+dc14)) SCALAR(0x1a8b6b4) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), State, DBI::dr=HASH(0x1
+e2dc14)) SCALAR(0x1a8b734) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), Errstr, DBI::dr=HASH(0x
+1e2dc14)) SCALAR(0x1a8b6f4) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), TraceLevel, DBI::dr=HAS
+H(0x1e2dc14)) 0 (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), FetchHashKeyName, DBI::
+dr=HASH(0x1e2dc14)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), HandleSetErr, DBI::dr=H
+ASH(0x1e2dc14)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), HandleError, DBI::dr=HA
+SH(0x1e2dc14)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), ReadOnly, DBI::dr=HASH(
+0x1e2dc14)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1e2e074), Profile, DBI::dr=HASH(0
+x1e2dc14)) undef (not defined)
    SQLDriverConnect failed:
    !!dbd_error2(err_rc=-1, what=post_connect/SQLSetConnectAttr(SQL_AU
+TOCOMMIT), handles=(1e0d508,1e4cfc8,0)
    !SQLError(1e0d508,1e4cfc8,0) = (HYC00, 8322, [Jade Software Corpor
+ation][JADE ODBC Driver] Optional feature not implemented)
    >> DESTROY     DISPATCH (DBI::db=HASH(0x1e2e124) rc1/1 @1 g0 ima10
+004 pid#4524) at C:/strawberry/perl/site/lib/DBI.pm line 638 via  at 
+test.pl line 11
    <> DESTROY(DBI::db=HASH(0x1e2e124)) ignored for outer handle (inne
+r DBI::db=HASH(0x1e2e074) has ref cnt 1)
    >> DESTROY     DISPATCH (DBI::db=HASH(0x1e2e074) rc1/1 @1 g0 ima10
+004 pid#4524) at C:/strawberry/perl/site/lib/DBI.pm line 638 via  at 
+test.pl line 11
    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1e2e074)~INNER) thr#1
+a207d4
         DESTROY for DBI::db=HASH(0x1e2e074) ignored - handle not init
+ialised
       ERROR: 1 '[Jade Software Corporation][JADE ODBC Driver] Optiona
+l feature not implemented (SQL-HYC00)' (err#1)
    <- DESTROY= undef at C:/strawberry/perl/site/lib/DBI.pm line 638 v
+ia  at test.pl line 11
    DESTROY (dbih_clearcom) (dbh 0x1e2e074, com 0x1a3cc34, imp DBD::OD
+BC::db):
       FLAGS 0x100011: COMSET Warn PrintWarn 
       ERR 1
       ERRSTR '[Jade Software Corporation][JADE ODBC Driver] Optional 
+feature not implemented (SQL-HYC00)'
       PARENT DBI::dr=HASH(0x1e2dc14)
       KIDS 0 (0 Active)
    dbih_clearcom 0x1e2e074 (com 0x1a3cc34, type 2) done.

    !! ERROR: 1 '[Jade Software Corporation][JADE ODBC Driver] Optiona
+l feature not implemented (SQL-HYC00)' (err#0)
    <- connect= undef at C:/strawberry/perl/site/lib/DBI.pm line 638
    -> $DBI::errstr (&) FETCH from lasth=HASH
    >> DBD::ODBC::dr::errstr
    <- $DBI::errstr= '[Jade Software Corporation][JADE ODBC Driver] Op
+tional feature not implemented (SQL-HYC00)'
       DBI connect('Greentree','super',...) failed: [Jade Software Cor
+poration][JADE ODBC Driver] Optional feature not implemented (SQL-HYC
+00)
    -- DBI::END ($@: , $!: )
    >> disconnect_all DISPATCH (DBI::dr=HASH(0x1e2dbb4) rc1/3 @1 g0 im
+a801 pid#4524) at C:/strawberry/perl/site/lib/DBI.pm line 716 via  at
+ test.pl line 11
    !! ERROR: 1 CLEARED by call to disconnect_all method
    -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x1e2dbb4)~0x1e2
+dc14) thr#1a207d4
    <- disconnect_all= '' at C:/strawberry/perl/site/lib/DBI.pm line 7
+16 via  at test.pl line 11
!   >> DESTROY     DISPATCH (DBI::dr=HASH(0x1e2dc14) rc1/1 @1 g0 ima10
+004 pid#4524) during global destruction
!   -> DESTROY in DBD::_::common for DBD::ODBC::dr (DBI::dr=HASH(0x1e2
+dc14)~INNER) thr#1a207d4
!   <- DESTROY= undef during global destruction
    DESTROY (dbih_clearcom) (drh 0x1e2dbb4, com 0x1e0d49c, imp global 
+destruction):
       FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit 
       PARENT undef
       KIDS 0 (0 Active)
    dbih_clearcom 0x1e2dbb4 (com 0x1e0d49c, type 1) done.

!   >> DESTROY     DISPATCH (DBI::dr=HASH(0x1e2dbb4) rc1/1 @1 g0 ima10
+004 pid#4524) during global destruction
!   <> DESTROY for DBI::dr=HASH(0x1e2dbb4) ignored (inner handle gone)

        [download]

        The output to STDERR:

        DBI connect('Greentree','super',...) failed: [Jade Software Corporatio
+n][JADE OD
BC Driver] Optional feature not implemented (SQL-HYC00) at test.pl lin
+e 11
connect at test.pl line 11.

        [download]

        And the test script

        use strict;
use warnings;


use DBI;

DBI->trace(15, 'trace.log');

my $dbh = DBI->connect("dbi:ODBC:Greentree", "super", "super", { autoc
+ommit => 0 }) or die "connect";

        [download]

        DBD::ODBC attempts to set autocommit unconditionally (i.e. it does so even if the autocommit option is set to false) and even if it doesn't, DBI itself does so. Preventing the specific instances is easy (I have done it and the connect works) but more work would be required to make the effect optional. I am reasonably certain (but would be happy to be surprised) that the current code does not provide any option to avoid setting this option or to ignore failures setting it.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://800112]
Approved by almut
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2024-04-25 23:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found