DBD::DB2: setting the application name
DB2 allows client applications to set the application name using the
SQL_ATTR_INFO_PROGRAMNAME attribute. If you have a number of perl
applications connecting to the same database, this allows you to
distinguish between them by setting the application name to the script /
library name instead of the default perl. The application name set is
visible when an administrator performs list applications.
I have long wanted to do this from DBD::DB2. I asked IBM to add this to
the driver and received an internal defect number for this (166029);
however, the latest DBD::DB2 release (1.3) does not include this feature.
The attached patch shows how this feature can be added. I hope IBM gets
around to including this in the next DBD::DB2 release; in the meantime,
other users of DBD::DB2 may benefit from this change.
This feature is used as follows:
my $dbh = DBI-connect(dbi:DB2:$dsn, $userid, $password,
{ 'db2_application_name' = $name });
Cheers,
Hildo Biersma
*** dbdimp.c.orig Tue Dec 30 10:03:29 2008
--- dbdimp.cTue Dec 30 10:02:58 2008
***
*** 417,422
--- 417,440
if( SQL_SUCCESS != ret )
goto exit;
}
+
+ pval = hv_fetch( attrh, db2_application_name, 20, 0 );
+ if(pval SvOK(*pval)) {
+ STRLEN vl;
+ SQLPOINTER app_name;
+ SQLINTEGER app_name_len;
+
+ app_name = (SQLPOINTER)SvPV(*pval, vl);
+ app_name_len = (SQLINTEGER)vl;
+
+ ret = SQLSetConnectAttr(imp_dbh-hdbc,
+ SQL_ATTR_INFO_PROGRAMNAME,
+ app_name,
+ app_name_len);
+ ret = check_error( dbh, ret, Set application name failed );
+ if( SQL_SUCCESS != ret )
+ goto exit;
+ }
}
/* If the string contains a =, use SQLDriverConnect */
DBD::DB2: handling warnings
The DBD::DB2 driver does not handle warnings in a manner consistent with
other DBD drivers. Specifically, if the trace level is lower than 3,
warnings are ignored; if the trace level is 3 or above, warnings are
treated like errors. The driver also directly manipulates the DBI
internals instead of using DBIh_SET_ERR_CHAR.
I have asked IBM to fix this and have received an internal defect number
for this (166025); however, the latest DBD::DB2 release (1.3) does not
include this feature.
The attached patch shows an attempt to fix this. It works for me, but
I'm not sure it is entirely correct; feedback from DBD::xxx authors and
DBI experts would be welcome.
The patch basically removes the dependency on the trace level and rips
out the direct manipulation of DBIc_err, errstr and state. It treats
all SQL_SUCCESS_WITH_INFO cases as warnings - perhaps these should be
reported as informational events instead.
I hope IBM gets around to including this (or an improved version) in the
next DBD::DB2 release; in the meantime, other users of DBD::DB2 may
benefit from this change.
Cheers,
Hildo Biersma
*** dbdimp.c.orig Tue Dec 30 10:03:29 2008
--- dbdimp.cTue Dec 30 12:28:15 2008
***
*** 54,63
SQLINTEGER sqlcode;
SQLCHAR sqlstate[6];
SQLCHAR msgBuffer[SQL_MAX_MESSAGE_LENGTH+1];
! char *msg = NULL;
SQLRETURN rec;
-
if( SQL_SUCCESS == rc )
return rc;
--- 54,62
SQLINTEGER sqlcode;
SQLCHAR sqlstate[6];
SQLCHAR msgBuffer[SQL_MAX_MESSAGE_LENGTH+1];
! char *msg = NULL, *err = NULL;
SQLRETURN rec;
if( SQL_SUCCESS == rc )
return rc;
***
*** 80,88
}
}
- if( SQL_SUCCESS_WITH_INFO == rc DBIS-debug 3 )
- return SQL_SUCCESS;
-
if( handle != SQL_NULL_HANDLE )
{
SQLRETURN rc;
--- 79,84
***
*** 112,125
msg = what ? what : ;
}
! sv_setiv(DBIc_ERR(imp_xxh), (IV)sqlcode);
! sv_setpv(errstr, (char *)msg);
! sv_setpv(state,(char *)sqlstate);
! DBIh_EVENT2(h, ERROR_event, DBIc_ERR(imp_xxh), errstr);
! if (DBIS-debug = 2)
! PerlIO_printf( DBILOGFP,
! %s error %d recorded: %s\n,
! what, rc, SvPV(errstr,na) );
return( rc == SQL_SUCCESS_WITH_INFO ? SQL_SUCCESS : rc );
}
--- 108,120
msg = what ? what : ;
}
! /* Report error or warning the DBI way */
! if (rc == SQL_SUCCESS_WITH_INFO) {
! err = 0; /* 0 = warning */
! } else {
! err = Nullch;
! }
! DBIh_SET_ERR_CHAR(h, imp_xxh, err, sqlcode, msg, sqlstate, Nullch);
return( rc == SQL_SUCCESS_WITH_INFO ? SQL_SUCCESS : rc );
}
***
*** 141,149
return rc;
}
- if( SQL_SUCCESS_WITH_INFO == rc DBIS-debug 3 )
- return SQL_SUCCESS;
-
if( SQL_ERROR == rc || SQL_SUCCESS_WITH_INFO == rc )
{
imp_sth_t *imp_sth;
Re: DBI / DBD and array data types
DBD::Oracle supports it
if you mean this
$sth-bind_param_array(1,\...@in_values);
$sth-bind_param_inout_array(2,\...@out_values,0,{ora_type = ORA_VARCHAR2});
$sth-execute_array({ArrayTupleStatus=\...@status});
in this case I am binding an array in and also binding an array on the way
out and I use the execute_array to do it.
No sure if DBD::Oracle will help much is it is useing OCI on the back end.
Cheers
Current releases of DB2 support an array datatype, where a list of
values is passed as a single parameter. The current release of DBD::DB2
does not support this.
Does anyone know of DBD drivers for other databases that implement an
array datatype and that I could use as an example?
Cheers, Hildo
Re: DBI / DBD and array data types
I'm not sure this is the same thing.
Both DB2 and Oracle support array inserts and array updates, where
an insert or update statement is executed with a list of values - i.e.
it is a more efficient way of executing the same insert/update multiple
times with a different list of values every time. AFAIK, that's what
the bind_param_array() and execute_array() methods are used for. I am
not sure whether DBD::DB2 currently supports this feature.
I'm talking about a different feature, where a single parameter to a
stored procedure, which will be executed a single time, is given a
variable-sized array of values (i.e. an array datatype). An example
would be a stored procedure with a signature like
CREATE PROCEDURE inArray (IN input IntArray)
I think PostgreSQL supports the same feature, but I may be wrong.
Anyway, that's what I'm looking for, not the bind_param_array() and
execute_array() methods.
Hildo
sco...@pythian.com wrote:
DBD::Oracle supports it
if you mean this
$sth-bind_param_array(1,\...@in_values);
$sth-bind_param_inout_array(2,\...@out_values,0,{ora_type = ORA_VARCHAR2});
$sth-execute_array({ArrayTupleStatus=\...@status});
in this case I am binding an array in and also binding an array on the way
out and I use the execute_array to do it.
No sure if DBD::Oracle will help much is it is useing OCI on the back end.
Cheers
Current releases of DB2 support an array datatype, where a list of
values is passed as a single parameter. The current release of DBD::DB2
does not support this.
Does anyone know of DBD drivers for other databases that implement an
array datatype and that I could use as an example?
Cheers, Hildo
Re: DBI / DBD and array data types
That is what I thought.
There is a round about way in DBD Oracle by binding a recordset 'ORA_RSET'
but that is not the same thing again
Unfortunetly Oracle itself does not really support this. Anytime I have
seen this done is usually some sort of programing trick, such as passing
in a Comma delimited list that is converted by the PSQL into an array.
You can pass an array to another SP from an SP but you have to declare the
array in the SP some people have done it that way.
A direct bind like this in DBD::Oracle is I think not going to work as you
are dealing with SQL going throuh OCI to call the SP.
Basically if you can't do someting in SQLPlus you can't do it in
DBD::Oracle there are some exceptions of course.
cheers
John Scoles
I'm not sure this is the same thing.
Both DB2 and Oracle support array inserts and array updates, where
an insert or update statement is executed with a list of values - i.e.
it is a more efficient way of executing the same insert/update multiple
times with a different list of values every time. AFAIK, that's what
the bind_param_array() and execute_array() methods are used for. I am
not sure whether DBD::DB2 currently supports this feature.
I'm talking about a different feature, where a single parameter to a
stored procedure, which will be executed a single time, is given a
variable-sized array of values (i.e. an array datatype). An example
would be a stored procedure with a signature like
CREATE PROCEDURE inArray (IN input IntArray)
I think PostgreSQL supports the same feature, but I may be wrong.
Anyway, that's what I'm looking for, not the bind_param_array() and
execute_array() methods.
Hildo
sco...@pythian.com wrote:
DBD::Oracle supports it
if you mean this
$sth-bind_param_array(1,\...@in_values);
$sth-bind_param_inout_array(2,\...@out_values,0,{ora_type =
ORA_VARCHAR2});
$sth-execute_array({ArrayTupleStatus=\...@status});
in this case I am binding an array in and also binding an array on the
way
out and I use the execute_array to do it.
No sure if DBD::Oracle will help much is it is useing OCI on the back
end.
Cheers
Current releases of DB2 support an array datatype, where a list of
values is passed as a single parameter. The current release of
DBD::DB2
does not support this.
Does anyone know of DBD drivers for other databases that implement an
array datatype and that I could use as an example?
Cheers, Hildo
