DBI DBD-ODBC

--=_alternative 005A160985257337_=
Content-Type: text/plain;
charset="US-ASCII"

I created an MSACCESS Database on my Windows XP O/S, set up the System
Name in the DATA SOURCES panel associating the "Microsoft Access
Driver(*.mdb) with my database name (db1). I installed the DBI Module
and DBD-ODBC and I still get the following error when I run my PERL
script..

Could not make a connection to the database: [Microsoft][ODBC Microsoft
Access D
river]General error Not enough information to connect to this DSN with
SQLConnec
t. Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at
C:\Do
cuments and Settings\O015181\Desktop\Edit1.pl line 8.


If anyone knows the reason for this error and how I can fix it, I would
appreciate it very much. I am sending this email as a last resort as I
have exhausted
all of my resources.

Regards,

Robert V Simmons

This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates. This transmission may contain information that is
privileged, confidential, legally privileged, and/or exempt from
disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of the information contained herein (including
any reliance thereon) is STRICTLY PROHIBITED. Although this
transmission 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 JPMorgan Chase & Co., its subsidiaries and affiliates,
as applicable, for any loss or damage arising in any way from its
use. If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to UK legal entities.

--=_alternative 005A160985257337_=--

Re: DBI DBD-ODBC

robert.v.simmons@jpmorgan.com wrote:
> I created an MSACCESS Database on my Windows XP O/S, set up the System
> Name in the DATA SOURCES panel associating the "Microsoft Access
> Driver(*.mdb) with my database name (db1). I installed the DBI Module
> and DBD-ODBC and I still get the following error when I run my PERL
> script..
>
> Could not make a connection to the database: [Microsoft][ODBC Microsoft
> Access D
> river]General error Not enough information to connect to this DSN with
> SQLConnec
> t. Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at
> C:\Do
> cuments and Settings\O015181\Desktop\Edit1.pl line 8.
>
>
> If anyone knows the reason for this error and how I can fix it, I would
> appreciate it very much. I am sending this email as a last resort as I
> have exhausted
> all of my resources.
>
> Regards,
>
> Robert V Simmons
>

Did you use:

DBI->connect('dbi:ODBC:mydsn');

or

DBI->connect('dbi:ODBC:DSN=mydsn');

I would try the latter.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBI DBD-ODBC

I am having almost the same error on a windows 2003 server. Same set of
scripts run fine on a Windows XP,
the following is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at c:\web\factory.pl line
61.
it failed at the line to execute after prepared the sql.I wish someone will
shed some light on this. Thanks.I am using the format of
DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?the sample
code as following:my ($dbh, $sth);my $dsn = "dbi:ODBC:btn";my $user
="";my $pswrd ="";my $driver = "ODBC";my $wkno;use Time::gmtime;my
$ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
"$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE wkmaster.refno
like '$ord_num%'"; print $starter; $dbh =
DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die "Database
connection not made: $DBI::errstr"; $sth = $dbh->prepare($sql);
$sth->execute();It failed at the last line shown above. I highly appreciate
your help!Samuel
----- Original Message -----
From: "Martin Evans"
To: "dbi-users"
Sent: Wednesday, August 15, 2007 3:31 AM
Subject: Re: DBI DBD-ODBC


> robert.v.simmons@jpmorgan.com wrote:
>> I created an MSACCESS Database on my Windows XP O/S, set up the System
>> Name in the DATA SOURCES panel associating the "Microsoft Access
>> Driver(*.mdb) with my database name (db1). I installed the DBI Module
>> and DBD-ODBC and I still get the following error when I run my PERL
>> script..
>>
>> Could not make a connection to the database: [Microsoft][ODBC Microsoft
>> Access D
>> river]General error Not enough information to connect to this DSN with
>> SQLConnec
>> t. Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect err=-1)
>> at C:\Do
>> cuments and Settings\O015181\Desktop\Edit1.pl line 8.
>>
>>
>> If anyone knows the reason for this error and how I can fix it, I would
>> appreciate it very much. I am sending this email as a last resort as I
>> have exhausted
>> all of my resources.
>>
>> Regards,
>>
>> Robert V Simmons
>>
>
> Did you use:
>
> DBI->connect('dbi:ODBC:mydsn');
>
> or
>
> DBI->connect('dbi:ODBC:DSN=mydsn');
>
> I would try the latter.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> __________ NOD32 2461 (20070815) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Re: DBI DBD-ODBC

Samuel_Zheng wrote:
> I am having almost the same error on a windows 2003 server.

I don't see this as the same. Robert is having a connection problem and
you are having an execute problem.

