Hello,

One of our tables awb_msg has the field name 'text' and when I try to
use the DBI to do a select it prompts an error. How can I do this.

I can't change the field name as it is in a production environment and
has been use for the past 10 years with no problems. We are using ms-
sqlserver. And also, I have no control of changing the field name. So,
changing the field name is not possible.

Here is my test:

This is working correctly.

--
my $sql = "SELECT seq, create_date, msg_code, send_receive FROM
awb_msg where seq = 7989176";

it returns:

 Number of Fields: 4
7989176, 2009-03-02 01:20:45.430, FHL, R

---

But, once I add the 'text' field in the SELECT Query, Perl DBI stops
with an error:

---
my $sql = "SELECT seq, create_date, msg_code, send_receive, text FROM
awb_msg where seq = 7989176";

DBD::ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server
Driver]Strin
g data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long
truncated DBI
attribute LongTruncOk not set and/or LongReadLen too small) err=-1)
at ./dbi.pl
line 34.
DBI::db=HASH(0x10055fb8)->disconnect invalidates 1 active statement
handle (eith
er destroy statement handles or call finish on them before
disconnecting) at ./d
bi.pl line 45.

---

I have tried various ways and all returns an error:

awb_msg.text
awb_msg."text"
"awb_msg"."text"
etc.

Please guide me how I can do this.

Surely=20just=20giving=20the=20field=20an=20alias=20would=20work.=20=20=20=
=20
my=20$sql=20=3D=20"SELECT=20seq,=20create_date,=20msg_code,=20send_receive=
,=20text=20my_text=20FROM=20awb_msg=20where=20seq=20=3D=207989176";

-----Original=20Message-----
From:=20Moderator=20[mailto:fiazidris@gmail.com]=20
Sent:=2003=20March=202009=2004:04
To:=20dbi-users@perl.org
Subject:=20SQL=20Keyword=20is=20used=20as=20Field=20Name.=20SELECT=20Query=
=20Problem=20using=20DBI.

Hello,

One=20of=20our=20tables=20awb_msg=20has=20the=20field=20name=20'text'=20an=
d=20when=20I=20try=20to=20use=20the=20DBI=20to=20do=20a=20select=20it=20pr=
ompts=20an=20error.=20How=20can=20I=20do=20this.

I=20can't=20change=20the=20field=20name=20as=20it=20is=20in=20a=20producti=
on=20environment=20and=20has=20been=20use=20for=20the=20past=2010=20years=20=
with=20no=20problems.=20We=20are=20using=20ms-=20sqlserver.=20And=20also,=20=
I=20have=20no=20control=20of=20changing=20the=20field=20name.=20So,=20chan=
ging=20the=20field=20name=20is=20not=20possible.

Here=20is=20my=20test:

This=20is=20working=20correctly.

--
my=20$sql=20=3D=20"SELECT=20seq,=20create_date,=20msg_code,=20send_receive=
=20FROM=20awb_msg=20where=20seq=20=3D=207989176";

it=20returns:

=20Number=20of=20Fields:=204
7989176,=202009-03-02=2001:20:45.430,=20FHL,=20R

---

But,=20once=20I=20add=20the=20'text'=20field=20in=20the=20SELECT=20Query,=20=
Perl=20DBI=20stops=20with=20an=20error:

---
my=20$sql=20=3D=20"SELECT=20seq,=20create_date,=20msg_code,=20send_receive=
,=20text=20FROM=20awb_msg=20where=20seq=20=3D=207989176";

DBD::ODBC::st=20fetchrow_arrayref=20failed:=20[Microsoft][ODBC=20SQL=20Ser=
ver=20Driver]Strin=20g=20data,=20right=20truncation=20(SQL-01004)(DBD:=20s=
t_fetch/SQLFetch=20(long=20truncated=20DBI=20attribute=20LongTruncOk=20not=
=20set=20and/or=20LongReadLen=20too=20small)=20err=3D-1)=20at=20./dbi.pl=20=
line=2034.
DBI::db=3DHASH(0x10055fb8)->disconnect=20invalidates=201=20active=20statem=
ent=20handle=20(eith=20er=20destroy=20statement=20handles=20or=20call=20fi=
nish=20on=20them=20before
disconnecting)=20at=20./d
bi.pl=20line=2045.

