Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

DBI MSSQL LongReadLen issue

by simplelogic (Initiate)
on Mar 24, 2014 at 19:42 UTC ( [id://1079586]=perlquestion: print w/replies, xml ) Need Help??

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

Greetings!!

Connecting to MSSQL via windows perl script. One of the columns has the 35000 bytes XML which i want to store in variable $OrigReportXML. Plan is to modify the XML and insert into another table. Any other column fetches fine, however, the $OrigReportXML is empty. No errors thrown as well.

1) Am setting as below per : 

    $dbh->{LongReadLen} = 50000;
    $dbh->{LongTruncOk} = 1;

    my $rv;
    my $cursor = $dbh->prepare($sqlstr) ;

    eval{
      $cursor->execute();
    };
    if ( $@) {
      $cursor->finish;
      exitApp("Error in execSql $@", -1);
    } else {
      pen ("Successfully executed the query");
      if (  length($colname) > 1) {
          my $row = $cursor->fetchrow_hashref;
          $rv = $row->{$colname};
          pen("Got SQlRet:" . $rv);
      } # if colname
    } # if error
  $cursor->finish;

[download]
2) If truncate is false it throws, even though it is set it to 50000, when the data is just 35000 bytes. 
$dbh->{LongTruncOk} = 0;

[download]

Throws error: DBD::ODBC::st fetchrow_hashref failed: MicrosoftSQL Server Native Client 10.0String data, right truncation (SQL-01004)

Any suggestions?

Advance thanks

Ref: http://docstore.mik.ua/orelly/linux/dbi/ch06_02.htm

Replies are listed 'Best First'.
Re: DBI MSSQL LongReadLen issue
by ww (Archbishop) on Mar 25, 2014 at 02:01 UTC

    Please don't reference sites that pirate IP.


    Questions containing the words "doesn't work" (or their moral equivalent) will usually get a downvote from me unless accompanied by:
    1. code
    2. verbatim error and/or warning messages
    3. a coherent explanation of what "doesn't work actually means.
[reply]
Re: DBI MSSQL LongReadLen issue
by mje (Curate) on Mar 25, 2014 at 11:29 UTC

    What version of DBD::ODBC and DBI? If newer versions can you set DBI_TRACE=DBD=x.log and rerun your script then pastebin the log file for me to look at?

[reply]
      Here is the DBI log for the queries. As you can see the first query returns data an ID '8' and is used in the second query which is expected to return the "m_oGroupDefinition as OrigReportXML". This is the LOB and fails to fetch. Advance thanks. 
          DBI 1.609-ithread default trace level set to 0x0/3 (pid 7264) at D
+BI.pm line 273 via wsoWinExtractor.pl line 31
    -> DBI->connect(dbi:ODBC:Driver={SQL Server Native Client 10.0};Pr
+ovider=SQLNCLI10;Server=TestServer;Database=FCSData;Trusted_Connectio
+n=yes, cttest$user, ****, HASH(0x2d87ff4))
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.010001 pid=7264 ru
+id=0 euid=0
       install_driver: DBD::ODBC version 1.23 loaded from N:/Perl/5.10