> Same set of
> scripts run fine on a Windows XP,
> the following is the error:
> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
> 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
> c:\web\factory.pl line 61.

As I said when you posted this before - What does "囀湔祛堤" mean in
English? Error 22018 in access, is "Invalid character value for cast
specification" which is described for SQLExecute as:

"StatementText contained a C type that was an exact or approximate
numeric, a datetime, or an interval data type; the SQL type of the
column was a character data type; and the value in the column was not a
valid literal of the bound C type."

See
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018

I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in your
code. What type is column refno, what is in $ord_num? What does an ODBC
trace contain?


> it failed at the line to execute after prepared the sql.I wish someone
> will shed some light on this. Thanks.I am using the format of
> DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?

The difference is that DBD::ODBC calls the newer ODBC connection API
SQLDriverConnect first then if this fails it attempts to use the older
ODBC API SQLConnect. If you are using a DSN then omitting DSN= will
cause SQLDriverConnect to attempt to connect using the DEFAULT data
source which for most people does not exist and hence fails. When
SQLDriverConnect fails DBD::ODBC will have a second attempt with the
older ODBC API SQLConnect.

I believe this is historical in that initially:

DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')

(before ODBC 3) used to call SQLConnect and works fine so long as the
DSN mydsn_name exists but SQLConnect is less flexible than the newer
SQLDriverConnect which supports new ODBC attributes and driver-specific
attributes which are required for say DSN-less connections.

> the
> sample code as following:my ($dbh, $sth);my $dsn = "dbi:ODBC:btn";my
> $user ="";my $pswrd ="";my $driver = "ODBC";my $wkno;use Time::gmtime;my
> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die "Database
> connection not made: $DBI::errstr"; $sth = $dbh->prepare($sql);
> $sth->execute();It failed at the last line shown above. I highly
> appreciate your help!Samuel

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBI DBD-ODBC

--=_alternative 0045A77785257338_=
Content-Type: text/plain;
charset="US-ASCII"

Yes that is the statement I am using in my PERL script.


Robert V Simmons



Martin Evans
08/15/2007 03:31 AM

To
dbi-users
cc

Subject
Re: DBI DBD-ODBC






robert.v.simmons@jpmorgan.com wrote:
> I created an MSACCESS Database on my Windows XP O/S, set up the System
> Name in the DATA SOURCES panel associating the "Microsoft Access
> Driver(*.mdb) with my database name (db1). I installed the DBI Module

> and DBD-ODBC and I still get the following error when I run my PERL
> script..
>
> Could not make a connection to the database: [Microsoft][ODBC Microsoft
> Access D
> river]General error Not enough information to connect to this DSN with
> SQLConnec
> t. Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect err=-1)
at
> C:\Do
> cuments and Settings\O015181\Desktop\Edit1.pl line 8.
>
>
> If anyone knows the reason for this error and how I can fix it, I would
> appreciate it very much. I am sending this email as a last resort as I
> have exhausted
> all of my resources.
>
> Regards,
>
> Robert V Simmons
>

Did you use:

DBI->connect('dbi:ODBC:mydsn');

or

DBI->connect('dbi:ODBC:DSN=mydsn');

I would try the latter.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com



This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates. This transmission may contain information that is
privileged, confidential, legally privileged, and/or exempt from
disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of the information contained herein (including
any reliance thereon) is STRICTLY PROHIBITED. Although this
transmission 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 JPMorgan Chase & Co., its subsidiaries and affiliates,
as applicable, for any loss or damage arising in any way from its
use. If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to UK legal entities.

--=_alternative 0045A77785257338_=--

Re: DBI DBD-ODBC

robert.v.simmons@jpmorgan.com wrote:
>
> Yes that is the statement I am using in my PERL script.
>
>
> Robert V Simmons
>

Can you run your script with DBI_TRACE set to a number >= 10

e.g.
set DBI_TRACE=10=x.log
myperlscript

then send me the x.log file.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com