---

I=20have=20tried=20various=20ways=20and=20all=20returns=20an=20error:

awb_msg.text
awb_msg."text"
"awb_msg"."text"
etc.

Please=20guide=20me=20how=20I=20can=20do=20this.


..

This=20message=20is=20intended=20only=20for=20the=20use=20of=20the=20perso=
n(s)=20to=20whom=20it=20is=20addressed.=20It=20may=20contain=20information=
=20which=20is=20privileged=20and=20confidential.=20Accordingly=20any=20una=
uthorised=20use=20is=20strictly=20prohibited.=20If=20you=20are=20not=20the=
=20intended=20recipient,=20please=20contact=20the=20sender=20as=20soon=20a=
s=20possible.

It=20is=20not=20intended=20as=20an=20offer=20or=20solicitation=20for=20the=
=20purchase=20or=20sale=20of=20any=20financial=20instrument=20or=20as=20an=
=20official=20confirmation=20of=20any=20transaction,=20unless=20specifical=
ly=20agreed=20otherwise.=20All=20market=20prices,=20data=20and=20other=20i=
nformation=20are=20not=20warranted=20as=20to=20completeness=20or=20accurac=
y=20and=20are=20subject=20to=20change=20without=20notice.=20Any=20opinions=
=20or=20advice=20contained=20in=20this=20Internet=20email=20are=20subject=20=
to=20the=20terms=20and=20conditions=20expressed=20in=20any=20applicable=20=
governing=20Marble=20Bar=20Asset=20Management=20LLP's=20=20terms=20and=20c=
onditions=20of=20business=20or=20client=20agreement=20letter.=20Any=20comm=
ents=20or=20statements=20made=20herein=20do=20not=20necessarily=20reflect=20=
those=20of=20Marble=20Bar=20Asset=20Management=20LLP.

Marble=20Bar=20Asset=20Management=20LLP=20is=20regulated=20and=20authorise=
d=20by=20the=20FSA.

--001636417f81eae06f0464328cb9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Thanks for your mail.

Now, I understand that it was not due to the field name which caused the
problem. It was due to the returned length of the awb_msg.text field was
just too big.. (too many characters in the order of about 800,000 bytes).

I read this article
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html#long_data

and fixed it with these 2 lines in the program:

$dbh->{'LongTruncOk'} = 1; #use 1 if it is ok to trunc data
$dbh->{'LongReadLen'} = 10000000; # i have seen upto 800,000 bytes already.

Thank you for your support.

In fact, even without the alias it works too.


2009/3/3 Neil Beddoe <Neil.Beddoe@mbamfunds.com>

> Surely just giving the field an alias would work.
> my $sql = "SELECT seq, create_date, msg_code, send_receive, text my_text
> FROM awb_msg where seq = 7989176";
>
> -----Original Message-----
> From: Moderator [mailto:fiazidris@gmail.com]
> Sent: 03 March 2009 04:04
> To: dbi-users@perl.org
> Subject: SQL Keyword is used as Field Name. SELECT Query Problem using DBI.
>
> Hello,
>
> One of our tables awb_msg has the field name 'text' and when I try to use
> the DBI to do a select it prompts an error. How can I do this.
>
> I can't change the field name as it is in a production environment and has
> been use for the past 10 years with no problems. We are using ms- sqlserver.
> And also, I have no control of changing the field name. So, changing the
> field name is not possible.
>
> Here is my test:
>
> This is working correctly.
>
> --
> my $sql = "SELECT seq, create_date, msg_code, send_receive FROM awb_msg
> where seq = 7989176";
>
> it returns:
>
>  Number of Fields: 4
> 7989176, 2009-03-02 01:20:45.430, FHL, R
>
> ---
>
> But, once I add the 'text' field in the SELECT Query, Perl DBI stops with
> an error:
>
> ---
> my $sql = "SELECT seq, create_date, msg_code, send_receive, text FROM
> awb_msg where seq = 7989176";
>
> DBD::ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server
> Driver]Strin g data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch
> (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too
> small) err=-1) at ./dbi.pl line 34.
> DBI::db=HASH(0x10055fb8)->disconnect invalidates 1 active statement handle
> (eith er destroy statement handles or call finish on them before
> disconnecting) at ./d
> bi.pl line 45.
>
> ---
>
> I have tried various ways and all returns an error:
>
> awb_msg.text
> awb_msg."text"
> "awb_msg"."text"
> etc.
>
> Please guide me how I can do this.
>
>
> .
>
> This message is intended only for the use of the person(s) to whom it is
> addressed. It may contain information which is privileged and confidential.
> Accordingly any unauthorised use is strictly prohibited. If you are not the
> intended recipient, please contact the sender as soon as possible.
>
> 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,
> unless specifically agreed otherwise. All market prices, data and other
> information are not warranted as to completeness or accuracy and are subject
> to change without notice. Any opinions or advice contained in this Internet
> email are subject to the terms and conditions expressed in any applicable
> governing Marble Bar Asset Management LLP's  terms and conditions of
> business or client agreement letter. Any comments or statements made herein
> do not necessarily reflect those of Marble Bar Asset Management LLP.
>
> Marble Bar Asset Management LLP is regulated and authorised by the FSA.
>