+.1/lib/DBD/ODBC.pm
    <- install_driver= DBI::dr=HASH(0x2da9dfc)
    !! warn: 0 CLEARED by call to connect method
    -> connect for DBD::ODBC::dr (DBI::dr=HASH(0x2da9dfc)~0x2da9e7c 'D
+river={SQL Server Native Client 10.0};Provider=SQLNCLI10;Server=NT-CH
+CTTVC01\MDCTTEST1;Database=FCSData;Trusted_Connection=yes' 'cttest$us
+er' **** HASH(0x1a0f47c)) thr#237014
    !SQLError(30212a0,3021348,0) = (01000, 5701, [Microsoft][SQL Serve
+r Native Client 10.0][SQL Server]Changed database context to 'FCSData
+'.)
    !SQLError(30212a0,3021348,0) = (01000, 5703, [Microsoft][SQL Serve
+r Native Client 10.0][SQL Server]Changed language setting to us_engli
+sh.)
    !SQLError(30212a0,3021348,0) = (01S00, 0, [Microsoft][SQL Server N
+ative Client 10.0]Invalid connection string attribute)
    <- connect= DBI::db=HASH(0x2da2b5c) at DBI.pm line 653 via  at Wso
+Lib.pm line 187
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'RaiseEr
+ror' 1) thr#237014
    !!DBD::ODBC unsupported attribute passed (RaiseError)
    STORE DBI::db=HASH(0x2daa28c) 'RaiseError' => 1
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- STORE= 1 at DBI.pm line 705 via  at WsoLib.pm line 187
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'PrintEr
+ror' 1) thr#237014
    !!DBD::ODBC unsupported attribute passed (PrintError)
    STORE DBI::db=HASH(0x2daa28c) 'PrintError' => 1
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- STORE= 1 at DBI.pm line 705 via  at WsoLib.pm line 187
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'AutoCom
+mit' 1) thr#237014
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- STORE= 1 at DBI.pm line 705 via  at WsoLib.pm line 187
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'Usernam
+e' 'cttest$user') thr#237014
    !!DBD::ODBC unsupported attribute passed (Username)
    STORE DBI::db=HASH(0x2daa28c) 'Username' => 'cttest$user'
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- STORE= 1 at DBI.pm line 708 via  at WsoLib.pm line 188
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <> FETCH= 'cttest$user' ('Username' from cache) at DBI.pm line 708
+ via  at WsoLib.pm line 187
    -> connected in DBD::_::db for DBD::ODBC::db (DBI::db=HASH(0x2da2b
+5c)~0x2daa28c 'dbi:ODBC:Driver={SQL Server Native Client 10.0};Provid
+er=SQLNCLI10;Server=TestServer;Database=FCSData;Trusted_Connection=ye
+s' 'cttest$user' '****
' HASH(0x2d87ff4)) thr#237014
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- connected= undef at DBI.pm line 714 via  at WsoLib.pm line 187
    <- connect= DBI::db=HASH(0x2da2b5c)
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'dbi_con
+nect_closure' CODE(0x2da9d3c)) thr#237014
    !!DBD::ODBC unsupported attribute passed (dbi_connect_closure)
    STORE DBI::db=HASH(0x2daa28c) 'dbi_connect_closure' => CODE(0x2da9
+d3c)
       info: '' '[Microsoft][SQL Server Native Client 10.0][SQL Server
+]Changed database context to 'FCSData'. (SQL-01000)
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language
+ setting to us_english. (SQL-01000) [state was 01000 now 01S00]
[Microsoft][SQL Server Native Client 10.0]Invalid connection string at
+tribute (SQL-01S00)' (err#0)
    <- STORE= 1 at DBI.pm line 723 via  at WsoLib.pm line 188
    !! info: '' CLEARED by call to prepare method
    -> prepare for DBD::ODBC::db (DBI::db=HASH(0x2da2b5c)~0x2daa28c '
    SELECT MAX(m_lObjectId) ReportGroupID
    FROM FCSData..tblRequestGroup
    WHERE m_sGroupName = 'BankDebtCashProjectionExtract'
    ') thr#237014
    SQLPrepare 
    SELECT MAX(m_lObjectId) ReportGroupID
    FROM FCSData..tblRequestGroup
    WHERE m_sGroupName = 'BankDebtCashProjectionExtract'
    
    SQLPrepare = 0
    <- prepare= DBI::st=HASH(0x2da2d8c) at wsoWinExtractor.pl line 211
+ via  at wsoWinExtractor.pl line 133
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x2da2d8c)~0x2da2b6c) t
+hr#237014
+dbd_st_execute(1a4f41c)
    dbd_st_finish(1a4f41c)
-dbd_st_execute(1a4f41c)=-1
    <- execute= -1 at wsoWinExtractor.pl line 214 via  at wsoWinExtrac
+tor.pl line 213
    -> fetchrow_hashref in DBD::_::st for DBD::ODBC::st (DBI::st=HASH(
+0x2da2d8c)~0x2da2b6c) thr#237014
1   <- FETCH= [ 'ReportGroupID' ] at wsoWinExtractor.pl line 222 via  
+at wsoWinExtractor.pl line 133
1   <- fetch= [ '8' ] row1 at wsoWinExtractor.pl line 222 via  at wsoW
+inExtractor.pl line 133
    <- fetchrow_hashref= HASH(0x2da2f4c)1keys row1 at wsoWinExtractor.
+pl line 222 via  at wsoWinExtractor.pl line 133
    -> finish for DBD::ODBC::st (DBI::st=HASH(0x2da2d8c)~0x2da2b6c) th
+r#237014
    dbd_st_finish(1a4f41c)
    <- finish= 1 at wsoWinExtractor.pl line 227 via  at wsoWinExtracto
+r.pl line 133
    <> DESTROY(DBI::st=HASH(0x2da2d8c)) ignored for outer handle (inne
+r DBI::st=HASH(0x2da2b6c) has ref cnt 1)
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x2da2b6c)~INNER) thr#2
+37014
    <- DESTROY= undef at wsoWinExtractor.pl line 133 via  at wsoWinExt
+ractor.pl line 133
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'LongRea
+dLen' 50000) thr#237014
    !!DBD::ODBC unsupported attribute passed (LongReadLen)
    STORE DBI::db=HASH(0x2daa28c) 'LongReadLen' => 50000
    <- STORE= 1 at wsoWinExtractor.pl line 162 via  at wsoWinExtractor