> *Martin Evans *
>
> 08/15/2007 03:31 AM
>
>
> To
> dbi-users
> cc
>
> Subject
> Re: DBI DBD-ODBC
>
>
>
>
>
>
>
>
> robert.v.simmons@jpmorgan.com wrote:
> > I created an MSACCESS Database on my Windows XP O/S, set up the System
> > Name in the DATA SOURCES panel associating the "Microsoft Access
> > Driver(*.mdb) with my database name (db1). I installed the DBI Module
> > and DBD-ODBC and I still get the following error when I run my PERL
> > script..
> >
> > Could not make a connection to the database: [Microsoft][ODBC Microsoft
> > Access D
> > river]General error Not enough information to connect to this DSN with
> > SQLConnec
> > t. Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect
> err=-1) at
> > C:\Do
> > cuments and Settings\O015181\Desktop\Edit1.pl line 8.
> >
> >
> > If anyone knows the reason for this error and how I can fix it, I would
> > appreciate it very much. I am sending this email as a last resort as I
> > have exhausted
> > all of my resources.
> >
> > Regards,
> >
> > Robert V Simmons
> >
>
> Did you use:
>
> DBI->connect('dbi:ODBC:mydsn');
>
> or
>
> DBI->connect('dbi:ODBC:DSN=mydsn');
>
> I would try the latter.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> ------------------------------------------------------------ ------------
>

Re: DBI DBD-ODBC

Thank you Martin for your response. "囀湔祛堤" is not readable, no one knows
what it means. refno is a string type and $ord_num is also string type
contains number characters. e.g. "88090"
I just don't understand the same codes run on XP is ok but not on Windows
2003 server. the only thing I can think of is the permision issue. BUT
proper rights are given to the a/c runs the script.

Samuel

----- Original Message -----
From: "Martin Evans"
To: "dbi-users"
Sent: Wednesday, August 15, 2007 8:26 AM
Subject: Re: DBI DBD-ODBC


> Samuel_Zheng wrote:
>> I am having almost the same error on a windows 2003 server.
>
> I don't see this as the same. Robert is having a connection problem and
> you are having an execute problem.
>
>> Same set of scripts run fine on a Windows XP,
>> the following is the error:
>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
>> 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
>> c:\web\factory.pl line 61.
>
> As I said when you posted this before - What does "囀湔祛堤" mean in
> English? Error 22018 in access, is "Invalid character value for cast
> specification" which is described for SQLExecute as:
>
> "StatementText contained a C type that was an exact or approximate
> numeric, a datetime, or an interval data type; the SQL type of the column
> was a character data type; and the value in the column was not a valid
> literal of the bound C type."
>
> See
> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>
> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in your
> code. What type is column refno, what is in $ord_num? What does an ODBC
> trace contain?
>
>
>> it failed at the line to execute after prepared the sql.I wish someone
>> will shed some light on this. Thanks.I am using the format of
>> DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?
>
> The difference is that DBD::ODBC calls the newer ODBC connection API
> SQLDriverConnect first then if this fails it attempts to use the older
> ODBC API SQLConnect. If you are using a DSN then omitting DSN= will cause
> SQLDriverConnect to attempt to connect using the DEFAULT data source which
> for most people does not exist and hence fails. When SQLDriverConnect
> fails DBD::ODBC will have a second attempt with the older ODBC API
> SQLConnect.
>
> I believe this is historical in that initially:
>
> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>
> (before ODBC 3) used to call SQLConnect and works fine so long as the DSN
> mydsn_name exists but SQLConnect is less flexible than the newer
> SQLDriverConnect which supports new ODBC attributes and driver-specific
> attributes which are required for say DSN-less connections.
>
>> the sample code as following:my ($dbh, $sth);my $dsn =
>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>> $wkno;use Time::gmtime;my
>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE wkmaster.refno
>> like '$ord_num%'"; print $starter; $dbh =
>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die "Database
>> connection not made: $DBI::errstr"; $sth = $dbh->prepare($sql);
>> $sth->execute();It failed at the last line shown above. I highly
>> appreciate your help!Samuel
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> __________ NOD32 2463 (20070815) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Re: DBI DBD-ODBC

Samuel_Zheng wrote:
> Thank you Martin for your response. "囀湔祛堤" is not readable, no one
> knows what it means. refno is a string type and $ord_num is also string
> type contains number characters. e.g. "88090"
> I just don't understand the same codes run on XP is ok but not on
> Windows 2003 server. the only thing I can think of is the permision
> issue. BUT proper rights are given to the a/c runs the script.
>
> Samuel