--001636417f81eae06f0464328cb9--

Did you actually READ the error message?

On 03.03.2009 05:04, Moderator wrote:
> String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long
> truncated DBI
> attribute LongTruncOk not set and/or LongReadLen too small) err=-1)
> at ./dbi.pl
> line 34.
>
>   
Alexander

-- 
Alexander Foken
mailto:alexander@foken.de  http://www.foken.de/alexander/

When LongTruncOk is false, attempting to retrieve more than LongReadLen byt=
es from a column will throw that error. You should set LongReadLen.

--
Blaine  http://datacentermoving.com

-----Original Message-----
From: Moderator [mailto:fiazidris@gmail.com]
Sent: Monday, March 02, 2009 9:04 PM
To: dbi-users@perl.org
Subject: SQL Keyword is used as Field Name. SELECT Query Problem using DBI.

Hello,

One of our tables awb_msg has the field name 'text' and when I try to use t=
he DBI to do a select it prompts an error. How can I do this.

I can't change the field name as it is in a production environment and has =
been use for the past 10 years with no problems. We are using ms- sqlserver=
.. And also, I have no control of changing the field name. So, changing the =
field name is not possible.

Here is my test:

This is working correctly.

--
my $sql =3D "SELECT seq, create_date, msg_code, send_receive FROM awb_msg w=
here seq =3D 7989176";

it returns:

 Number of Fields: 4
7989176, 2009-03-02 01:20:45.430, FHL, R

---

But, once I add the 'text' field in the SELECT Query, Perl DBI stops with a=
n error:

---
my $sql =3D "SELECT seq, create_date, msg_code, send_receive, text FROM awb=
_msg where seq =3D 7989176";

DBD::ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server Driver]=
Strin g data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long tru=
ncated DBI attribute LongTruncOk not set and/or LongReadLen too small) err=
=3D-1) at ./dbi.pl line 34.
DBI::db=3DHASH(0x10055fb8)->disconnect invalidates 1 active statement handl=
e (eith er destroy statement handles or call finish on them before
disconnecting) at ./d
bi.pl line 45.

---

I have tried various ways and all returns an error:

awb_msg.text
awb_msg."text"
"awb_msg"."text"
etc.

Please guide me how I can do this.

--xgyAXRrhYN0wYx8y
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2009-03-02 20:04:02 -0800, Moderator wrote:
                              ^^^^^^^^^
			      first off, I don't think choosing the nick
name "Moderator" is an especially bright move. Some might think you are
claiming to be the moderator of the list.

> One of our tables awb_msg has the field name 'text' and when I try to
> use the DBI to do a select it prompts an error. How can I do this.
>=20
> I can't change the field name as it is in a production environment and

The error below doesn't look like is has anything to do with the field
name.


