Re: How to Connect MSSQL Server from a Linux machine using Perl
Stephane Perennes(71 91, Mascotte)
On 8 Feb 2003, Jeyaraj wrote:
I am new to this environment I don't know how to establish connection
to SQL Server from Linux machine using Perl Pls help me in this
issue
Expecting your favourable answers
Thanx in advance.
Regards,
Jeyaraj
Your question is answered in many FAQs,
see :
http://perl.about.com/gi/dynamic/offsite.htm?site=http%3A%2F%2Fwww.extropia.com%2Ftutorials%2Fsql%2Fdbi_api.html
but it's not a very good tutorial i would even say it's bad ..
Since i use now php, i have only old examples, at that time i was using
perl just intuitively (i still have to read an manual one day )
use DBI;
$dsn = DBI:$driver:host=$host;port=$port;
This more or less tell that you connect to a mysql database
on host $host and port $port, the port is usually the standard one for
mysqld. Host can be either the IP or the name of the host.
$dbh = DBI-connect($dsn, $user, $password) ;
You can probably specify here the database too, here it's omitted
This establishes the connection with user and password.
Indeed it creates a Data Base handle that you will use to talk to the
database.
So yu have 2 steps :
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
That can be made as one
my $dbh = DBI-connect(DBI:mysql:database=$database;host=$host;port=$port,
$user, $password,);
database=$database can be omittted.
ans usually host and port too (assuming that host is localhost and the
port mysqld default port)
Now you have the DBH (data base handle)
test if it's ok
if (! ($dbh))
{
SQLERROR(Connection de $user depuis $host refuse par le serveur);
return ( (01));
}
olog(Nouvelle connection Reussie);
return(1);
$dbh has some error checking functions : err(), state() , errstr()
$dbh-errstr retunr the sql error text, err() is an error code, state()
must be documented somewhere.
Now you can use $dbh to perform a request simply call some crappy
function like EASYDO($req)
sub EASYDO
{
my $req= shift;
if ( (!dbh) || (!defined($dbh)) )
{ treat the error , }
$u= $dbh-do($req) ;
if ($u) { it's ok }
else { trouble, dbh-errstr contains an error message );
}
In the case before you don't need the result of the request,
you just need to be sure that the request was done.
You can also prepare the request before doing it,
it then works like Mysql, you just need to take some care about special
characters when writing requests.
You do something like
EASYDO(use MYDatabase);
unless you specified the database when connecting.
$req=Select ID Name from Peoples
$sth = $dbh-prepare($req);
$sth-execute
then $sth contains the request result
(you can test that things are ok by testing $sth)
{
my $ref;
while( $ref = $sth-fetchrow_arrayref )
{ print ID is $$ref[0] Name is is $$ref[1]\n }
}
Here $sth-fetchrow_arrayref return the next row of the resulting table as
an array.
$ref refers to it.
You can also get associative array, and also the name of the resulting
table fields, using fetchrow_hashref()
$sth-execute();
while (my $ref = $sth-fetchrow_hashref()) {
print Found a row: id = $ref-{'ID'}, name = $ref-{'NAME'}\n;
}
There is much more, see CPAN DBI module reference.
$sth-rows; (numer of rows returned or affected)
$sth-{'NUM_OF_FIELDS'}; (number of fields is the
resulting table)
$sth-{Names'} Names of the columns of the resulting table.
Re: How to Connect MSSQL Server from a Linux machine using Perl
On 8 Feb 2003 11:58:56 -, Jeyaraj wrote: I am new to this environment I don't know how to establish connection to SQL Server from Linux machine using Perl Pls help me in this issue It's a FAQ: http://tlowery.hypermart.net/perl_dbi_dbd_faq.html See questions 4 through 7. -- Bart.
Re: need help!
What exactly does 'does not seem to work anymore mean'? Error messages? What platform? Have you read the README's, and googled for answers? Jared On Thursday 06 February 2003 09:34, [EMAIL PROTECTED] wrote: Recently we upgraded to Oracle 9i and the DBD::Oracle-1.12 does not seem to work anymore. The CPAN documentation says, it was for Oracle 7 and 8. We've Oracle 9.2.0 Please help ?!!! regards surjit This message contains information from Equifax Inc. which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail [EMAIL PROTECTED]
Re: Newbie problem with Oracle functions
When troubleshooting Perl/DBI/Oracle problems such as this, the first thing you should do is try the operation from sqlplus. If it won't work in sqlplus, it certainly won't work from DBI. Jared On Thursday 06 February 2003 21:46, David Bakkers wrote: At 03:40 06/02/03 +, you wrote: Date: Wed, 05 Feb 2003 09:19:14 +1100 To: [EMAIL PROTECTED] From: David Bakkers [EMAIL PROTECTED] Subject: Newbie problem with Oracle functions Many thanks to those that replied and helped me out. Steve Kell's transcript of the Database Error Messages for PLS-00201 sparked me to look at the Oracle database and check the access rights to that function. Bingo ! The username / password I was connecting with did not have access to that function. I added the user and was in business :) The other problem I encounter was binding the input parameter as the correct type. If I used... $func-bind_param(:parameter1, 993193); it worked, but if I used... $func-bind_param(:parameter1, 020123); I got an error ORA-01008: not all variables bound. It seemed that the zero was being dropped from the parameter value being passed to the function, which was rejecting it because it expects a six digit number (we have some Student numbers with a leading zero). Keeping the the value enclosed in comments thus... $func-bind_param(:parameter1, '020123'); gets it correctly bound and passed. David Bakkers, Systems Administrator. Central College, Sydney, Australia. Ph:(02) 8217 9609. Fax:(02) 9211 0628 reply to [EMAIL PROTECTED]
Re: Oracle 8.1.7 connect
From sqlplus: select * from v$nls_parameters' Here you will find the actual character set. Ask you DBA if the character set was changed during the upgrade. There are special procedures for this, and you can get unexpected results if not followed properly. Jared On Saturday 25 January 2003 17:52, Jeff Gross wrote: I have a problem connecting to Oracle 8.1.7 with dbi I have been using the Perl DBI to connect to Oracle with success until an upgrade to Oracle 8.1.7. Now dbi will not connect unless I have the following environment variable setting setenv NLS_LANG AMERICAN_AMERICA.US7ASCII The problem is that I am retrieving Eastern European character from Oracle and some of them are translated into question marks ?. The Oracle database administrator suggested that I use the following env setting setenv NLS_LANG AMERICAN_AMERICA.EE8ISO8859P2. The problem with this setting is that the DBI connect fails. The error message is DBI-connect failed: ORA-12705: invalid or unknown NLS parameter value specified (DBD: login failed) at oracle_connect.pl line 10 Unable to connect: ORA-12705: invalid or unknown NLS parameter value specified (DBD: login failed) at oracle_connect.pl line 10. Thank you for your time and any help is most appreciated. Jeff Gross _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus
Re: Oracle select of date fields
This should work as Michael stated, I've used it frequently from DBI myself. Try it from sqlplus and see if it works properly there. If not, you have a non-DBI problem. Jared On Monday 27 January 2003 08:52, Doug Hughes wrote: On Sun, 26 Jan 2003, Michael A Chase wrote: On Mon, 27 Jan 2003 00:24:58 -0700 (MST) Doug Hughes [EMAIL PROTECTED] wrote: I've got some fields in an oracle table where the internal format is date. I'm currently using a select * type query to return all the colums of the table (36), and the date fields come back as 'MM/DD/'. I could enumerate every column and for each data field do a select to_char(field,'MM/DD/ HH24:MI:SS'), but that seems rather tedious and I was thinking there must be a better way. Is there some way to do the equivalent of an alter system to set the NLS_DATE_FORMAT for retrieving date fields? You'll need to check the fine Oracle SQL manual to be sure, but I think ALTER SESSION SET NLS DATE FORMAT TO '/MM/DD HH24:MI:SS' or something similar would do what you want. I recommend putting the year first since that makes sorting and parsing easier. yup. Tried that. put it in a $dbh-do $dbsrc-do(ALTER SESSION SET NLS_DATE_FORMAT='/MM/DD HH24:MI:SS') || die $dbsrc-errstr; It executes just fine, but it doesn't effect the date results I get in the query. To see what I mean try something like this: $dbsrc-do(ALTER SESSION SET NLS_DATE_FORMAT='/MM/DD HH24:MI:SS') || die $dbsrc-errstr; print $_ for $dbsrc-selectrow_array(select sysdate from dual) or die Couldn't get date $DBI-errstr; it still shows as 24-JAN-03 for me.
Re: Oracle select of date fields
To be pedantic, Oracle doesn't care what the date format is, it will always sort properly on dates regardless of format. Sorting within perl is another matter: put the year first. Jared On Sunday 26 January 2003 23:46, Michael A Chase wrote: On Mon, 27 Jan 2003 00:24:58 -0700 (MST) Doug Hughes [EMAIL PROTECTED] wrote: I've got some fields in an oracle table where the internal format is date. I'm currently using a select * type query to return all the colums of the table (36), and the date fields come back as 'MM/DD/'. I could enumerate every column and for each data field do a select to_char(field,'MM/DD/ HH24:MI:SS'), but that seems rather tedious and I was thinking there must be a better way. Is there some way to do the equivalent of an alter system to set the NLS_DATE_FORMAT for retrieving date fields? You'll need to check the fine Oracle SQL manual to be sure, but I think ALTER SESSION SET NLS DATE FORMAT TO '/MM/DD HH24:MI:SS' or something similar would do what you want. I recommend putting the year first since that makes sorting and parsing easier.
multiple MYSQL
Hi, I'm running two versions of mysql (with different ports) on a server. How do I make DBI connect to the instance of mysql with the non-default-port. TIA.
Re: multiple MYSQL
At 8:23 +0800 2/10/03, Jun Beldad wrote: Hi, I'm running two versions of mysql (with different ports) on a server. How do I make DBI connect to the instance of mysql with the non-default-port. TIA. Specify the port number in your DSN. Also, if the server is on the local host, specify the host name as 127.0.0.1 or the host's actual name rather than as localhost. At least, do that if you're on Unix, for which connections to localhost will use a socket file rather than TCP/IP and a port number.