So try and narrow it down. Does it happen on all values of refno or just
one particular one? Does it still happen if you remove the where clause
from your select statement altogether. If it is none of that start
taking columns out the select - start with duedate.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> ----- Original Message ----- From: "Martin Evans"
>
> To: "dbi-users"
> Sent: Wednesday, August 15, 2007 8:26 AM
> Subject: Re: DBI DBD-ODBC
>
>
>> Samuel_Zheng wrote:
>>> I am having almost the same error on a windows 2003 server.
>>
>> I don't see this as the same. Robert is having a connection problem
>> and you are having an execute problem.
>>
>>> Same set of scripts run fine on a Windows XP,
>>> the following is the error:
>>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
>>> Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
>>> c:\web\factory.pl line 61.
>>
>> As I said when you posted this before - What does "囀湔祛堤" mean in
>> English? Error 22018 in access, is "Invalid character value for cast
>> specification" which is described for SQLExecute as:
>>
>> "StatementText contained a C type that was an exact or approximate
>> numeric, a datetime, or an interval data type; the SQL type of the
>> column was a character data type; and the value in the column was not
>> a valid literal of the bound C type."
>>
>> See
>> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>>
>>
>> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in
>> your code. What type is column refno, what is in $ord_num? What does
>> an ODBC trace contain?
>>
>>
>>> it failed at the line to execute after prepared the sql.I wish
>>> someone will shed some light on this. Thanks.I am using the format of
>>> DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?
>>
>> The difference is that DBD::ODBC calls the newer ODBC connection API
>> SQLDriverConnect first then if this fails it attempts to use the older
>> ODBC API SQLConnect. If you are using a DSN then omitting DSN= will
>> cause SQLDriverConnect to attempt to connect using the DEFAULT data
>> source which for most people does not exist and hence fails. When
>> SQLDriverConnect fails DBD::ODBC will have a second attempt with the
>> older ODBC API SQLConnect.
>>
>> I believe this is historical in that initially:
>>
>> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>>
>> (before ODBC 3) used to call SQLConnect and works fine so long as the
>> DSN mydsn_name exists but SQLConnect is less flexible than the newer
>> SQLDriverConnect which supports new ODBC attributes and driver-specific
>> attributes which are required for say DSN-less connections.
>>
>>> the sample code as following:my ($dbh, $sth);my $dsn =
>>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>>> $wkno;use Time::gmtime;my
>>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
>>> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
>>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
>>> "Database connection not made: $DBI::errstr"; $sth =
>>> $dbh->prepare($sql); $sth->execute();It failed at the last line shown
>>> above. I highly appreciate your help!Samuel
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>> __________ NOD32 2463 (20070815) Information __________
>>
>> This message was checked by NOD32 antivirus system.
>> http://www.nod32.com.hk
>>
>>
>
>

Re: DBI DBD-ODBC

Thank you for your input. I was going to try what you told me, but it gives
this to me now:
CGI Error
The specified CGI application misbehaved by not returning a complete set of
HTTP headers.

I now give up on Windows 2003 completely. Falling back on the XP.
I think I'll reinstalling the server.

Samuel

----- Original Message -----
From: "Martin Evans"
To: "dbi-users"
Sent: Wednesday, August 15, 2007 10:59 AM
Subject: Re: DBI DBD-ODBC


