Hope this helps
On Tue, Oct 22, 2002 at 08:54:42AM -0400, Jeff Urlwin wrote:
>
> >
> > Not sure about this one, but it appears that my version
> > ($DBD::ODBC::VERSION = '0.40';) doesn't deal with nulls properly.
> Please upgrade to .45_18, if possible.
Not possible at the moment.
> Also, post code, if possible. With select * you usually can't assume the
> column ordering. Try using fetchrow_hashref and use Data::Dumper to view
> the contents.
$ /usr/bin/perl --version
This is perl, v5.6.1 built for i386-linux
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbh = &make_dbh();
$dbh->trace(9, '/tmp/dbi_trace.log');
print "\n\n====FIRST====\n\n";
my $sql = q[SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
];
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;
while(my $data = $sth->fetchrow_hashref()) {
print Dumper $data;
}
print "\n\n====SWAP ORDER====\n\n";
$sql .= ' ORDER BY sc_item_code DESC';
$sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;
while(my $data = $sth->fetchrow_hashref()) {
print Dumper $data;
}
sub make_dbh {
my $u = 'xxxxxx';
my $p = 'yyyyyy';
my $dbh = DBI->connect("dbi:ODBC:DSN=gaffa2;UID=$u;PWD=$p",
{ RaiseError => 1,
AutoCommit => 0 }) or die "$DBI::errstr\n";
return $dbh;
}
__END__
../sql_file.pl 2> ~/2 > ~/1
$ cat ~/1
====FIRST====
$VAR1 = {
'' => '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' => 'DURATION',
'SC_LAST_MOD' => 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' => 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' => 'RAP Administrator',
'SC_INDEX' => '5',
'SC_VALUE' => ''
};
$VAR1 = {
'' => '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' => 'PERFORMER',
'SC_LAST_MOD' => 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' => 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' => 'RAP Administrator',
'SC_INDEX' => '5',
'SC_VALUE' => 'RICHARD G. MITCHELL'
};
====SWAP ORDER====
$VAR1 = {
'' => '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' => 'PERFORMER',
'SC_LAST_MOD' => 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' => 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' => 'RAP Administrator',
'SC_INDEX' => '5',
'SC_VALUE' => 'RICHARD G. MITCHELL'
};
$VAR1 = {
'' => '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' => 'DURATION',
'SC_LAST_MOD' => 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' => 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' => 'RAP Administrator',
'SC_INDEX' => '5',
'SC_VALUE' => 'RICHARD G. MITCHELL'
};
$ cat ~/2
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
ORDER BY sc_item_code DESC
> Finally, post a trace file (set the level = 9) along with the code
> ($dbh->trace(9,'filename.log').
DBI::db=HASH(0x824ad44) trace level set to 9 in DBI 1.21-nothread
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x824adf8)~0x824ad44 'SELECT
convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
')
dbih_setup_handle(DBI::st=HASH(0x824ae70)=>DBI::st=HASH(0x824aef4), DBD::ODBC::st,
824aec4, Null!)
dbih_make_com(DBI::db=HASH(0x824ad44), DBD::ODBC::st, 200)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Err, DBI::db=HASH(0x824ad44))
SCALAR(0x82362b4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), State, DBI::db=HASH(0x824ad44))
SCALAR(0x82362fc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Errstr, DBI::db=HASH(0x824ad44))
SCALAR(0x82362d8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Handlers, DBI::db=HASH(0x824ad44))
ARRAY(0x824adbc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Debug, DBI::db=HASH(0x824ad44)) 9
(already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), FetchHashKeyName,
DBI::db=HASH(0x824ad44)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), HandleError, DBI::db=HASH(0x824ad44))
undef (not defined)
ignore named placeholders = 0
dbd_st_prepare'd sql f136653576
SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
<- prepare= DBI::st=HASH(0x824ae70) at ./sql_file.pl line 22
-> execute for DBD::ODBC::st (DBI::st=HASH(0x824ae70)~0x824aef4)
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 136653576 before)...
dbd_st_execute (for hstmt 136653576 after)...
dbd_describe sql 136653576: num_fields=7
col 1: VARCHAR len=255 disp=256, prec=255 scale=0
col 2: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 3: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 4: INTEGER len= 4 disp= 9, prec= 4 scale=0
col 5: VARCHAR len=255 disp=256, prec=255 scale=0
col 6: DATE len= 8 disp= 20, prec= 8 scale=0
col 7: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 1: '' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=256
col 2: 'SC_GROUP_CODE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
col 3: 'SC_ITEM_CODE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
col 4: 'SC_INDEX' sqltype=INTEGER, ctype=SQL_C_CHAR, maxlen=9
col 5: 'SC_VALUE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=256
col 6: 'SC_LAST_MOD' sqltype=DATE, ctype=SQL_C_CHAR, maxlen=20
col 7: 'SC_LAST_MOD_USER' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
<- execute= '0E0' at ./sql_file.pl line 23
-> fetchrow_hashref in DBD::_::st for DBD::ODBC::st
(DBI::st=HASH(0x824ae70)~0x824aef4)
>> fetch DISPATCH (DBI::st=HASH(0x824aef4) rc1/1 @1 g0 a0) at ./sql_file.pl
line 25
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER)
SQLFetch rc 0
dbih_setup_fbav for 7 fields => 0x8252168
fetch num_fields=7
fetch col#0 datalen=36 displ=256
fetch col#1 SC_GROUP_CODE datalen=13 displ=21
fetch col#2 SC_ITEM_CODE datalen=8 displ=21
fetch col#3 SC_INDEX datalen=1 displ=9
fetch col#4 SC_VALUE datalen=0 displ=256
fetch col#5 SC_LAST_MOD datalen=19 displ=20
fetch col#6 SC_LAST_MOD_USER datalen=17 displ=21
2 <- fetch= [ '94F6C407-D567-11D6-A75A-00508BFCB09E' 'MUSIC_CONTENT' 'DURATION' '5'
'' 'Oct 17 2002 05:02PM' 'RAP Administrator' ] row1 at ./sql_file.pl line 25
>> FETCH DISPATCH (DBI::st=HASH(0x824aef4) rc2/1 @2 g0 a8203928) at
./sql_file.pl line 25
2 -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER 'NAME')
2 <- FETCH= [ '' 'SC_GROUP_CODE' 'SC_ITEM_CODE' 'SC_INDEX' 'SC_VALUE' 'SC_LAST_MOD'
'SC_LAST_MOD_USER' ] at ./sql_file.pl line 25
<- fetchrow_hashref= HASH(0x8252270)7keys row1 at ./sql_file.pl line 25
-> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x824ae70)~0x824aef4)
>> fetch DISPATCH (DBI::st=HASH(0x824aef4) rc1/1 @1 g0 a0) at ./sql_file.pl
line 25
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER)
SQLFetch rc 0
fetch num_fields=7
fetch col#0 datalen=36 displ=256
fetch col#1 SC_GROUP_CODE datalen=13 displ=21
fetch col#2 SC_ITEM_CODE datalen=9 displ=21
fetch col#3 SC_INDEX datalen=1 displ=9
fetch col#4 SC_VALUE datalen=19 displ=256
fetch col#5 SC_LAST_MOD datalen=19 displ=20
fetch col#6 SC_LAST_MOD_USER datalen=17 displ=21
2 <- fetch= [ '94F6C407-D567-11D6-A75A-00508BFCB09E' 'MUSIC_CONTENT' 'PERFORMER' '5'
'RICHARD G. MITCHELL' 'Oct 17 2002 05:02PM' 'RAP Administrator' ] row2 at
./sql_file.pl line 25
>> FETCH DISPATCH (DBI::st=HASH(0x824aef4) rc2/1 @2 g0 a8203928) at
./sql_file.pl line 25
2 -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER 'NAME')
2 <- FETCH= [ '' 'SC_GROUP_CODE' 'SC_ITEM_CODE' 'SC_INDEX' 'SC_VALUE' 'SC_LAST_MOD'
'SC_LAST_MOD_USER' ] at ./sql_file.pl line 25
<- fetchrow_hashref= HASH(0x8252390)7keys row2 at ./sql_file.pl line 25
-> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x824ae70)~0x824aef4)
>> fetch DISPATCH (DBI::st=HASH(0x824aef4) rc1/1 @1 g0 a0) at ./sql_file.pl
line 25
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER)
SQLFetch rc 100
SQLGetFunctions - SQL_MoreResults supported: 0
2 <- fetch= undef row2 at ./sql_file.pl line 25
<- fetchrow_hashref= undef row2 at ./sql_file.pl line 25
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x824adf8)~0x824ad44 'SELECT
convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
ORDER BY sc_item_code DESC')
dbih_setup_handle(DBI::st=HASH(0x824af3c)=>DBI::st=HASH(0x8252204), DBD::ODBC::st,
824af48, Null!)
dbih_make_com(DBI::db=HASH(0x824ad44), DBD::ODBC::st, 200)
dbih_setup_attrib(DBI::st=HASH(0x8252204), Err, DBI::db=HASH(0x824ad44))
SCALAR(0x82362b4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), State, DBI::db=HASH(0x824ad44))
SCALAR(0x82362fc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), Errstr, DBI::db=HASH(0x824ad44))
SCALAR(0x82362d8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), Handlers, DBI::db=HASH(0x824ad44))
ARRAY(0x824adbc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), Debug, DBI::db=HASH(0x824ad44)) 9
(already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), FetchHashKeyName,
DBI::db=HASH(0x824ad44)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8252204), HandleError, DBI::db=HASH(0x824ad44))
undef (not defined)
ignore named placeholders = 0
dbd_st_prepare'd sql f136668672
SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
ORDER BY sc_item_code DESC
<- prepare= DBI::st=HASH(0x824af3c) at ./sql_file.pl line 33
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x824aef4)~INNER)
SQLFreeStmt called, returned 0.
<- DESTROY= undef at ./sql_file.pl line 34
-> execute for DBD::ODBC::st (DBI::st=HASH(0x824af3c)~0x8252204)
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 136668672 before)...
dbd_st_execute (for hstmt 136668672 after)...
dbd_describe sql 136668672: num_fields=7
col 1: VARCHAR len=255 disp=256, prec=255 scale=0
col 2: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 3: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 4: INTEGER len= 4 disp= 9, prec= 4 scale=0
col 5: VARCHAR len=255 disp=256, prec=255 scale=0
col 6: DATE len= 8 disp= 20, prec= 8 scale=0
col 7: VARCHAR len= 20 disp= 21, prec= 20 scale=0
col 1: '' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=256
col 2: 'SC_GROUP_CODE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
col 3: 'SC_ITEM_CODE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
col 4: 'SC_INDEX' sqltype=INTEGER, ctype=SQL_C_CHAR, maxlen=9
col 5: 'SC_VALUE' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=256
col 6: 'SC_LAST_MOD' sqltype=DATE, ctype=SQL_C_CHAR, maxlen=20
col 7: 'SC_LAST_MOD_USER' sqltype=VARCHAR, ctype=SQL_C_CHAR, maxlen=21
<- execute= '0E0' at ./sql_file.pl line 34
-> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x824af3c)~0x8252204)
>> fetch DISPATCH (DBI::st=HASH(0x8252204) rc1/1 @1 g0 a0) at ./sql_file.pl
line 36
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER)
SQLFetch rc 0
dbih_setup_fbav for 7 fields => 0x824ae70
fetch num_fields=7
fetch col#0 datalen=36 displ=256
fetch col#1 SC_GROUP_CODE datalen=13 displ=21
fetch col#2 SC_ITEM_CODE datalen=9 displ=21
fetch col#3 SC_INDEX datalen=1 displ=9
fetch col#4 SC_VALUE datalen=19 displ=256
fetch col#5 SC_LAST_MOD datalen=19 displ=20
fetch col#6 SC_LAST_MOD_USER datalen=17 displ=21
2 <- fetch= [ '94F6C407-D567-11D6-A75A-00508BFCB09E' 'MUSIC_CONTENT' 'PERFORMER' '5'
'RICHARD G. MITCHELL' 'Oct 17 2002 05:02PM' 'RAP Administrator' ] row1 at
./sql_file.pl line 36
>> FETCH DISPATCH (DBI::st=HASH(0x8252204) rc2/1 @2 g0 a8203928) at
./sql_file.pl line 36
2 -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER 'NAME')
2 <- FETCH= [ '' 'SC_GROUP_CODE' 'SC_ITEM_CODE' 'SC_INDEX' 'SC_VALUE' 'SC_LAST_MOD'
'SC_LAST_MOD_USER' ] at ./sql_file.pl line 36
<- fetchrow_hashref= HASH(0x825218c)7keys row1 at ./sql_file.pl line 36
-> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x824af3c)~0x8252204)
>> fetch DISPATCH (DBI::st=HASH(0x8252204) rc1/1 @1 g0 a0) at ./sql_file.pl
line 36
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER)
SQLFetch rc 0
fetch num_fields=7
fetch col#0 datalen=36 displ=256
fetch col#1 SC_GROUP_CODE datalen=13 displ=21
fetch col#2 SC_ITEM_CODE datalen=8 displ=21
fetch col#3 SC_INDEX datalen=1 displ=9
fetch col#4 SC_VALUE datalen=19 displ=256
fetch col#5 SC_LAST_MOD datalen=19 displ=20
fetch col#6 SC_LAST_MOD_USER datalen=17 displ=21
2 <- fetch= [ '94F6C407-D567-11D6-A75A-00508BFCB09E' 'MUSIC_CONTENT' 'DURATION' '5'
'RICHARD G. MITCHELL' 'Oct 17 2002 05:02PM' 'RAP Administrator' ] row2 at
./sql_file.pl line 36
>> FETCH DISPATCH (DBI::st=HASH(0x8252204) rc2/1 @2 g0 a8203928) at
./sql_file.pl line 36
2 -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER 'NAME')
2 <- FETCH= [ '' 'SC_GROUP_CODE' 'SC_ITEM_CODE' 'SC_INDEX' 'SC_VALUE' 'SC_LAST_MOD'
'SC_LAST_MOD_USER' ] at ./sql_file.pl line 36
<- fetchrow_hashref= HASH(0x82521e0)7keys row2 at ./sql_file.pl line 36
-> fetchrow_hashref for DBD::ODBC::st (DBI::st=HASH(0x824af3c)~0x8252204)
>> fetch DISPATCH (DBI::st=HASH(0x8252204) rc1/1 @1 g0 a0) at ./sql_file.pl
line 36
2 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER)
SQLFetch rc 100
2 <- fetch= undef row2 at ./sql_file.pl line 36
<- fetchrow_hashref= undef row2 at ./sql_file.pl line 36
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x8252204)~INNER)
SQLFreeStmt called, returned 0.
<- DESTROY= undef
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x824ad44)~INNER)
** auto-rollback due to disconnect without commit returned 1
<- DESTROY= undef
Cheers,
--
dave thorn | [EMAIL PROTECTED]