+.pl line 143
    -> STORE for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER 'LongTru
+ncOk' 0) thr#237014
    !!DBD::ODBC unsupported attribute passed (LongTruncOk)
    STORE DBI::db=HASH(0x2daa28c) 'LongTruncOk' => 0
    <- STORE= 1 at wsoWinExtractor.pl line 163 via  at wsoWinExtractor
+.pl line 143
    -> prepare for DBD::ODBC::db (DBI::db=HASH(0x2da2b5c)~0x2daa28c '
        SELECT m_sGroupName, m_oGroupDefinition as OrigReportXML
        FROM tblRequestGroup
        WHERE m_lObjectId  = 8
       ') thr#237014
    SQLPrepare 
        SELECT m_sGroupName, m_oGroupDefinition as OrigReportXML
        FROM tblRequestGroup
        WHERE m_lObjectId  = 8
       
    SQLPrepare = 0
    <- prepare= DBI::st=HASH(0x1a4f56c) at wsoWinExtractor.pl line 211
+ via  at wsoWinExtractor.pl line 164
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x1a4f56c)~0x2da2f4c) t
+hr#237014
+dbd_st_execute(2da2e9c)
    dbd_st_finish(2da2e9c)
-dbd_st_execute(2da2e9c)=-1
    <- execute= -1 at wsoWinExtractor.pl line 214 via  at wsoWinExtrac
+tor.pl line 213
    -> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x1a4f56c)~0x2
+da2f4c) thr#237014
1   <- FETCH= [ 'm_sGroupName' 'OrigReportXML' ] at wsoWinExtractor.pl
+ line 222 via  at wsoWinExtractor.pl line 164
    !SQLError(30212a0,3021348,30218e0) = (01004, 0, [Microsoft][SQL Se
+rver Native Client 10.0]String data, right truncation)
    !! ERROR: 1 '[Microsoft][SQL Server Native Client 10.0]String data
+, right truncation (SQL-01004)' (err#1)
1   <- fetch= undef row1 at wsoWinExtractor.pl line 222 via  at wsoWin
+Extractor.pl line 164
    !! ERROR: 1 '[Microsoft][SQL Server Native Client 10.0]String data
+, right truncation (SQL-01004)' (err#1)
    <- fetchrow_hashref= undef row1 at wsoWinExtractor.pl line 222 via
+  at wsoWinExtractor.pl line 164
    <> DESTROY(DBI::st=HASH(0x1a4f56c)) ignored for outer handle (inne
+r DBI::st=HASH(0x2da2f4c) has ref cnt 1)
    <> DESTROY(DBI::db=HASH(0x2da2b5c)) ignored for outer handle (inne
+r DBI::db=HASH(0x2daa28c) has ref cnt 3)
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x2da2f4c)~INNER) thr#2
+37014
    dbd_st_finish(1a4f47c)
       ERROR: 1 '[Microsoft][SQL Server Native Client 10.0]String data
+, right truncation (SQL-01004)' (err#1)
    <- DESTROY= undef at wsoWinExtractor.pl line 222 via  at wsoWinExt
+ractor.pl line 222
    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x2daa28c)~INNER) thr#2
+37014
       ERROR: 1 '[Microsoft][SQL Server Native Client 10.0]String data
+, right truncation (SQL-01004)' (err#0)
    <- DESTROY= undef at wsoWinExtractor.pl line 222 via  at wsoWinExt
+ractor.pl line 222
    -- DBI::END ($@: , $!: )
    !! ERROR: 1 CLEARED by call to disconnect_all method
    -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x2da9dfc)~0x2da
+9e7c) thr#237014
    <- disconnect_all= '' at DBI.pm line 731 via  at wsoWinExtractor.p
+l line 222
!   <> DESTROY(DBI::dr=HASH(0x2da9dfc)) ignored for outer handle (inne
+r DBI::dr=HASH(0x2da9e7c) has ref cnt 1)
!   -> DESTROY in DBD::_::common for DBD::ODBC::dr (DBI::dr=HASH(0x2da
+9e7c)~INNER) thr#237014
!   <- DESTROY= undef during global destruction

      [download]
[reply]
[d/l]

        At first I did not recognise that logging but then I realised you are using DBD::ODBC 1.23. That version is 4.5 years old at least. You should look at the change log and you'll realise I've changed a massive amount since then and lots of fixes and enhancements have been made. You are retrieving an XML field which is relatively new.

        Plus your trace level at 3 is too low. It needs to be 15 to see the DBD::ODBC stuff properly.

        I strongly suggest you upgrade DBD::ODBC but if you cannot, run again with trace = 15 and post the last 200-300 lines (basically from the call to SQLPrepare to when the error is output).

[reply]

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://1079586]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2024-04-23 06:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found