> Samuel_Zheng wrote:
>> Thank you Martin for your response. "囀湔祛堤" is not readable, no one
>> knows what it means. refno is a string type and $ord_num is also string
>> type contains number characters. e.g. "88090"
>> I just don't understand the same codes run on XP is ok but not on Windows
>> 2003 server. the only thing I can think of is the permision issue. BUT
>> proper rights are given to the a/c runs the script.
>>
>> Samuel
>
> So try and narrow it down. Does it happen on all values of refno or just
> one particular one? Does it still happen if you remove the where clause
> from your select statement altogether. If it is none of that start taking
> columns out the select - start with duedate.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>> ----- Original Message ----- From: "Martin Evans"
>>
>> To: "dbi-users"
>> Sent: Wednesday, August 15, 2007 8:26 AM
>> Subject: Re: DBI DBD-ODBC
>>
>>
>>> Samuel_Zheng wrote:
>>>> I am having almost the same error on a windows 2003 server.
>>>
>>> I don't see this as the same. Robert is having a connection problem and
>>> you are having an execute problem.
>>>
>>>> Same set of scripts run fine on a Windows XP,
>>>> the following is the error:
>>>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
>>>> 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
>>>> c:\web\factory.pl line 61.
>>>
>>> As I said when you posted this before - What does "囀湔祛堤" mean in
>>> English? Error 22018 in access, is "Invalid character value for cast
>>> specification" which is described for SQLExecute as:
>>>
>>> "StatementText contained a C type that was an exact or approximate
>>> numeric, a datetime, or an interval data type; the SQL type of the
>>> column was a character data type; and the value in the column was not a
>>> valid literal of the bound C type."
>>>
>>> See
>>> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>>>
>>> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in your
>>> code. What type is column refno, what is in $ord_num? What does an ODBC
>>> trace contain?
>>>
>>>
>>>> it failed at the line to execute after prepared the sql.I wish someone
>>>> will shed some light on this. Thanks.I am using the format of
>>>> DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?
>>>
>>> The difference is that DBD::ODBC calls the newer ODBC connection API
>>> SQLDriverConnect first then if this fails it attempts to use the older
>>> ODBC API SQLConnect. If you are using a DSN then omitting DSN= will
>>> cause SQLDriverConnect to attempt to connect using the DEFAULT data
>>> source which for most people does not exist and hence fails. When
>>> SQLDriverConnect fails DBD::ODBC will have a second attempt with the
>>> older ODBC API SQLConnect.
>>>
>>> I believe this is historical in that initially:
>>>
>>> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>>>
>>> (before ODBC 3) used to call SQLConnect and works fine so long as the
>>> DSN mydsn_name exists but SQLConnect is less flexible than the newer
>>> SQLDriverConnect which supports new ODBC attributes and driver-specific
>>> attributes which are required for say DSN-less connections.
>>>
>>>> the sample code as following:my ($dbh, $sth);my $dsn =
>>>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>>>> $wkno;use Time::gmtime;my
>>>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>>>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>>>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
>>>> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
>>>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
>>>> "Database connection not made: $DBI::errstr"; $sth =
>>>> $dbh->prepare($sql); $sth->execute();It failed at the last line shown
>>>> above. I highly appreciate your help!Samuel
>>>
>>> Martin
>>> --
>>> Martin J. Evans
>>> Easysoft Limited
>>> http://www.easysoft.com
>>>
>>> __________ NOD32 2463 (20070815) Information __________
>>>
>>> This message was checked by NOD32 antivirus system.
>>> http://www.nod32.com.hk
>>>
>>>
>>
>>
>
> __________ NOD32 2463 (20070815) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Re: DBI DBD-ODBC

Thanks Ron. Problem fixed, I was able to run the script after I deleted the
DNS server I tried to setup. (silly me)

----- Original Message -----
From: "Ron Savage"
To:
Sent: Wednesday, August 15, 2007 7:59 PM
Subject: Re: DBI DBD-ODBC


> Hi Sam
>
>> Thank you for your input. I was going to try what you told me, but it
>> gives
>> this to me now:
>> CGI Error
>> The specified CGI application misbehaved by not returning a complete set
>> of
>> HTTP headers.
>
> This is not a /new/ error. It just means your program ran long enough to
> output an error message to the web server /before/ it output the headers
> expected by the web server.
>
> Are you sure you created a system DSN and not a user DSN? After all, the
> web server, which is running the script, is probably run under control of
> a different user that you are logged in as.
> --
> Ron Savage
> ron@savage.net.au
> http://savage.net.au/
>
> __________ NOD32 2464 (20070815) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Re: DBI DBD-ODBC

Martin,

I did as you suggested and it ran! But taking out the where clause makes the
result not meaningful.
I use the 'like' word is to include the records begin with '88090'. Some
records may have some Chinese characters after '88090'. Is there another way
of doing it? Thanks.

Samuel

----- Original Message -----
From: "Martin Evans"
To: "dbi-users"
Sent: Wednesday, August 15, 2007 10:59 AM
Subject: Re: DBI DBD-ODBC