> DBD::ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server
> Driver]Strin
> g data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long
> truncated DBI
> attribute LongTruncOk not set and/or LongReadLen too small) err=3D-1)
> at ./dbi.pl
> line 34.

It says that it must truncate a string (presumably the contents of
"text"), but isn't allowed to (LongTruncOk not set). So the problem
almost certainly not the name "text" but the size of the data.

Read perldoc DBI for information about LongTruncOk and LongReadLen.

> Please guide me how I can do this.

The first step is to actually read the error message.

	hp

--=20
   _  | Peter J. Holzer    | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR       | eingepr=E4gt stehen: "Ein ewig R=E4tsel will i=
ch
| |   | hjp@wsr.ac.at      | bleiben, mir und andern."
__/   | http://www.hjp.at/ |    -- Wolfram Heinrich in desd

--xgyAXRrhYN0wYx8y
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iD8DBQFJrO+kMdFfQa64PCwRAiY/AJ4zos3c4pPvIx49eDz1DtzoSKA27gCfc+sH
cDZsFj920/o3sAAp/kqMQFg=
=0a+v
-----END PGP SIGNATURE-----

--xgyAXRrhYN0wYx8y--

--_dcb87ee1-a600-4660-a666-9f8bb042eeba_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


please display the contents of the dbi.pl perl script ..preferrably around =
line 34
we would need to see the allocation and initialisation for your variable Lo=
ngTruncOk=20
we would need to see the allocation and initialisation for your variable Lo=
ngReadLen

Martin=20
______________________________________________=20
Disclaimer and confidentiality note=20
Everything in this e-mail and any attachments relates to the official busin=
ess of Sender. This transmission is of a confidential nature and Sender doe=
s not endorse distribution to any party other than intended recipient. Send=
er does not necessarily endorse content contained within this transmission.=
=20




> Date: Tue=2C 3 Mar 2009 09:19:03 +0100
> From: alexander@foken.de
> To: fiazidris@gmail.com
> CC: dbi-users@perl.org
> Subject: Re: SQL Keyword is used as Field Name. SELECT Query Problem usin=
g DBI.
>=20
> Did you actually READ the error message?
>=20
> On 03.03.2009 05:04=2C Moderator wrote:
> > String data=2C right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (lon=
g
> > truncated DBI
> > attribute LongTruncOk not set and/or LongReadLen too small) err=3D-1)
> > at ./dbi.pl
> > line 34.
> >
> >  =20
> Alexander
>=20
> --=20
> Alexander Foken
> mailto:alexander@foken.de  http://www.foken.de/alexander/
>=20

_________________________________________________________________
Hotmail=AE is up to 70% faster. Now good news travels really fast.=20
http://windowslive.com/online/hotmail?ocid=3DTXT_TAGLM_WL_HM_70faster_03200=
9=

--_dcb87ee1-a600-4660-a666-9f8bb042eeba_--

Thank you all.

Now, I understand that it was not due to the field name which caused
the problem. It was due to the returned length of the awb_msg.text
field was just too big.. (too many characters in the order of about
800,000 bytes).

I read this article
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html#long_data

and fixed it with these 2 lines in the program:

$dbh->{'LongTruncOk'} = 1; #use 1 if it is ok to trunc data
$dbh->{'LongReadLen'} = 10000000; # i have seen upto 800,000 bytes
already.

Thank you for your support.

Previously those 2 lines were not in my code at all and I never knew
about this parameter before as I am totally new to this. And, to add
to the confusion, the field name was using the SQL keyword.

Now, the field name works nicely regardless of quotes, or anything.

awb_msg.text
text
awb_msg."text"

ALL works.

On Mar 3, 3:51=A0pm, h...@wsr.ac.at (Peter J. Holzer) wrote:
> On 2009-03-02 20:04:02 -0800, Moderator wrote:
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ^^^^^^^^^
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 first off, I =
don't think choosing the nick
> name "Moderator" is an especially bright move. Some might think you are
> claiming to be the moderator of the list.
>

I am confused with the nickname moderator myself.

I just happen to realise that I am a moderator in an other group and
that carried forward in here.

Sorry for that... I will remove it right away. Thank you for your
notice.