Re: Safely timing out DBI queries
I realize that this is very specific to the database, however, it may be possible to set a resource limit at the database level that will prevent the queries from consuming too much time. Chuck [EMAIL PROTECTED] wrote: On Sep 18, 2006, at 6:18 PM, Tyler MacDonald wrote: Dean Arnold [EMAIL PROTECTED] wrote: Which brings me back to the notion of non-blocking requests. Assuming many/most client libs do support an async capability, and a OOB cancel, then it should be possible to standardize the behavior externally. Attempting to standardize, let alone implement, non-blocking requests for the current DBI is a far bigger task than the above. On the other hand, I'd be *delighted* if you, or anyone else, would like to champion the work. cheap hack Start up a thread to handle the request, which sets a state variable on the statement handle then the request has been processed? /cheap hack The problem is not to know when a request is done processing. The problem is killing requests that are processing for too long. If you want kill them safely, you may not be able to kill them until they're done, which defeats the purpose. If you kill them unsafely, then the Perl interpreter might be in a dirty state, forcing you to thoroughly dispose of it if you want to be 100% safe. To kill the requests safely and when you want to, you need asynchronous support from the database client APIs and drivers, and quite a bit of standardized support code from DBI. H
Re: DBD::Sybase misleads me?
[EMAIL PROTECTED] wrote: The sp_configure option is dynamic, but only affects future connections. It is an upper limit, and the client has to specifically request the larger packet size to get it. Otherwise the default 512 byte packet size is used. Michael In addition to setting the configuration param for max packet size, you also need to allocate a number of bytes to the pool of memory from which the larger packets are drawn. This pool of memory is addressed by the sp_configure parameter 'additional network memory'. There is a formula to caluclate the actual amount required based on the number of connections and the packet sizes required. Regards, Chuck
Re: Can't connect to Sybase Rep server
One solution would be to install your own error handler and catch the
17001 and 2056 errors and ignore them.
HTH,
Chuck
[EMAIL PROTECTED] wrote:
This is not a very helpful answer, but are you sure this is worth
trying? A Replication Server is not an Adaptive Server Enterprise.
Admittedly they both use the same Tabular Data Stream networking
protocol, but they have very different command languages. A Rep
Server isn't even a database server. Are you sure it is reasonable to
expect a DBI driver for Sybase ASE to work correctly with Rep Server?
Maybe Sybperl is the way to go if you want your perl program to talk
to a Rep Server.
Good luck,
Mark
Matthew Persico wrote:
This simple test program:
use strict;
use warnings;
use DBI;
my $dbh = DBI-connect('dbi:Sybase:server=REPP', 'REPP_login',
'REPP_passwd', {RaiseError = 1});
my $dummy = 6;
causes this:
Server message number=17001 severity=10 state=0 line=0 server=REPP
text=No SRV_OPTION handler installed.OpenClient message: LAYER = (1)
ORIGIN = (1) SEVERITY = (1) NUMBER = (183)
Server REPP, database
Message String: ct_options(): user api layer: external error: An error
was returned from the server while setting the options, check the
server message for details.
Server message number=2056 severity=12 state=0 line=0 server=REPP
text=Line 1, character 8: Incorrect syntax with 'select'.
[EMAIL PROTECTED] (DEV, uid=8030(persicom) gid=200(develop) depth=0)
using Perl 5.6.1, DBI 1.48 and DBD::Sybase 1.07.
However, when using Perl 5.6.1, DBI 1.37 and DBD::Sybase 1.00, I get
no error message.
What DBI trace flags/envars do I neet to set in order to diagnose this?
--
Matthew O. Persico
Re: DBD::ODBC, FreeTDS, SQL Server and 'money' column type
Have you tried putting the appropriate money symbol in the front of the string ? Such as $ for US currency. HTH, Chuck [EMAIL PROTECTED] wrote: Hi all. I'm using the process: my $sth = $dbh-prepare ( update xxx set a=?, b=?, c=? where d=? ); $sth-execute( $a, $b, $c, $d ); to update data in a SQL Server database. It works fine most of the time, but if I have a 'money' column type in SQL Server, I get the following error: [unixODBC][FreeTDS][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'NUSsql.dbo.EAPosting', column 'EAPTotal'. Use the CONVERT function to run this query. (SQL-) I've googled around and found that this error occurs if you enclose a money value in single quotes. There are a couple of problems with this. Firstly, I'm not quoting the values ... I assume something further up the chain is. Possibly DBD::ODBC or FreeTDS? Secondly, I've got a couple of layers of abstraction between getting the data, processing it, and updating the database, and I really can't ( without some *major* work ) insert convert() functions around things - particularly since a lot of the stuff I'm doing is supposed to be db-neutral. I suppose I can convert the data in SQL Server to something else. I didn't set the table up - I wouldn't have used 'money' anyway, but I'm not sure what effect changing it now will have. Anyone encountered this before? Looks like a bug to me.
Re: Connection Issue with DBI::DBD::mysql
Sounds like a windows feature, does user1 have an account on the windows host ? are you using any type of firewall or other security related software ? [EMAIL PROTECTED] wrote: Hi everyone, Trying to connect to a MySQL database from a Linux Server using PERL to a Windows Machine with a MySQL database (5.0.6). I can connect successfully on the windows box but cannot on the Linux server! I ran a SQLTRACE (DBD) with the following output: imp_dbh-connect: dsn = database=userdb:hostname=192.168.2.113:port=3307, uid = user1, pwd = user1 imp_dbh-MyLogin: dbname = userdb, uid = user1, pwd = user1,host = 192.168.2.113, port = 3307 imp_dbh-mysql_dr_connect: host = 192.168.2.113, port = 3307, uid = user1, pwd = user1 imp_dbh-mysql_dr_connect: client_flags = 2 imp_dbh-mysql_dr_connect: -Access denied for user 'user1'@'192.168.3.90' (using password: YES) error 1045 recorded: Access denied for user 'user1'@'192.168.3.90' (using password: YES) What disturbs me is that the return error comes back with a different IP address than what I sent out. Anyone have any idea as to why? That would seem to be the problem with the access denied! The machine address that issues the connect is 192.168.3.190 but the address comming back is totally different. George __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp
Re: storing hierarchical values in db; methods
I have done this in a production environment.
THIS IS NOT TO BE UNDERTAKING BY THE FAINT OF HEART.
Caveats:
I have used multi parented relationships. In a static environment this
is not a big issue. In an environment where constant reparenting occurs,
along with the attendant woes of widow and orphaned hierarchies, use
dynamic sql and a cursoring technique and in terms of the table just
store the parent key and the child key.
In the years since I first did this, CPU and disk speeds have more than
made up for the cost of constructing the hierarchy on the fly.
There are a number of solutions to this problem. Attached are theory
behind how I implemented an idea that is quite similar to yours. While
it was a very interesting problem and the solution is rather elegant,
maintenance can become a nightmare. Take it from someone who implemented
this for a table of about a million parent-child relationships. If you
are interested in a full blown example, please contact me directly.
Your Friendly Neighborhood DBA,
Chuck
[EMAIL PROTECTED] wrote:
Hi all,
I have question about storing hierarchical values in db. I found some
methods, pick up the first easiest, but I ahve question about that -
have someone coded some methods, or knows some module, which helps me
with this issue ?
Ok, here is infos:
MYSQL tables:
CREATE TABLE `cat_name` (
`id` int(11) unsigned NOT NULL auto_increment,
`cat_name` varchar(150) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `cat_name` (`cat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `cat_tree` (
`id` int(9) unsigned NOT NULL auto_increment,
`parent_id` int(11) unsigned NOT NULL default '0',
`child_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `child_id` (`child_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
input data:
name = 'some name...'
category = 'foo/bar/foo/bar'
name = 'something other...'
category = 'foo/bar/foo/bar'
Perl snippet:
foreach my $cat ( split '/', $hr-{category} ) {
next if $hash{$cat}++; #skip duplicates
$hr-{cat_id} = cat_get($cat) || cat_insert($cat);
print $hr-{cat_id}, \n;
#need other functions to store/get (recursive) values into cat_tree
}
sub cat_get {
my ($id) = $dbh-selectrow_array(select id from cat_name where cat_name =
?, {}, shift);
return ($id);
}
sub cat_insert {
my $cat_insert = $dbh-prepare(INSERT INTO cat_name (cat_name) values
(?));
$cat_insert-execute( shift );
return $dbh-{'mysql_insertid'};
}
I don't know if this problem is solved like my code, but it it my
first try. Could anyone helps on this, please ?
thanks.
From: [EMAIL PROTECTED] (Scott Gray)
Date: 3 Jun 1996 08:02:07 -0400
Subject: Re: recursive to tree conversion?
Message-ID: [EMAIL PROTECTED]
Organization: VoiceNet Internet Access
In article [EMAIL PROTECTED],
Scott Gray [EMAIL PROTECTED] wrote:
There are some *very* nice algorithms for representing hierarchical
structures in a relational database using only base SQL92 features
(without cursors or recursion), which I have had great success with
in the past. I would recommend reading SQL for Smarties for
some examples.
If there is sufficient interest, I can provide some examples.
Ok, I threw the following together, so I'm not promising that it is
entirely correct, but I hope that y'all find it useful. It is
written with a heavy slant towards Sybase, for obvious reasons, but
could easily be ported to other platforms.
I know that I said this didn't involve cursors, but this example
minimally involves them, and it should be relatively easy to
implement it without cursor.
-scott
[ snip ]--
Alright, so you wanna know more about representing hierarchies
in a relational database? Before I get in to the nitty gritty
I should at least give all of the credit for this algorithm
to: _Hierarical_Structures:_The_Relational_Taboo!_, _(Can_
Transitive_Closure_Queries_be_Efficient?)_, by Michael J. Kamfonas
as published in 1992 Relational Journal (I don't know which
volume or issue).
The basic algorithm goes like this, given a tree (hierarchy) that
looks roughly like this (forgive the ASCII art--I hope you are using
a fixed font to view this):
a
/ \
/ \
/ \
b c
/ \ /|\
/ \/ | \
/ \ /| \
d e f gh
(note, that the tree need not be balanced for this algorithm to work).
The next step assigned two numbers to each node in the tree, called left
and right numbers, such that the left and right numbers of each node
contain the left and right numbers of the ancestors of that node
Re: db or file access?
Sample database code supplied with Sybase stores pics :p I'm sure someone somewhere will store them in databases. -- -- Your Friendly Neighborhood DBA, Chuck
Re: install_driver(Sybase) failed: DBD::Sybase initialize:
Anand,
It appears that your environment is not correctly setup. Check that you have
sourced in the SYBASE.sh located at /opt/sybase11.9.2. It appears that
DBD::Sybase attempted to call ct_init in the Sybase libs and failed to find it.
Check the SHLIB_PATH. Use isql to connect to verify that open client is
working properly. Then start digging into the perl code.
Desai, Anand (HP-GDIC) wrote on 4/11/2005, 8:58 AM:
Help..
I have been struggling to get DBI to work on my server...
Here is the code..
#! /usr/bin/perl
#use strict;
BEGIN
{
$ENV{SYBASE} = /opt/sybase11.9.2;
}
use DBI;
print Here's a list of DBI drivers:\n;
my @available_drivers = DBI-available_drivers('quiet');
my $driver;
foreach $driver (@available_drivers)
{
print $driver\n;
}
foreach $key (sort keys(%INC)) {
print $key = $INC{$key}\n;
}
my $dbh =DBI-connect(dbi:Sybase:server=crisp, 'sa', '', {PrintError
= 0});
die Unablee for connect to server $DBI::errstr
unless $dbh;
my $rc;
my $sth;
$sth = $dbh-prepare(select [EMAIL PROTECTED]@servername);
if($sth-execute) {
while(@$dat = $sth-fetchrow) {
print @dat\n;
}
}
my $key;
and the output is
Here's a list of DBI drivers:
DBM
ExampleP
File
Proxy
Sponge
Sybase
AutoLoader.pm = /opt/perl-uxpe/lib/5.8.0/AutoLoader.pm
Carp.pm = /opt/perl-uxpe/lib/5.8.0/Carp.pm
Config.pm = /opt/perl-uxpe/lib/5.8.0/PA-RISC1.1-thread-multi/Config.pm
Cwd.pm = /opt/perl-uxpe/lib/5.8.0/PA-RISC1.1-thread-multi/Cwd.pm
DBI.pm =
/opt/perl-uxpe/lib/site_perl/5.8.0/PA-RISC1.1-thread-multi/DBI.pm
DirHandle.pm = /opt/perl-uxpe/lib/5.8.0/DirHandle.pm
DynaLoader.pm =
/opt/perl-uxpe/lib/5.8.0/PA-RISC1.1-thread-multi/DynaLoader.pm
Exporter.pm = /opt/perl-uxpe/lib/5.8.0/Exporter.pm
Exporter/Heavy.pm = /opt/perl-uxpe/lib/5.8.0/Exporter/Heavy.pm
ExtUtils/Installed.pm = /opt/perl-uxpe/lib/5.8.0/ExtUtils/Installed.pm
ExtUtils/Liblist.pm = /opt/perl-uxpe/lib/5.8.0/ExtUtils/Liblist.pm
ExtUtils/Liblist/Kid.pm =
/opt/perl-uxpe/lib/5.8.0/ExtUtils/Liblist/Kid.pm
ExtUtils/MM.pm = /opt/perl-uxpe/lib/5.8.0/ExtUtils/MM.pm
nstall_driver(Sybase) failed: DBD::Sybase initialize: ct_init(1100)
failed at /opt/perl-uxpe/lib/5.8.0/PA-RISC1.1-thread-multi/DynaLoader.pm
line 249.
Compilation failed in require at (eval 36) line 3.
at ./test.pl line 29
can someone please help
Anand Desai
--
Your Friendly Neighborhood DBA,
Chuck
Re: Mixing select and print statements using DBI, DBD::Sybase
Just spoke to my Sybase Tech Support engineer and she assures me that the statements should be coming in the order specified by the SQL. She suggested that the DBD::Sybase implementation may be at the heart of the matter. David Goodman wrote on 2/10/2005, 1:20 PM: No, select ONE=1 is legitimate transact-sql. --- Michael A Chase [EMAIL PROTECTED] wrote: Quoting David Goodman [EMAIL PROTECTED]: Hello Michael: You write: Theoretically yes - at least for queries (select statements). Print and raiserror statements *should* normally be sent back in the same way as you'd get them in isql, but you may be seeing buffering differences where the error handler prints to STDERR and your data rows (from fetchrow) are printed to STDOUT. In my case both the messages and the rows are inserted to an array and later printed to a file. So I don't think that the difference between buffering of STDERR and STDOUT is at work. Would you be able to think of an alternative explanation for messages coming back to the message handler before the rows arrive? Just a SWAG since I don't use Sybase. Could the SELECT var=value statements be being processed in the local client instead of the database? -- Mac :}) -- Your Friendly Neighborhood DBA, Chuck
Re: Nested query problem
Michael A Chase tech wrote on 12/13/2004, 9:24 AM: On 12/13/2004 06:09 AM, Hardy Merrill said: I realize I'm splitting hairs here, and I'm no database expert, but I'm curious about your answer to this - wouldn't this be even slightly more efficient to write the WHERE clause conditions as most restricting first? In other words, SELECT feature.id FROM feature, reporter WHERE reporter.attributes_id = ? === most restrictive 1st AND feature.reporter_id = reporter.id === next most restrictive I was once told (or read?) that it is most efficient to put the most restrictive conditions first in the WHERE - is that right? I've always tended to put my joins towards the end of the WHERE when I have other criteria that I'm looking for - just curious to know if I've been doing it wrong. The general answer is that it all depends. A RDBMS builds its search plans based on a lot of factors; the order of the arguments may or may not be one of them. For instance, a long time ago in sybase it mattered. Now the actual order doesn't have that much influence. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. -- Your Friendly Neighborhood DBA, Chuck
Re: Does DBD::Sybase support a way to get the number of rows affected by a statement?
But for DBD::Sybase and most others it only tells you after you have fetched/processed the whole result set. Matthew Persico wrote on 12/9/2004, 7:56 PM: Check the DBI docs - I seem to remember a generic function to call for all DBDs which should be implemented to work for particular DBDs On Thu, 9 Dec 2004 14:03:57 -0800 (PST), David Goodman [EMAIL PROTECTED] wrote: I checked that DBD::Sybase documentation and did not find a way to get the number of rows affected by the previous statement. Is there an equivalent for ct_res_info()? Or is select @@rowcount the only way to do it? regards, David -- Matthew O. Persico -- Your Friendly Neighborhood DBA, Chuck
Re: Slow connection to Oracle 9i
Paul, Without knowing much about Oracle (although I can talk your ear off about Sybase), you want to cache connections to the server and set a limit (maybe its 1!) and use this pool of connections to connect to your server. Major advantage: No overhead for creating the connection on startup. Major disadvantage: Need to goto java afaik. Apache has some modules written for it to do something like this, hit google. There maybe a dbi module that I am unaware of that can provide this capability to your webserver via cgi/bin perl. Hit CPAN search. Paul Appleby wrote on 10/18/2004, 7:57 PM: I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby -- Your Friendly Neighborhood DBA, Chuck
Re: Inserting an Email Address fails.
FROM is a keyword, change the name of the column
Hamilton, Kent wrote on 9/23/2004, 3:15 PM:
I'm trying to insert an email address into a mysql database and it seems
to fail no matter what I do. Here are two different cuts at the code,
neither of which works and both return pretty much the same error message.
sub update_Unprocessed
{
my $From = shift; # From
my $MsgID = shift; # MsgID
my $Subject = shift;# Subject
my $Date = shift; # Date
my $Reason = shift; # Reason
my $Attachment = shift; # Body or attachment
$rows = $dbh-do(INSERT INTO UnprocessedMail SET
From = '$From',
MsgID = '$MsgID',
Subject = '$Subject',
Date = \'$Date\',
Reason = '$Reason',
Attachment = '$Attachment');
if ( $DBI::errstr ) {
$errmsg = 'Can not updated UnprocessedMail. Error: ' .
$DBI::errstr . \n;
doerror($errmsg);
if ( $DEBUG ) {
print STDERR Can not update UnprocessedMail.
Error: $DBI::errstr\n;
}
}
}
This produces this error message:
Can not update UnprocessedMail. Error: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'From = '[EMAIL PROTECTED]',
MsgID = 'BB7A3D4475F89F4696DC
Changing the subroutine to look like:
sub update_Unprocessed
{
my $count = 0;
$values[$count++] = shift; # From
$values[$count++] = shift; # MsgID
$values[$count++] = shift; # Subject
$values[$count++] = shift; # Date
$values[$count++] = shift; # Reason
$values[$count] = shift;# Body or attachment
$sth = $dbh-prepare(q{INSERT INTO UnprocessedMail
(From, MsgID, Subject, Date, Reason, Attachment )
VALUES ( ?, ?, ?, ?, ?, ? )});
if ( $DBI::errstr ) {
$errmsg = 'Can not prepare UnprocessedMail. Error: ' .
$DBI::errstr . \n;
doerror($errmsg);
if ( $DEBUG ) {
print STDERR Can not prepare UnprocessedMail.
Error: $DBI::errstr\n;
}
}
$sth-execute(@values);
if ( $DBI::errstr ) {
$errmsg = 'Can not update UnprocessedMail. Error: ' .
$DBI::errstr . \n;
doerror($errmsg);
if ( $DEBUG ) {
print STDERR Can not update UnprocessedMail.
Error: $DBI::errstr\n;
}
}
}
Gives me THIS error message
Can not update UnprocessedMail. Error: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'From, MsgID, Subject, Date, Reason,
Attachment )
VALUES ( 'KH
If I change the insert order it still blows up on the email address, IE if
I put MsgID first and the email address last it still dies on the mail
address.
I've tried various $sth-quote(), perl quotemeta, qq, q, etc., options.
I'm sure it's something very simple and stupid since I don't do this much
but.
Can anyone please offer me some help?
Thanks
Kent
--
Your Friendly Neighborhood DBA,
Chuck
Re: UnixODBC
You don't call unixODBC. You setup a data source for unixODBC, then you reference the datasource as if it was a dataserver. I just went through this exercise. The odbc.ini and odbcinst.ini need to have the correct entries for the source of your data. The name that is created in brackets is the name you pass to DBI. Christian Stalp wrote on 9/7/2004, 10:21 AM: I tryed to call UnixODBC via DBI. my $data_source = dbi:UnixODBC:refdb; $dbh = DBI-connect ( $data_source, $user, $password ) or die unable to connect !\n; . and got : install_driver(UnixODBC) failed: Can't locate DBD/UnixODBC.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/5.8.3 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/site_perl/5.8.3 /usr/local/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::UnixODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'UnixODBC' isn't right. Available drivers: Adabas, DBM, ExampleP, File, Proxy, Sponge. at ./dbi-test1.pl line 15 But there is a UnixODBC.pm in /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0/ and this directory is part of @INC. you see: [imserv03]/home/STALP perl -e 'printf %d %s\n, $i++, $_ for @INC' 0 /usr/local/lib/perl5/5.8.3/PA-RISC2.0 1 /usr/local/lib/perl5/5.8.3 2 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 3 /usr/local/lib/perl5/site_perl/5.8.3 4 /usr/local/lib/perl5/site_perl 5 . Why perl don't except the module? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de -- Your Friendly Neighborhood DBA, Chuck
Re: how to format query results
Ravi, Why not try something like testing the var using Posix functions to determine if it is a number and then sprintfing ? Ravi Kongara wrote on 8/19/2004, 6:25 PM: I won't be able to use sprintf( ), b'coz column type is not known. Query includes both numeric and character columns. I may have to check for each column whether it's a NUMBER or not, and then apply sprintf, which i'm not in favour of. Instead if a set number format ( like date format) at session level, number columns are automatically rounded off . Thanks for your quick reply though. -Ravi Reidy, Ron wrote: The SQL*Plus 'set' commands are used for display purposes only to STDOUT. To get the correct formatting of variables, use 'sprintf()'. See perldoc -d sprintf. - Ron Reidy Senior DBA Array BioPharma, Inc. -Original Message- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Thursday, August 19, 2004 2:05 PM To: DBI-Users Subject: how to format query results Task: Set the number format to 16 decimal positions for Oracle DBI session. Versions: Perl 5.6.1, DBI 1.42, DBD::Oracle 1.14, Oracle 8.1.7 on Solaris. I guess Oracle doesn't have 'ALTER SESSION' command for setting Number Format at session level. Basically how can i set those SQLPLUS options ( ex : set numwidth 16 ) in DBI..? Any clues..? Thanks, Ravi Kumar This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system. -- Your Friendly Neighborhood DBA, Chuck
Re: DBD::Sybase message suppression....
Amonotod, Install an error handler (perldoc DBD::Sybase) and ignore messages that come back with a severity of 0 or 10, (print blah has one of these two levels depending on the version of open client.) Your Friendly Neighborhood DBA, Chuck amonotod wrote on 7/21/2004, 5:32 PM: From: Henri Asseily [EMAIL PROTECTED] Date: 2004/07/21 Wed PM 09:22:07 GMT On Jul 21, 2004, at 2:06 PM, amonotod wrote: Now, if the answer is that I should change the RaiseError to 0, then Try to add PrintError = 0 Well, that sounded good, but had no effect on the messages... Thanks for the suggestion, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
Re: Connection error with DBD-Sybase
Eric, You are using the TLI interface file. You might try using the DNS based one. Chuck Eric Wimer wrote on 6/25/2004, 8:33 PM: We're running Solaris 2.8 with the Sybase ASE v12.5.1 client installed on it. I've confirmed that we can connect to a remote database using isql. Our current interfaces file: -file starts-- foo query tli tcp /dev/tcp \x000207f1a9898202 master tli tcp /dev/tcp \x000207f1a9898202 -file ends My working isql command: isql -P baz -S foo -U bar -D news My PWD file in the build directory: -file starts-- # This file contains optional login id, passwd and server info for the test # programs: # You probably don't want to have it lying around after you've made # sure that everything works OK. PWD=baz SRV=foo UID=bar DB=news -file ends I built DBD::Sybase v1.02 using DBI v1.14 with no problems reported. However, I get the following errors when I run make test: -command starts-- make test PERL_DL_NONLAZY=1 /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.0/sun4-solaris -I/usr/local/lib/perl5/5.6.0 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/autocommitCan't call method do on an undefined value at t/autocommit.t line 29. t/autocommitdubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-4 Failed 3/4 tests, 25.00% okay t/base..ok t/exec..DBI-connect(server=foo;database=news) failed: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. at t/exec.t line 26 Can't call method prepare on an undefined value at t/exec.t line 30. t/exec..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-9 Failed 8/9 tests, 11.11% okay t/fail..Unable for connect to foo: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. t/fail..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-13 Failed 12/13 tests, 7.69% okay t/login.ok 3/3FAILED test 2 Failed 1/3 tests, 66.67% okay t/main..Unable for connect to foo: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. t/main..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-16 Failed 15/16 tests, 6.25% okay t/nsql..DBI-connect(server=foo;database=news) failed: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. at t/nsql.t line 24 Can't call method func on an undefined value at t/nsql.t line 29. t/nsql..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-4 Failed 3/4 tests, 25.00% okay t/place.Unable for connect to foo: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. t/place.dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-11 Failed 10/11 tests, 9.09% okay t/xblob.DBI-connect(server=foo;database=news) failed: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Server foo, database Message String: ct_connect(): network packet layer: internal net library error: There was an error encountered while establishing the connection. at t/xblob.t line 26 Can't call method do on an undefined value at t/xblob.t line 31. t/xblob.dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 2-6 Failed 5/6 tests, 16.67% okay Failed Test Status Wstat Total Fail Failed List of failed --- t/autocommit.t 255 65280 43 75.00% 2-4 t/exec.t255
Re: Selecting a record froma table where a column might be null
[EMAIL PROTECTED] wrote: On Thu, 2004-04-29 at 23:20, Chuck Fox wrote: Hmmm, So column != column when the column contains a null. How very unusual. I would have assumed that null = null, but there I go assuming things again. So why does this work ? create table foo( i int, c char(10) null ) go insert foo values( 1, null ) insert foo values( 2, 2 ) go select * from foo where c = null go declare @c char(10) select @c = null select * from foo where c = isnull( @c, c ) go If you are using Sybase then the default behavior is to allow comparison to NULL. This is NOT ANSI SQL compliant, and one should use where foo is NULL instead of where foo = NULL to be on the safe side. This behavior is controlled by the ANSINULL option - see the SET T-SQL command. Michael Michael and others Thanks for the lesson in non-compliant behavior in one's favorite product. I commonly use this trick to solve the exact problem the original questioner posted and assumed the technique would be as universal as duct tape. Sadly, this is not the case. However, another poster, Alan, did resolve the issue in the Oracle fashion with a technique that was quite similar in nature. Try to add nvl function to both side of equal in the where clause. eg. where nvl(column, '~') = nvl(?, '~') Bad case of the heart was in the right place, but the head was in the wrong db. Your Friendly Neighborhood DBA, Chuck
Re: Selecting a record froma table where a column might be null
[EMAIL PROTECTED] wrote: On Fri, 2004-04-30 at 15:01, Chuck Fox wrote: [EMAIL PROTECTED] wrote: If you are using Sybase then the default behavior is to allow comparison to NULL. This is NOT ANSI SQL compliant, and one should use where foo is NULL instead of where foo = NULL to be on the safe side. This behavior is controlled by the ANSINULL option - see the SET T-SQL command. Michael Michael and others Thanks for the lesson in non-compliant behavior in one's favorite product. I commonly use this trick to solve the exact problem the original questioner posted and assumed the technique would be as universal as duct tape. Sadly, this is not the case. However, another poster, Alan, did resolve the issue in the Oracle fashion with a technique that was quite similar in nature. Try to add nvl function to both side of equal in the where clause. eg. where nvl(column, '~') = nvl(?, '~') Bad case of the heart was in the right place, but the head was in the wrong db. :-) Note that Sybase (via DBD::Sybase) will NOT accept a placeholder (?) that does not directly refer to a column, so using something like where isnull(?, '~') ... won't work. Similarly the code snippet mentioned in the DBI docs to handle NULL parameters where foo = ? or (? is null and foo is null) will NOT work, because Sybase's API can't figure out what type the second ? refers to. Ah the fun of writing portable SQL... :-) Michael Personally, I prefer stored procedures, but I can understand how that portability thing can get in the way :-) Chuck
Re: Selecting a record froma table where a column might be null
Hmmm,
So column != column when the column contains a null. How very unusual.
I would have assumed that null = null, but there I go assuming things
again.
So why does this work ?
create table foo( i int, c char(10) null )
go
insert foo values( 1, null )
insert foo values( 2, 2 )
go
select * from foo where c = null
go
declare @c char(10)
select @c = null
select * from foo where c = isnull( @c, c )
go
[EMAIL PROTECTED] wrote:
This does not work in Oracle. A NULL value is the absence of value and cannot be compared to anything (including NULL).
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-Original Message-
From: Chuck Fox [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 3:01 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Selecting a record froma table where a column might be null
You can try reformatting your query like this:
select dns_id
from dmg.do_not_solicit
where dns_prospect_id = isnull( ?,
dns_prospect_id )
and dns_area_code = isnull( ? ,
dns_area_code )
and dns_phone = isnull( ?, dns_phone )
and dns_zip_code = isnull( ? ,
dns_zip_code )
and dns_pr_branch = isnull( ? ,
dns_pr_branch )
and dns_pr_client_nbr = isnull( ? ,
dns_pr_client_nbr )
and dns_pr_client_status = isnull( ?,
dns_pr_client_status ) );
This will cause a null parameter to be replaced with column=column which
is always true and should select the row.
HTH,
Your Friendly Neighborhood DBA,
Chuck
[EMAIL PROTECTED] wrote:
I am trying to retrieve a record from a table, and one of the
parameters in the where clause happens to be null, and the select I am
doing below in my script, the id does not come back, although I can
use TOAD and view the record in the table. It just happens that the
branch parameter is NULL. Is there anyway to do this select with a
parameter that is NULL in a dynamic fashion?
And I have tried the quote dbi function, but that doesnt seem to work
either.
Any help would be greatly appreciated.
Chris
#!/usr/local/bin/perl
#
use warnings;
use strict;
use DBI;
$database_name = nssdev8;
eval { $dbh = DBI-connect(dbi:Oracle: . $database_name,,,{
RaiseError = 1,
PrintError = 0,
AutoCommit = 1
});};
if($@)
{
exit; # exit the program, since error from not connecting to db
}
gathering of other variables cut to save space, since it is many
other selects
my $sth = $dbh-prepare(select dns_id
from dmg.do_not_solicit
where dns_prospect_id = ?
and dns_area_code = ?
and dns_phone = ?
and dns_zip_code = ?
and dns_pr_branch = ?
and dns_pr_client_nbr = ?
and dns_pr_client_status = ?);
$sth-bind_param(1,$pro_prospect_id);
$sth-bind_param(2,$dnc_areacode);
$sth-bind_param(3,$dnc_phone);
$sth-bind_param(4,$dnc_zipcode);
$sth-bind_param(5,$pro_pr_branch);
$sth-bind_param(6,$pro_pr_client_nbr);
$sth-bind_param(7,$pro_pr_client_status);
# execute sth3 above
$sth-execute();
$sth-bind_columns(\$dns_id);
$sth-fetch;
print dnsid $dns_id\n;
--
this is end here
-
this is a sample output of a record that fails
-
new record here
pros id 477974
areacode 716
phone 4733866
zipcode 14618
branch
client nbr 0
client status P
Use of uninitialized value in concatenation (.) or string at ./dnc.pl
line 205.
dnsid
-
The information contained in this message may be privileged,
confidential, and protected from disclosure. If the reader of this
message is not the intended recipient, or any employee or agent
responsible for delivering this message to the intended recipient, you
are hereby notified that any dissemination, distribution, or copying
of this communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by replying
to the message and deleting it from your computer.
Thank you. Paychex, Inc.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying
Re: Avoiding coding username/password directly into perl script
[EMAIL PROTECTED] wrote: Hi there, I figured this would be a pretty common problem, but I didn't see anything in the FAQ or docs. We've got a script that uses DBI to connect to SQL Server. There is a system DSN set up and this already has the username and password. So do we really need to pass them again, as shown below? my $db = DBI-connect(dbi:ODBC:$dsn,$db_user_name,$db_password); We tried the following (removing the username/password parameters), but we got a connection error. my $db = DBI-connect(dbi:ODBC:$dsn); It would be nice (for maintenance) and obviously more secure if we could pass only the DSN name. Thanks, - ML One approach is to subclass DBI and override the connect method. Put in a password/userid retrieval mechanism and then change all your code to call your new subclass. Chuck
Re: DBD
Jeff, I am an associate of Les Peters, the Perl Monk, contact here. We are heavy Sybase users and there may be some possibility of us assisting you in getting *nix flavors. The Windoze and Linux versions of Sybase Open Client are available from www.sybase.com for free to developers, it just requires that you register with them to download and use the software. HTH, Chuck [EMAIL PROTECTED] wrote: Thanks Guys for DBD-Oracle. Could Someone make DBD-sybase Available for Perl5.8.2. I don't have Sybase client libs or Sybase anywhere here. If someone who is willing to keep up with versions and build DBD::Sybase with the same version of DBI that I have, then I'd be willing to put it up on my ftp site with the other downloads. That, or, if someone from Sybase is out there, get me a developer version and permission to post it, I'll do it. Thanks, Jeff Thanks in Advance. -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED] Sent: Monday, January 12, 2004 6:23 PM To: 'David N Murray'; 'Fermin, Gennie' Cc: [EMAIL PROTECTED] Subject: RE: DBD David, I got this same error and have seen others post with the same problem. I d/l'd the whole oracle kit (dbd-oracle-1.14, from cpan) and built it on my machine, and it works fine (DBI doesn't have any .dll dependencies that I'm aware of). I have the full oracle 8.1.4 client installed, MSVC 6 SP3, and perl 5.8.2 build 808 (ActiveState) on XP Pro. The commands I used (in a command window) were: vcvars32.bat (configures command-line MSVC environment), perl Makefile.pl (complained about not being able to find Oracle headers), nmake (worked despite prev warning), nmake install. If someone has some info on how I can bundle this up, I'd be happy to do it. My concern is with the various pieces that I used, however. I don't really have access to any other Oracle clients and I don't know what the DLL hell would look like if I tried to ship this. I already have it bundled and I have it PPM installable. Gennie -- I announced this a while back on DBI-Users and DBI-Announce. If you are running ActiveState build 808 (5.8.2) ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBI.ppd ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBD-Oracle.ppd Regards, Jeff Dave On Jan 9, Fermin, Gennie scribed: Hi I'm trying to install DBD-Oracle. I get this error msg when I run: C:\Perl\lib\DBD-Oracleppm install DBD-Oracle.ppd Error: no suitable installation target found for package DBD-Oracle. Does anyone know of documentation on-line that could help me? Or has anyone come across this error? Thank you, Gennie C 2003 OpenLink Financial Copyright in this message and any attachments remains with us. It is confidential and may be legally privileged. If this message is not intended for you it must not be read, copied or used by you or disclosed to anyone else. Please advise the sender immediately if you have received this message in error. Although this message and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Open Link Financial, Inc. for any loss or damage in any way arising from its use.
Re: Trying to connect to 2 Sybase database with one 1 script (Intern)
[EMAIL PROTECTED] wrote: Hi! Hello, I am trying to connect to 2 sybase databases (actually it is a nco_sql version3.6). I am trying to connect to the these database with use from a sybase client. When I am just connecting to 1 database, it all works out fine, but when I try to connect to 2 databases concurrently it fails: Error message: OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (4) Message String: ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed Operating System Error: Failed to parse address info The message indicates that the server is down or you are attempting to connect to a host/port that is not available, i.e.; routing, bad interfaces file, etc... Do any of you guys know how I can avoid this problem? Verify that you can connect to both servers with sqsh or isql. I recommend sqsh, available at www.sqsh.org, as it will allow you to connect to both and jump around as well as many additional features. Regards Alexander Ballamy Telenor Networks A/S TN-DL-DS-F-OSS 1331 FORNEBU Office: Snarøyveien 30 J3B Phone : +47 67 89 43 29 Mobile: +47 99 26 88 25 Fax : +47 67 89 41 04 E-mail: [EMAIL PROTECTED] HTH, Chuck
Re: DBD::ODBC - SQL Server - odbc_err_handler - inconsistent results
Louise,
[EMAIL PROTECTED] wrote:
All,
Several days ago, I learned how to use the odbc_err_handler subroutine
provided by DBD::ODBC... in order to capture the 'message' spit out for
non-DDL/DML commands in a SQL Server, such as 'backup' or 'dbcc'.
The first batch of commands tested were a variety of backup commands and
then DBCC CHECKDB... and the output was all in the variable, as
expected...
But then I tested a DBCC INDEXDEFRAG in the Query Analyzer it spews
out some output (not much)... but when I run it thru my process, no
output is returned...
Anybody have any clues? Code snippet below..
did you set dbcc traceon( 3604 ) first ? Doesn't appear below. The
traceon ( 3604 ) sends the output to the client, otherwise it falls into
the error log of the server.
L
Snippet for connection:
$dbh_actions = DBI-connect
(dbi:ODBC:Driver={SQL
Server};Server=$action_server;Trusted_Connection=yes;,'','',
{ PrintError = 0,
RaiseError = 0,
LongReadLen = 65536,
odbc_async_exec = 0,
odbc_err_handler = sub {
my ($state, $msg) = @_;
# Strip out all of the driver ID
stuff
$msg =~ s/^(\[[\w\s]*\])+//;
$err_text .= $msg.\n;
return 0;
}
}
);
Snippet to execute:
sub just_do_it
{
my $command = shift;
my $sth = $dbh_actions-prepare($command);
my $rc;
print ** command in sub: $command \n;
if ( $sth )
{
$rc = $sth-execute(); # This is the 'do it'.
if ( ! $rc ) # Error
{
# Save error messages to email
$err_msgs{GROUP: $group_name - ITEM: $item_id} =
Command executed:
$command\n-\nError
Message:\n$err_text;
}
}
else
{
# Save error messages to email
$err_msgs{GROUP: $group_id - ITEM: $item_id} =
Command executed:
$command\n--\nError
Message:\n$err_text;
}
print \nCOMMAND OUTPUT:\n$err_text\n;
$err_text = '';
return $rc;
HTH,
Chuck
Re: nmaking dbd-mysql errors
Looks like you are missing the c compiler that your make file is requesting/requiring. Chuck [EMAIL PROTECTED] wrote: Hi: Please see snippet below. Perhaps someone could advise me where to go from here? OS is Win2K Thanks in advance, Howard Begin snippet+++ C:\Documents and Settings\Administrator\Desktop\Downloads\DBD-mysql-2.9003nmake Microsoft (R) Program Maintenance Utility Version 1.50 Copyright (c) Microsoft Corp 1988-94. All rights reserved. cp lib/DBD/mysql.pm blib\lib\DBD/mysql.pm cp lib/DBD/mysql/GetInfo.pm blib\lib\DBD/mysql/GetInfo.pm cp lib/Mysql.pm blib\lib\Mysql.pm cp lib/DBD/mysql/INSTALL.pod blib\lib\DBD/mysql/INSTALL.pod cp lib/Mysql/Statement.pm blib\lib\Mysql/Statement.pm cp lib/Bundle/DBD/mysql.pm blib\lib\Bundle/DBD/mysql.pm cl -c -ID:/Perl/site/lib/auto/DBI -IC:\mysql\include -nologo -Gf -W3 - MD -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DES_FCRYPT -DPERL_IMPL ICIT _CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX -MD -DNDEBUG -O1 -DVERSION=\2.9003\ -DXS_VERSION=\2.9003\ -ID:\Perl\lib\CORE dbdimp .c 'cl' is not recognized as an internal or external command, operable program or batch file. NMAKE : fatal error U1077: 'C:\WINNT\system32\cmd.exe' : return code '0x1' Stop. End snippet+++ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003
Re: Stored procedures and DBD::Sybase
Steven,
I believe that you are misunderstanding what it is that you are
receiving. The only output from your commands is the print @c. This
comes through the error handler since a print statement is a level 0 or
level 10 message from the server and not a result set. Try changing the
print to a select and then you can receive it as a row result.
Regards,
Chuck
[EMAIL PROTECTED] wrote:
Hello,
I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to
connect to an MSSQL 6.5 database from a Linux client and I am having
problems retrieving the values of out parameters to a stored procedure
call.
I followed the example in the DBD::Sybase docs modifying for my purpose
as follows:
#!/usr/bin/perl
use DBI;
use DBD::Sybase;
my $dbh = DBI-connect('dbi:Sybase:server=',
'sa', '', {PrintError = 1, AutoCommit = 1});
$dbh-do('use tempdb');
my $sql = q{
declare @a int
declare @b varchar(10)
exec foo @a output, @b output
declare @c varchar(255)
select @c = @b + ' : ' + convert(varchar(3), @a)
print @c
};
my $sth = $dbh-prepare($sql);
$sth-execute;
do {
while ($d = $sth-fetch) {
$rt = $sth-{'syb_result_type'};
print(Iterating: $rt\n);
if ($rt == DBD::Sybase::CS_PARAM_RESULT) {
print(found param result [EMAIL PROTECTED]);
$a = $$d[0];
$b = $$d[1];
} elsif ($rt == DBD::Sybase::CS_ROW_RESULT) {
print(found row result [EMAIL PROTECTED]);
} elsif ($rt == DBD::Sybase::CS_STATUS_RESULT) {
print(found status result [EMAIL PROTECTED]);
}
}
} while($sth-{'syb_more_results'});
print(Retrieved values $a $b\n);
And received the following output:
Iterating: 4043
found status result [0]
hello : 5
Retrieved values
As I understand the docs, the there should have been a CS_PARAM_RESULT
result set but there was not. As you see, I even printed out the
results from the sql to make sure the out params were being set. Am I
doing something wrong or is this a known limitation when using my
configuration. Perhaps I can use the Sybase Open Client libraries
instead?
I help will be greatly appreciated.
Thank you,
Steven
Re: ActivePerl And Sybase
Ramez, In the future please send your questions to the mailing list. It has a much wider audience than my lonely ( and overly full ) mail box. My comments are in italics. From the CPAN FAQ: Where can I find Perl modules? * http://www.cpan.org/modules/00modlist.long.html * http://search.cpan.org/ * http://kobesearch.cpan.org/ Due to the ever increasing number of modules on CPAN, the CPAN search engine is possibly a better starting point in your quest for code, especially if you already know exactly what you are looking for. So jump out to one of these links and search up DBI and DBD::Sybase How do I install Perl modules? Installing a new module can be as simple as typing perl -MCPAN -e 'install Chocolate::Belgian'. The CPAN.pm documentation http://search.cpan.org/author/JHI/perl-5.8.0/lib/CPAN.pm has more complete instructions on how to use this convenient tool. If you are uncomfortable with having something take that much control over your software installation, or it otherwise doesn't work for you, the perlmodinstall http://search.cpan.org/author/JHI/perl-5.8.0/pod/perlmodinstall.pod documentation covers module installation for UNIX, Windows and Macintosh in more familiar terms. Finally, if you're using ActivePerl on Windows, the PPM http://aspn.activestate.com/ASPN/Products/ActivePerl-5.6/faq/ActivePerl-faq2.html (Perl Package Manager) has much of the same functionality as CPAN.pm. Additionally, you will need a C compiler to build the DBD for Sybase. So in your case you want to perl -MCPAN -e 'install DBI' followed by perl -MCPAN -e 'install DBD::Sybase' . Another option for you is to get the source from one of the above links and then manually install it. Manual installation involves unzipping/untarring the source into some directory. Assume you have unloaded DBI into \tmp\DBI cd \tmp\DBI perl Makefile.PL make make test make install If everything is setup correctly, ie. perl installed correctly, environment correct, etc. then you should have a working copy of DBI installed into your Perl lib. Installing DBD::Sybase is quite similar. Regards, Chuck [EMAIL PROTECTED] wrote: i have already Sybase Open Client in my computer... but what is the CPAN interface and where can i find it... please can you tell me the way to install it in details and thanks - Original Message - From: Chuck Fox [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:17 PM Subject: Re: ActivePerl And Sybase In addition to the DBD and DBI, you will also need to install Sybase Open Client or FreeTDS. In terms of installing perl, use the CPAN interface to download and install. Chuck [EMAIL PROTECTED] wrote: I have a WinXP OS and IIS http server. I have download and install ActivePerl 5.8. And I want To connect to Database In Sybase Server. i read some notes and i figure that i should install DBI-1.38 then DBD-Sybase-1.01 Is that right and how can i install it should i extract these two file into the perl directory(C:/Perl) or what
Re: ActivePerl And Sybase
In addition to the DBD and DBI, you will also need to install Sybase Open Client or FreeTDS. In terms of installing perl, use the CPAN interface to download and install. Chuck [EMAIL PROTECTED] wrote: I have a WinXP OS and IIS http server. I have download and install ActivePerl 5.8. And I want To connect to Database In Sybase Server. i read some notes and i figure that i should install DBI-1.38 then DBD-Sybase-1.01 Is that right and how can i install it should i extract these two file into the perl directory(C:/Perl) or what
Re: Escaping single quote in DBI::ODBC
Doubling up the single quotes will effectively escape them
[EMAIL PROTECTED] wrote:
I am attempting to update a SQLServer db and getting an error when the value
contains a quote (apostrophe) as can be seen from the following error
message.
Error is Update contact
information failed, column is asc_contacts.title, value is vp's -1
([Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 's'. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL
Server]Unclosed quotation mark before the character string ''.
(SQL-42000)(DBD: Execute immediate failed err=-1)), occurred at
Subscriber module line 891
Here is the code for the update:
my $rows = $dbh-do (UPDATE asc_contacts set $subscriberhash{$contactvar}
= '$tree-{contact}-{$contactvar}'
where asc_contacts.contact_id = '$tree-{contact}-{contact_id}')
or error_handler (Update contact information failed, column is
$subscriberhash{$contactvar}, value is $tree-{contact}-{$contactvar}
$DBI::err ($DBI::errstr), __LINE__);
In reading Paul DuBois's MySQL book he notes the quote() method as a way
around the problem; however, I am not sure if that mechanism will work with
SQLServer.
If it is appropriate, I would change the code to:
my $varvalue = $dbh-quote($tree-{contact}-{$contactvar});
my $rows = $dbh-do (UPDATE asc_contacts set $subscriberhash{$contactvar} =
$varvalue
where asc_contacts.contact_id = '$tree-{contact}-{contact_id}')
or error_handler (Update contact information failed, column is
$subscriberhash{$contactvar}, value is $tree-{contact}-{$contactvar}
$DBI::err ($DBI::errstr), __LINE__);
Can anyone give me some advice as to whether this will work?
Many thanks.
Bernard Golden
Re: Q: SQL Relay
Henri, I couldn't agree with you more ! Stored procedures are always the way to go. Chuck [EMAIL PROTECTED] wrote: On Thursday, October 23, 2003, at 10:09 AM, Tim Bunce wrote: On Thu, Oct 23, 2003 at 06:50:21PM +1000, Ron Savage wrote: Hi Folks Anyone have experience with this: http://sqlrelay.sourceforge.net/ Funny you should mention that. I've just had an email conversation with the author (Dave Muse) to suggest that he place the relevant modules on CPAN. I'd be very interested in reports of any experience, good or bad, people may have had with SQL Relay. (Details of versions, or approximate date, plus what database interface was used would be handy.) Tim. Haven't tried it, but from the (extensive) docs, it only supports stored procedures for Oracle. That's a little at odds with its aim of improving speed for database querying. I can't use it in my environment for this reason. On a related note, it's amazing that people don't see stored procs as being one of the most effective ways to increase query speed, both on complex queries and simple ones when called very often. H.
Re: prepared insert syntax error on exec
Gary,
The names of the columns in the table cannot be replaced with
placeholders. If you have to vary the column names, you will need to
prepare or preferably do the insert separately for each change of column
names. Another option would be to specifiy all the column names in the
insert statement and put nulls (undef) in the values for the columns
that are not applicable to this data row.
Chuck
[EMAIL PROTECTED] wrote:
Hi folks,
can anyone see why this isn't working:
my $cinh=$dbh-prepare(insert into names (nid, ncname, ?) values (?, ?,
?););
$cinh-execute('nsmacc',1,'test name','ACC01');
produces the error message:
ERROR: parser: parse error at or near '
nid is an int4 primary key
ncname is a varchar(50) company name
nsmacc is a varchar(5) account number field
Re: Problem with DBD::ODBC misinterpreting CHAR columns as VARCHAR so the DBI ChopBlanks attribute 'not working'
Paul,
Sounds like the bind did not specifiy the correct property for the
column. Its been a long while since I have had to do this, but I beleve
that there is a property for the column that will specify what to do
with trailing blanks. It sounds like it currently is set to truncate
blanks, I believe you want to pad with blanks.
Chuck
[EMAIL PROTECTED] wrote:
Hi,
appended is a message I sent to the FreeTDS list earlier on today. I am having
problems with trailing blanks in CHAR fields despite the DBI ChopBlanks attribute
being set. I had thought that the type information returned to DBD::ODBC was incorrect
because of some FreeTDS problem. However, since then I used the isql program and the
'help' command. The column type is correctly reported as being of type CHAR(30)
any idea why the CHAR(30) should be reported as being a VARCHAR(30)?
any low-level workarounds for ChopBlanks? I don't want to have fixup all of the data at the application level.
thanks in advance,
pjjH
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Harrington, Paul
Sent: Monday, October 13, 2003 1:30 PM
To: FreeTDS Development Group
Subject: [freetds] Weird problem with CHAR columns being reported as
VARCHARcolumns with FreeTDS 0.61.2,DBD::ODBC and unixODBC and iODBC
talking to SQL Server 2000,perl 5.8.1 on Solaris
Hi,
I thought that I was having problems with the ChopBlanks attribute being honored in
DBD::ODBC as any of my tests with CHAR columns were failing because of trailing
spaces. However, on checking into it a bit closer, it seems that the columns in the
result set are being described as varchar rather than char. The retrieved values do
not contain trailing spaces when I access the database with DBD::Sybase built over the
same revision of FreeTDS (but -- of course -- going over ct-lib).
any ideas as to why unixODBC and iODBC seem to be interpreting the column as a varchar rather than a char?
pjjH
[details]
The condition (line 2226 of dbdimp.c in DBD-ODBC-1.06) is:
if (ChopBlanks fbh-ColSqlType == SQL_CHAR fbh-datalen 0) {
on checking the DBI trace with freetds, I see:
colname 14 = nodmst_name, len = 11 (194)
col 14: VARCHAR len= 30 disp= 31, prec= 30 scale=0
This is the (section of the) output I get from sp_help for the table.
nodmst_name
char
no 30
yes no
yes
SQL_Latin1_General_CP1_CI_AS
___
FreeTDS mailing list
[EMAIL PROTECTED]
http://lists.ibiblio.org/mailman/listinfo/freetds
Re: where clause
Maybe my MSSql is a little rusty but isn't the wildcard symbol the % and
not the * ?
Chuck
[EMAIL PROTECTED] wrote:
After posting to the newsgroup I thought I should post to the dbi-users mailing list instead.
I am unable to get a where clause to work against MS SQL Server. I get no runtime
errors, but my recordset is always empty. I have
the latest version from ActivePerl and Win2000. I just installed DBI and DBD-ODBC
using PPM so they should be current. To debug I am
displaying the SQL string, and if I paste the string into a MS Access query or
Enterprise Manager it works fine. I did trial and
error with everything I could think of changing syntax and had no success. This is my
first Perl application employing a database,
and is a test case to see what we can do in Perl rather than Java. Code is pasted
below. I changed my posting width to 132
characters to avoid wrapping but usually post with 76. If someone could help I'd
appreciate it. Thanks
#!e:\perl\bin\perl.exe
use warnings;
use strict;
use DGM;
use DBI;
use DBD::ODBC;
require 'RecipAuthFunc.pl';
my $select = 'SELECT TOP 201 AuUnique, AuRIN, AuCaseID, AuFirstName, AuLastName,
AuMiddleInitial, AuSSN FROM Authorize ';
my ($connection, $recordset, @field, @record, $filter, $count, $toomany);
RecipAuthStart(1); #this creates %passed from CGI arguments, insures there are no undefs, and writes the HTML header
$passed{'LName'} = 'W'; #this is so I can skip the selection screen
$passed{'queryby'} = 'select+using+name';
if ($passed{'queryby'} eq 'select+using+RIN') {$filter = WHERE AuRIN LIKE
'$passed{'RIN'}*'}
elsif ($passed{'queryby'} eq 'select+using+case') {$filter = WHERE AuCaseID LIKE
'$passed{'CaseID'}*'}
elsif ($passed{'queryby'} eq 'select+using+name') {$filter = WHERE (AuFirstName LIKE
'$passed{'FName'}*') AND .
(AuLastName LIKE
'$passed{'LName'}*') AND .
(AuMiddleInitial LIKE
'$passed{'Initial'}*')}
elsif ($passed{'queryby'} eq 'select+using+SSN') {$filter = WHERE AuSSN LIKE
'$passed{'SSN'}*'}
elsif ($passed{'queryby'} eq 'select+using+all') {$filter = WHERE (AuRIN LIKE
'$passed{'RIN'}*') AND .
(AuCaseID LIKE
'$passed{'CaseID'}*') AND .
(AuFirstName LIKE
'$passed{'FName'}*') AND .
(AuLastName LIKE
'$passed{'LName'}*') AND .
(AuMiddleInitial LIKE
'$passed{'Initial'}*') AND .
(AuSSN LIKE
'$passed{'SSN'}*')}
else {problem('Invalid invocation')}
print br.$select.$filter.' ORDER BY AuRINbr'; #debug
$connection = DBI-connect(dbi:ODBC:RATS,'RATSDefault','none') or problem('Cannot
connect to database');
$recordset = $connection-prepare($select.$filter.' ORDER BY AuRIN') or
problem('Cannot prepare SQL statement');
$recordset-execute() or problem('Cannot create recordset');
$count = 0;
while (@field=$recordset-fetchrow_array) {
$count++;
foreach (@field) {$_ ='' unless $_}
push @record, [EMAIL PROTECTED];
}
# $count = @record;
print br$countbr; #debug
if ($count 200) {
$count = 200;
$toomany = 'Too many records were returned for the selection criteria.brChange
the criteria and do again.';
}
else {$toomany = 'brbr'}
print 'font size=1.5 face=Arialform name=RecipAuth action=RecipAuthView.pl
method=post';
print 'table border=0 cellspacing=4 align=center';
print 'tr',tdfont($hidden),
tdfont($toomany),'/tr';
foreach (0..($count-1)) {
print 'tr',tdfont($record[$_][0]). #this is not the final display version of
course
tdfont($record[$_][1]).
tdfont($record[$_][2]).
tdfont($record[$_][3]).
tdfont($record[$_][4]).
tdfont($record[$_][5]).
tdfont($record[$_][6]),'/tr';
}
print '/table/form/font/body/html';
exit;
This is the displayed SQL statement:
SELECT TOP 201 AuUnique, AuRIN, AuCaseID, AuFirstName, AuLastName, AuMiddleInitial,
AuSSN FROM Authorize WHERE (AuFirstName LIKE
'*') AND (AuLastName LIKE 'W*') AND (AuMiddleInitial LIKE '*') ORDER BY AuRIN
If I remove the where clause and prepare as follows it returns records:
$recordset = $connection-prepare($select.' ORDER BY AuRIN') or problem('Cannot
prepare SQL statement');
Re: SQL statement to find and delete double entries
Jeff, Here is the syntax for doing this in Sybase as a stored procedure. Assumption: id is an int. CREATE PROCEDURE del_dupes AS BEGIN DECLARE @id int, @id_count int, @delete_rows int DECLARE read_keys CURSOR FOR SELECT id, count(*) FROM table GROUP BY id HAVING count(*) 1 OPEN read_keys FETCH read_keys INTO @id, @id_count WHILE( @@sqlstatus = 0 ) BEGIN SELECT @delete_rows = @id_count - 1 SET ROWCOUNT @delete_rows DELETE table WHERE id = @id FETCH read_keys INTO @id, @id_count END END GO Stored procedures are the fastest way to do it. As the data never leaves the server. Check with your RDBMS on correct syntax for your particular db. The same steps could be done in perl, but the data for the select is transmitted to the client and then each delete statement is passed, compiled, optimized and then executed. HTH, Chuck Fox Principal DBA America Online, INC [EMAIL PROTECTED] wrote: The only problem with that approach is that it deletes all of the entries and doesn't leave singles behind. I'd probably do it programatically. Grab the results of query 1, store the data in a hash of hashes, then do the delete and re-insert. But I'd love to hear an SQL solution to leaving one copy of each duplicate behind. Jeff Seger Fairchild Semiconductor [EMAIL PROTECTED] Christian Merz [EMAIL PROTECTED] 09/18/2003 08:33 AM To: Morrison, Trevor (Trevor) [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: SQL statement to find and delete double entries Hi, the basic idea to find duplicate or multiple values is: select id, count(*) from table group by id having count(*) 1; to delete ALL such values you may do this: delete from table where id in ( select id from table group by id having count(*) 1 ); cu, Christian - Original Message - From: Morrison, Trevor (Trevor) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 16, 2003 6:39 PM Subject: SQL statement to find and delete double entries Hi, What would be an SQL statement that will find duplicate order numbers in table and then delete them? TIA Trevor
Re: get the messages, not the rows
Dave, Sorry to hear about the lack of FLUSHMESSAGE in MS. I assumed that this command was still hanging around, after all, the code base for MS was originally Sybase.( many moons and version ago). Chuck [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2003 at 05:10:21PM -0700, David L. Good [EMAIL PROTECTED] wrote: On Tue, Sep 16, 2003 at 02:21:54PM -0400, Chuck Fox [EMAIL PROTECTED] wrote: Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. OK, but it works fine (no buffering) using DBD::Sybase on Unix platforms without using the 'SET FLUSHMESSAGE ON', but doesn't when using DBD::ODBC on Win32. I'll see if I can give your suggestion a try, though. It'd be great if it works! Hmmm. Sybase has FLUSHMESSAGE, but MS-SQL Server 2000 does not. Since I don't have Sybase's ODBC driver installed (and don't really need to access Sybase from Windows) I'm kinda stuck. In any case, I was interested in a solution for SQL Server, so I guess I'm out of luck :-(
Re: SQL statement to find and delete double entries
Avis, The use of a unique clustered index is an interesting solution, however, it requires 1.2 times the space currently consumed by the table and rewrites the table. IMHO, unless we are talking a major duplication i.e. 90% dupes, creating the clustered index will be slower. Chuck [EMAIL PROTECTED] wrote: If you are using Sybase, you can delete duplicate rows from a table by creating a unique clustered index 'with ignore_dup_row' (and then dropping it again if you don't want to ignore later duplicate inserts). I haven't benchmarked but I think this will be somewhat faster than a stored procedure. Anyway, this gets offtopic for the DBI list - if you want to write some SQL at the client for it, ask an SQL mailing list, and if you want to get the best performance on a particular RDBMS, ask a list for that system.
Re: get the messages, not the rows
Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. Chuck [EMAIL PROTECTED] wrote: On Mon, Sep 15, 2003 at 09:23:18AM -0400, Chuck Fox [EMAIL PROTECTED] wrote: David, In regards to the buffering, in Sybase and MS there is a set command SET FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to the OS. But the discussion was about using DBD::ODBC, not OpenClient (DBD::Sybase). Does it also work with ODBC?
Re: get the messages, not the rows
David,
In regards to the buffering, in Sybase and MS there is a set command SET
FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to
the OS.
HTH,
Chuck Fox
[EMAIL PROTECTED] wrote:
On Sat, Sep 13, 2003 at 01:08:27PM -0400, Jeff Urlwin [EMAIL PROTECTED] wrote:
Is there a way to execute an SQL command and get the
messages, not the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my
servers' health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Buried in the DBD::ODBC t/20SqlServer.t is an example. You need to set an
error handler and set odbc_async_exec. In there is the snippet that calls
dbcc TRACESTATUS(-1) which runs during the tests (the safest thing I think
I could find to run on generic tests, but I believe DBCC
CHECKDB('databasename') was the reason the original patches were submitted
by David L. Good and included by me... The only clear documentation is in
the Changes file. The Changes file also points to a specific file which
calls checkdb...
You don't really need to set odbc_async_exec unless you need to get the
messages as soon as possible after they are generated. In my case, I wanted
to be able to send an alert as soon as my program saw a DBCC error but still
let the DBCC continue. Unfortunately, it seems that ODBC buffers the output,
so even with odbc_async_exec set you get the messages in chunks, so an error
could sit in a buffer for quite some time (say, while a really large table
is being DBCC'd) before you'll see it.
use strict;
use DBI;
sub err_handler {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
print === state: $state msg: $msg\n;
return 0;
}
my $dbh = DBI-connect(dbi:ODBC:PERL_TEST_SQLSERVER, $ENV{DBI_USER},
$ENV{DBI_PASS})
|| die Can't connect: $DBI::errstr\n;
$dbh-{odbc_err_handler} = \err_handler;
$dbh-{odbc_async_exec} = 1;
print odbc_async_exec is: $dbh-{odbc_async_exec}\n;
my $sth;
$sth = $dbh-prepare(dbcc checkdb(model)) || die $dbh-errstr;
$sth-execute || die $dbh-errstr;
$sth-finish;
$dbh-disconnect;
Regards,
Jeff
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just
did not find it:) = [EMAIL PROTECTED] ===
http://Jenda.Krynicky.cz = When it comes to wine, women
and song, wizards are allowed to get drunk and croon as much
as they like.
-- Terry Pratchett in Sourcery
Re: Problem connecting to non-default db
[EMAIL PROTECTED] wrote:
Hi,
Hello,
I'm trying to connect to two ms sqlserver 2000 databases with the same schema
(let's call them
dbA and dbB) using the following code:
my $connect_string = dbi:Sybase:dbname=dbA:server=MyServer;
$connect_string .= ;host=192.168.0.2;port=1433;
my $dbh = DBI-connect($connect_string, $username, $userpass, {PrintError =
0});
die Unable for connect to server $DBI::errstr
unless $dbh;
and in my freetds.conf I have:
[MyServer]
host = 192.168.0.2
port = 1433
tds version = 4.2
try domain login = no
try server login = yes
I have read access to both dbA and dbB. However if dbB is my default db and I
specify dbA as the dbname (as in the example above), or vice versa, when my
script executes the following:
Why not just use dbB or whichever is appropriate to the query ? I
seem to remember some bug in Sybase DBD that prevented it from
using/processing the database specified in the connect string.
$sth = $dbh-prepare(select count(*) from array);
die Unable for connect to server $DBI::errstr unless $sth;
if($sth-execute) {
while(my @dat = $sth-fetchrow) {
print @dat\n;
}
}
the data is retrieved from the default db rather than the one I specified (btw,
yes, i know for sure that the select count(*) from array on both db's should
return different numbers). So I think that DBI's only connecting to the
default db.
As well, the statment
print Data sources: . ($dbh-data_sources()) . \n $DBI::errstr\n;
prints out no data sources.
The DBD I'm using is DBD::Sybase running on the freetds library. My script's
running on a linux box and the sqlserver 2000 db's are on a windows 2000 adv
server box.
So any ideas on how I may get around this problem? I would really appreciate
any help.
Thanks,
Take care,
Bilal
HTH,
Chuck Fox
Principal DBA
America Online, INC
Re: Problem connecting to non-default db
Bilal,
Try just executing use dbA after the connection is made. Another
option is to directly reference the db in the query by using select *
from dbA.dbo.array. A table can be referenced as tableName or
ownerName.tableName or as databaseName.ownerName.tableName. Insofar as
the bug, try ugrading to the latest dbd, I believe that 1.01 was just
released. We are using 1.0 and the issue with the db disappeared
there. I think the fix was in the 0.94 release.
Chuck
[EMAIL PROTECTED] wrote:
Hi,
Do you mean replace dbname=dbA with dbname=dbB in my connect string?
I've tried that. Is there another way to switch the data source to
another db?
Is there any fix for this DBD::Sybase bug?
Thanks,
Bilal
On Thu, 11 Sep 2003, Chuck Fox wrote:
[EMAIL PROTECTED] wrote:
Hi,
Hello,
I'm trying to connect to two ms sqlserver 2000 databases with the same schema
(let's call them
dbA and dbB) using the following code:
my $connect_string = dbi:Sybase:dbname=dbA:server=MyServer;
$connect_string .= ;host=192.168.0.2;port=1433;
my $dbh = DBI-connect($connect_string, $username, $userpass, {PrintError =
0});
die Unable for connect to server $DBI::errstr
unless $dbh;
and in my freetds.conf I have:
[MyServer]
host = 192.168.0.2
port = 1433
tds version = 4.2
try domain login = no
try server login = yes
I have read access to both dbA and dbB. However if dbB is my default db and I
specify dbA as the dbname (as in the example above), or vice versa, when my
script executes the following:
Why not just use dbB or whichever is appropriate to the query ? I
seem to remember some bug in Sybase DBD that prevented it from
using/processing the database specified in the connect string.
$sth = $dbh-prepare(select count(*) from array);
die Unable for connect to server $DBI::errstr unless $sth;
if($sth-execute) {
while(my @dat = $sth-fetchrow) {
print @dat\n;
}
}
the data is retrieved from the default db rather than the one I specified (btw,
yes, i know for sure that the select count(*) from array on both db's should
return different numbers). So I think that DBI's only connecting to the
default db.
As well, the statment
print Data sources: . ($dbh-data_sources()) . \n $DBI::errstr\n;
prints out no data sources.
The DBD I'm using is DBD::Sybase running on the freetds library. My script's
running on a linux box and the sqlserver 2000 db's are on a windows 2000 adv
server box.
So any ideas on how I may get around this problem? I would really appreciate
any help.
Thanks,
Take care,
Bilal
HTH,
Chuck Fox
Principal DBA
America Online, INC
Re: What to do with UTF-8 data?
Steve,
I am a Sybase DBA, but in situations like this, I have declared the
column on the table to be varbinary or binary and stored the data
directly without conversion. Don't know if MySql supports this datatype.
HTH,
Chuck Fox
Principal Database Adminstrator
America Online, INC.
[EMAIL PROTECTED] wrote:
Hi,
Having looked through the list archives a bit, I see there has been
lots of discussion over what to do with UTF-8 data, but I can't find
anything that really helps me out now.
I'm running Perl 5.8.0 with MySQL 3.23.56 (via DBD-mysql). Since (I
think) there is no native UTF-8 support in MySQL below 4.1.x, my plan
was to simply store the bytes of each UTF-8 character in the database
(either by explicitly converting Perl's UTF-8 strings to sequences of
octets using Encode::encode_utf8(), or else just letting that happen
of its own accord as it seems to do :-s), and then convert such octet
sequences back to UTF-8 strings when retrieving data from the database
using Encode::decode_utf8().
As long as I store *all* my data in this way, those conversions should
always succeed. (encode_utf8() never fails anyway, and decode_utf8()
will always work here because I'm always feeding it valid data.)
The problem is: How do I trap all input/output to/from DBI to do these
conversions?
I can easily do it manually:
$dbh-do('INSERT INTO foo (bar) VALUES (?)', undef,
Encode::encode_utf8($input_utf8str));
...
my @octets_row = $dbh-selectrow_array('SELECT bar FROM foo');
my $output_utf8str2 = Encode::decode_utf8($octets_row[0]);
but that's way too tedious in practice. I want to have those
conversions done for me automatically, perhaps by having specified
some appropriate encode/decode callbacks for certain hooks. Do any
such hooks exist?
If, as I suspect, there are not, then what is the currently
recommended solution here?
Would it be at all easy to add such hooks, or some other means of
specifying that I want these conversions done, to DBI as a stop-gap
solution?
Thanks,
- Steve
Re: deadlock errors w/Perl DBD::Sybase and MS SQL 2000
Ahhh, my favorite error code, the infamous 1205. Obviously the insert
statement is not the problem, its the trigger that is being called.
Please provide source for the trigger and perhaps I can help.
Chuck
[EMAIL PROTECTED] wrote:
I have a small perl script using DBI and DBD::Sybase as well as
DBD::MySQL
It pops mail for a feedback system, after parsing the message to see
if it's good or bad it then inserts good messages (customer feedback)
into a Microsoft SQL 2000 database or if bad inserts them into a MySQL
database.
Recently the Coldfusion developers and SQL admin's decided to rework
things and put all the different site feedback tables into one table.
So I had to change my code to just update this one table with all the
site feedback.
But since my perl code runs from the crontab sometimes jobs overlap
and I might be popping mail for more then one site's feedback address
at a time and thus making multiple inserts into the MS SQL database
server. Then I get these damn deadlock errors.
What's odd is I never get errors with MySQL, I can have all 11 site
pop scripts running at the same time inserting bounced messages into
the MySQL table without problem. But just 2 scripts trying to insert
good feedback into Microsoft SQL crap out.
Is it my code? The driver? The way our SQL guys built the database?
I just recently added the syb_deadlock_retry = 3 , syb_deadlock_sleep
= 5 commands hoping that would help, but it did't.
Below is the code and error any insight would be very much appreciated.
Also I am using FreeTDS and have been using the same drivers and
FreeTDS for 3 years. Without problems.
Thanks
zack
DBD::Sybase::st execute failed: Server message number=1205 severity=13
state=50 line=5 server=SQL82 procedure=trg_cleanup text=Transaction
(Process ID 92) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction. at
./dating_pop2sql.pl line 448.
Can't execute the SQL statement: Server message number=1205
severity=13 state=50 line=5 server=SQL82 procedure=trg_cleanup
text=Transaction (Process ID 92) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
1205
S1000 at ./dating_pop2sql.pl line 448.
Segmentation fault
Perl Code in question:
sub insert_good_data {
my ($dsquery, $data_base, $data_source, $username, $password) =
@_;
($dbh) = DBI-connect($data_source, $username,
$password, { syb_deadlock_retry = 3 , syb_deadlock_sleep = 5 })
|| die Can't connect: $DBI::errstr;
$message{to} = substr($message{to},0, 49);
$message{from} = substr($message{from},0, 49);
$message{date} = substr($message{date},0, 49);
$message{subject} = substr($message{subject},0, 49);
$sql_statement = sprintf INSERT INTO feedback
(site_id, category, status, to_email,
message_header, message_subject, from_email,program_name,message_body)
VALUES
('25','1','1',%s,%s,%s,%s,'perlpop',%s),
$dbh-quote($message{from}),
$dbh-quote($message{headers}),
$dbh-quote($message{subject}),
$dbh-quote($message{to}),
$dbh-quote($message{message_body});
$sth = $dbh-prepare($sql_statement)
|| die Can't prepare the SQL statement:
$DBI::errstr $DBI::err \n $DBI::state;
$sth-execute || die Can't execute the SQL statement:
$DBI::errstr \n $DBI::err \n $DBI::state;
$dbh-disconnect;
}
_
Get MSN 8 and enjoy automatic e-mail virus protection.
http://join.msn.com/?page=features/virus