> Samuel_Zheng wrote:
>> Thank you Martin for your response. "囀湔祛堤" is not readable, no one
>> knows what it means. refno is a string type and $ord_num is also string
>> type contains number characters. e.g. "88090"
>> I just don't understand the same codes run on XP is ok but not on Windows
>> 2003 server. the only thing I can think of is the permision issue. BUT
>> proper rights are given to the a/c runs the script.
>>
>> Samuel
>
> So try and narrow it down. Does it happen on all values of refno or just
> one particular one? Does it still happen if you remove the where clause
> from your select statement altogether. If it is none of that start taking
> columns out the select - start with duedate.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>> ----- Original Message ----- From: "Martin Evans"
>>
>> To: "dbi-users"
>> Sent: Wednesday, August 15, 2007 8:26 AM
>> Subject: Re: DBI DBD-ODBC
>>
>>
>>> Samuel_Zheng wrote:
>>>> I am having almost the same error on a windows 2003 server.
>>>
>>> I don't see this as the same. Robert is having a connection problem and
>>> you are having an execute problem.
>>>
>>>> Same set of scripts run fine on a Windows XP,
>>>> the following is the error:
>>>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
>>>> 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
>>>> c:\web\factory.pl line 61.
>>>
>>> As I said when you posted this before - What does "囀湔祛堤" mean in
>>> English? Error 22018 in access, is "Invalid character value for cast
>>> specification" which is described for SQLExecute as:
>>>
>>> "StatementText contained a C type that was an exact or approximate
>>> numeric, a datetime, or an interval data type; the SQL type of the
>>> column was a character data type; and the value in the column was not a
>>> valid literal of the bound C type."
>>>
>>> See
>>> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>>>
>>> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in your
>>> code. What type is column refno, what is in $ord_num? What does an ODBC
>>> trace contain?
>>>
>>>
>>>> it failed at the line to execute after prepared the sql.I wish someone
>>>> will shed some light on this. Thanks.I am using the format of
>>>> DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?
>>>
>>> The difference is that DBD::ODBC calls the newer ODBC connection API
>>> SQLDriverConnect first then if this fails it attempts to use the older
>>> ODBC API SQLConnect. If you are using a DSN then omitting DSN= will
>>> cause SQLDriverConnect to attempt to connect using the DEFAULT data
>>> source which for most people does not exist and hence fails. When
>>> SQLDriverConnect fails DBD::ODBC will have a second attempt with the
>>> older ODBC API SQLConnect.
>>>
>>> I believe this is historical in that initially:
>>>
>>> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>>>
>>> (before ODBC 3) used to call SQLConnect and works fine so long as the
>>> DSN mydsn_name exists but SQLConnect is less flexible than the newer
>>> SQLDriverConnect which supports new ODBC attributes and driver-specific
>>> attributes which are required for say DSN-less connections.
>>>
>>>> the sample code as following:my ($dbh, $sth);my $dsn =
>>>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>>>> $wkno;use Time::gmtime;my
>>>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>>>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>>>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
>>>> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
>>>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
>>>> "Database connection not made: $DBI::errstr"; $sth =
>>>> $dbh->prepare($sql); $sth->execute();It failed at the last line shown
>>>> above. I highly appreciate your help!Samuel
>>>
>>> Martin
>>> --
>>> Martin J. Evans
>>> Easysoft Limited
>>> http://www.easysoft.com
>>>
>>> __________ NOD32 2463 (20070815) Information __________
>>>
>>> This message was checked by NOD32 antivirus system.
>>> http://www.nod32.com.hk
>>>
>>>
>>
>>
>
> __________ NOD32 2463 (20070815) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com.hk
>
>

Re: DBI DBD-ODBC

Samuel_Zheng wrote:
> Martin,
>
> I did as you suggested and it ran! But taking out the where clause makes
> the result not meaningful.
> I use the 'like' word is to include the records begin with '88090'. Some
> records may have some Chinese characters after '88090'. Is there another
> way of doing it? Thanks.

