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 <[email protected]> > 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:[email protected]] > Sent: 03 March 2009 04:04 > To: [email protected] > 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. >