I realised why you were using the like clause but by removing it and
seeing the problem go away we now know the problem is with the argument
to the where clause. I still believe you are putting something in
$ord_num which the driver does not like the fact you are using '%' when
if you are using MS Access it should be '*'.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> Samuel
>
> ----- Original Message ----- From: "Martin Evans"
>
> To: "dbi-users"
> Sent: Wednesday, August 15, 2007 10:59 AM
> Subject: Re: DBI DBD-ODBC
>
>
>> Samuel_Zheng wrote:
>>> Thank you Martin for your response. "囀湔祛堤" is not readable, no
>>> one knows what it means. refno is a string type and $ord_num is also
>>> string type contains number characters. e.g. "88090"
>>> I just don't understand the same codes run on XP is ok but not on
>>> Windows 2003 server. the only thing I can think of is the permision
>>> issue. BUT proper rights are given to the a/c runs the script.
>>>
>>> Samuel
>>
>> So try and narrow it down. Does it happen on all values of refno or
>> just one particular one? Does it still happen if you remove the where
>> clause from your select statement altogether. If it is none of that
>> start taking columns out the select - start with duedate.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>>> ----- Original Message ----- From: "Martin Evans"
>>>
>>> To: "dbi-users"
>>> Sent: Wednesday, August 15, 2007 8:26 AM
>>> Subject: Re: DBI DBD-ODBC
>>>
>>>
>>>> Samuel_Zheng wrote:
>>>>> I am having almost the same error on a windows 2003 server.
>>>>
>>>> I don't see this as the same. Robert is having a connection problem
>>>> and you are having an execute problem.
>>>>
>>>>> Same set of scripts run fine on a Windows XP,
>>>>> the following is the error:
>>>>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
>>>>> Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at
>>>>> c:\web\factory.pl line 61.
>>>>
>>>> As I said when you posted this before - What does "囀湔祛堤" mean in
>>>> English? Error 22018 in access, is "Invalid character value for cast
>>>> specification" which is described for SQLExecute as:
>>>>
>>>> "StatementText contained a C type that was an exact or approximate
>>>> numeric, a datetime, or an interval data type; the SQL type of the
>>>> column was a character data type; and the value in the column was
>>>> not a valid literal of the bound C type."
>>>>
>>>> See
>>>> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>>>>
>>>>
>>>> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in
>>>> your code. What type is column refno, what is in $ord_num? What does
>>>> an ODBC trace contain?
>>>>
>>>>
>>>>> it failed at the line to execute after prepared the sql.I wish
>>>>> someone will shed some light on this. Thanks.I am using the format
>>>>> of DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?
>>>>
>>>> The difference is that DBD::ODBC calls the newer ODBC connection API
>>>> SQLDriverConnect first then if this fails it attempts to use the
>>>> older ODBC API SQLConnect. If you are using a DSN then omitting DSN=
>>>> will cause SQLDriverConnect to attempt to connect using the DEFAULT
>>>> data source which for most people does not exist and hence fails.
>>>> When SQLDriverConnect fails DBD::ODBC will have a second attempt
>>>> with the older ODBC API SQLConnect.
>>>>
>>>> I believe this is historical in that initially:
>>>>
>>>> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>>>>
>>>> (before ODBC 3) used to call SQLConnect and works fine so long as
>>>> the DSN mydsn_name exists but SQLConnect is less flexible than the
>>>> newer SQLDriverConnect which supports new ODBC attributes and
>>>> driver-specific
>>>> attributes which are required for say DSN-less connections.
>>>>
>>>>> the sample code as following:my ($dbh, $sth);my $dsn =
>>>>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>>>>> $wkno;use Time::gmtime;my
>>>>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>>>>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>>>>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
>>>>> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
>>>>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
>>>>> "Database connection not made: $DBI::errstr"; $sth =
>>>>> $dbh->prepare($sql); $sth->execute();It failed at the last line
>>>>> shown above. I highly appreciate your help!Samuel
>>>>
>>>> Martin
>>>> --
>>>> Martin J. Evans
>>>> Easysoft Limited
>>>> http://www.easysoft.com
>>>>
>>>> __________ NOD32 2463 (20070815) Information __________
>>>>
>>>> This message was checked by NOD32 antivirus system.
>>>> http://www.nod32.com.hk
>>>>
>>>>
>>>
>>>
>>
>> __________ NOD32 2463 (20070815) Information __________
>>
>> This message was checked by NOD32 antivirus system.
>> http://www.nod32.com.hk
>>
>>
>
>

Re: DBI DBD-ODBC

Samuel_Zheng wrote:
> Thank you Martin. Do you mean I should be using '*' for MSAccess file?

Yes.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> ----- Original Message ----- From: "Martin Evans"
>
> To: "dbi-users"
> Sent: Monday, August 20, 2007 4:43 AM
> Subject: Re: DBI DBD-ODBC
>
>
>> Samuel_Zheng wrote:
>>> Martin,
>>>
>>> I did as you suggested and it ran! But taking out the where clause
>>> makes the result not meaningful.
>>> I use the 'like' word is to include the records begin with '88090'.
>>> Some records may have some Chinese characters after '88090'. Is there
>>> another way of doing it? Thanks.
>>
>> I realised why you were using the like clause but by removing it and
>> seeing the problem go away we now know the problem is with the
>> argument to the where clause. I still believe you are putting
>> something in $ord_num which the driver does not like the fact you are
>> using '%' when if you are using MS Access it should be '*'.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>>> Samuel
>>>
>>> ----- Original Message ----- From: "Martin Evans"
>>>
>>> To: "dbi-users"
>>> Sent: Wednesday, August 15, 2007 10:59 AM
>>> Subject: Re: DBI DBD-ODBC
>>>
>>>
>>>> Samuel_Zheng wrote:
>>>>> Thank you Martin for your response. "囀湔祛堤" is not readable, no
>>>>> one knows what it means. refno is a string type and $ord_num is
>>>>> also string type contains number characters. e.g. "88090"
>>>>> I just don't understand the same codes run on XP is ok but not on
>>>>> Windows 2003 server. the only thing I can think of is the permision
>>>>> issue. BUT proper rights are given to the a/c runs the script.
>>>>>
>>>>> Samuel
>>>>
>>>> So try and narrow it down. Does it happen on all values of refno or
>>>> just one particular one? Does it still happen if you remove the
>>>> where clause from your select statement altogether. If it is none of
>>>> that start taking columns out the select - start with duedate.
>>>>
>>>> Martin
>>>> --
>>>> Martin J. Evans
>>>> Easysoft Limited
>>>> http://www.easysoft.com
>>>>
>>>>> ----- Original Message ----- From: "Martin Evans"
>>>>>
>>>>> To: "dbi-users"
>>>>> Sent: Wednesday, August 15, 2007 8:26 AM
>>>>> Subject: Re: DBI DBD-ODBC
>>>>>
>>>>>
>>>>>> Samuel_Zheng wrote:
>>>>>>> I am having almost the same error on a windows 2003 server.
>>>>>>
>>>>>> I don't see this as the same. Robert is having a connection
>>>>>> problem and you are having an execute problem.
>>>>>>
>>>>>>> Same set of scripts run fine on a Windows XP,
>>>>>>> the following is the error:
>>>>>>> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
>>>>>>> Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1)
>>>>>>> at c:\web\factory.pl line 61.
>>>>>>
>>>>>> As I said when you posted this before - What does "囀湔祛堤" mean
>>>>>> in English? Error 22018 in access, is "Invalid character value for
>>>>>> cast specification" which is described for SQLExecute as:
>>>>>>
>>>>>> "StatementText contained a C type that was an exact or approximate
>>>>>> numeric, a datetime, or an interval data type; the SQL type of the
>>>>>> column was a character data type; and the value in the column was
>>>>>> not a valid literal of the bound C type."
>>>>>>
>>>>>> See
>>>>>> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_s tatus_return_codes.html#22018
>>>>>>
>>>>>>
>>>>>> I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in
>>>>>> your code. What type is column refno, what is in $ord_num? What
>>>>>> does an ODBC trace contain?
>>>>>>
>>>>>>
>>>>>>> it failed at the line to execute after prepared the sql.I wish
>>>>>>> someone will shed some light on this. Thanks.I am using the
>>>>>>> format of DBI->connect('dbi:ODBC:mydsn');I think. What is the
>>>>>>> difference?
>>>>>>
>>>>>> The difference is that DBD::ODBC calls the newer ODBC connection
>>>>>> API SQLDriverConnect first then if this fails it attempts to use
>>>>>> the older ODBC API SQLConnect. If you are using a DSN then
>>>>>> omitting DSN= will cause SQLDriverConnect to attempt to connect
>>>>>> using the DEFAULT data source which for most people does not exist
>>>>>> and hence fails. When SQLDriverConnect fails DBD::ODBC will have a
>>>>>> second attempt with the older ODBC API SQLConnect.
>>>>>>
>>>>>> I believe this is historical in that initially:
>>>>>>
>>>>>> DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')
>>>>>>
>>>>>> (before ODBC 3) used to call SQLConnect and works fine so long as
>>>>>> the DSN mydsn_name exists but SQLConnect is less flexible than the
>>>>>> newer SQLDriverConnect which supports new ODBC attributes and
>>>>>> driver-specific
>>>>>> attributes which are required for say DSN-less connections.
>>>>>>
>>>>>>> the sample code as following:my ($dbh, $sth);my $dsn =
>>>>>>> "dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my
>>>>>>> $wkno;use Time::gmtime;my
>>>>>>> $ltime=localtime(time);open(LOG,">>chkorder.log");print LOG
>>>>>>> "$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AG ENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Selec t
>>>>>>> refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE
>>>>>>> wkmaster.refno like '$ord_num%'"; print $starter; $dbh =
>>>>>>> DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
>>>>>>> "Database connection not made: $DBI::errstr"; $sth =
>>>>>>> $dbh->prepare($sql); $sth->execute();It failed at the last line
>>>>>>> shown above. I highly appreciate your help!Samuel
>>>>>>
>>>>>> Martin
>>>>>> --
>>>>>> Martin J. Evans
>>>>>> Easysoft Limited
>>>>>> http://www.easysoft.com
>>>>>>
>>>>>> __________ NOD32 2463 (20070815) Information __________
>>>>>>
>>>>>> This message was checked by NOD32 antivirus system.
>>>>>> http://www.nod32.com.hk
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>> __________ NOD32 2463 (20070815) Information __________
>>>>
>>>> This message was checked by NOD32 antivirus system.
>>>> http://www.nod32.com.hk
>>>>
>>>>
>>>
>>>
>>
>> __________ NOD32 2470 (20070819) Information __________
>>
>> This message was checked by NOD32 antivirus system.
>> http://www.nod32.com.hk
>>
>>
>
>