Re^2: identifier too long error

given your post I decided to try embedding into variable $query and then calling prepare, thanks to the thezip I also turned on logging for dbi and have a tracefile, here is what I have so far, now it appears to execute but I don't get any results to stdout..

###   Rewrite of reminder
###   added dbi & dbd::odbc modules for debugging
###   added data:dumper also for debugging
###   added tracelevel and trace.txt for debugging

use OLE;
use DBI;
use DBD::ODBC;
    use Mail::Sender;
my $trace_level=1;
my $trace_file='tracefile.txt';
DBI->trace($trace_level,$trace_file);

#### lnitialization of Global Variables & Arrays ####
    my $debug =0;
    my $t = time;
    (my $sec, my $min, my $hour, my $mday, my $mon, my $year, my $wday
+, my $yday, my $isdst)=localtime($t);
    $year = $year + "1900";
    my $SQLServer = "servername";
    my $DB = "dbname";
    my $SQLUname = "username";
    my $SQLpass="userpass";
       
    my $query="SELECT ClassSchedule.EventDate AS EventDate, 
                DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDate) AS
+ Until, 
                DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.Event
+Date) AS Since, 
                Classes.Duration AS Duration, 
                Classes.Topic AS Topic, 
                Classes.Description AS Description, 
                ClassLocation.Location AS Location, 
                ClassLocation.Address AS Address, 
                ClassLocation.Directions AS Directions, 
                ClassLocation.Map AS Map, 
                ClassRoster.idEnrollee AS idEnrollee,
                ClassRoster.System AS System,
                ClassRoster.Account AS Account,
                ClassRoster.PublicID AS PublicID,
                ClassRoster.Name AS Name,
                ClassRoster.Email AS emailAddress
            FROM ClassSchedule 
                INNER JOIN
                    Classes ON ClassSchedule.id_Class = Classes.idClas
+s 
                INNER JOIN
                    ClassRoster ON ClassSchedule.idEvent = ClassRoster
+.id_Event
                INNER JOIN
                    ClassLocation ON ClassSchedule.id_Location = Class
+Location.idLocation
                WHERE (ClassSchedule.EventDate > { fn NOW() }) AND 
                    (ClassRoster.Cancelled IS NULL) AND 
                    (ClassRoster.Reminded IS NULL) AND 
                    (DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.
+EventDate) > 7) AND 
                    (NOT (DATEPART(dw, { fn NOW() }) IN (1, 7))) AND 
                    (DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDat
+e) < 7)";

    # Connect to the database
# See footnote 1

my $dbh = DBI->connect("dbi:ODBC:driver=SQL Server;Server=VASVCSSQL;da
+tabase=MLXhelp;uid=$SQLUname;pwd=$SQLpass;") or die  "Couldn't open d
+atabase: $DBI::errstr; stopped";
print "connected finally";

# Prepare the SQL query for execution
my $sth=$dbh->prepare($query);
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop
+ped";

$sth->finish();


$dbh->disconnect();
[download]

finally tracefile.txt...

 DBI 1.48-ithread default trace level set to 0x0/1 (pid 5748)
    -> DBI->connect(dbi:ODBC:driver=SQL Server;Server=VASVCSSQL;databa
+se=MLXhelp;uid=Frank;pwd=fu3go;, , ****)
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.006001 pid=5748 ru
+id=0 euid=0
       install_driver: DBD::ODBC version 1.13 loaded from C:/Perl/site
+/lib/DBD/ODBC.pm
    <- install_driver= DBI::dr=HASH(0x2b1bf3c)
    <- default_user(undef undef ...)= ( undef undef ) [2 items] at DBI
+.pm line 580
    <- connect('driver=SQL Server;Server=servername;database=dbname;ui
+d=username;pwd=userpass;' undef ...)= DBI::db=HASH(0x2e8b33c) at DBI.
+pm line 598
    <- STORE('PrintError' 1)= 1 at DBI.pm line 645
    <- STORE('AutoCommit' 1)= 1 at DBI.pm line 645
    <- STORE('Username' undef)= 1 at DBI.pm line 648
    <> FETCH('Username')= undef ('Username' from cache) at DBI.pm line
+ 648
    <- connect= DBI::db=HASH(0x2e8b33c)
    <- STORE('dbi_connect_closure' CODE(0x2e8b294))= 1 at DBI.pm line 
+668
    <- prepare('SELECT ClassSchedule.EventDate AS EventDate, 
                DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDate) AS
+ Until, 
                DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.Event
+Date) AS Since, 
                Classes.Duration AS Duration, 
                Classes.Topic AS Topic, 
                Classes.Description AS Description, 
                ClassLocation.Location AS Location, 
                ClassLocation.Address AS Address, 
                ClassLocation.Dire...')= DBI::st=HASH(0x2e8b9d4) at te
+st2.pl line 61
dbd_describe failed, dbd_st_execute #2...!
    <- execute= -1 at test2.pl line 62
    <- finish= 1 at test2.pl line 64
    <- disconnect= 1 at test2.pl line 67
    <- DESTROY(DBI::st=HASH(02E8B9D4))= undef
    <- DESTROY(DBI::db=HASH(02E8B33C))= undef
    <- disconnect_all= '' at DBI.pm line 677
!   <- DESTROY(DBI::dr=HASH(02B1BF3C))= undef during global destructio
+n
[download]

Comment on Re^2: identifier too long error Select or Download Code

Replies are listed 'Best First'.
Re^3: identifier too long error by Krambambuli (Curate) on Nov 04, 2008 at 20:31 UTC
It might not be the problem, but it seems that both the DBI and the DBD::ODBC you use are a bit outdated. Current versions are DBI-1.607 and DBD::ODBC-1.17. Can you give them a try ? Krambambuli ---	[reply]
Re^4: identifier too long error by grashoper (Monk) on Nov 04, 2008 at 20:58 UTC
will they work with 5.6.1 build 630? this is production and some of our stuff will not work if I upgrade the perl being used, I know I already tried and had to go-back to previous version.	[reply]
Re^5: identifier too long error by Krambambuli (Curate) on Nov 04, 2008 at 21:26 UTC
I don't know... But you'll to have get a way out of this setup one way or another. (Hmmm... if it's PROD, why would you make development on that box anyway...?) You should be able to try if the problems vanish with some newer Perl and Perl modules even so, by using another development machine (you can have one at Amazon in no time) or by just installing the new modules into your home, even without upgrading Perl. I cannot say so, but what would you do if I could: 'Oh, it's a nasty bug that was patched in the latest DBI/DBD::ODBC' ? Krambambuli ---	[reply]
Re^6: identifier too long error by grashoper (Monk) on Nov 04, 2008 at 23:34 UTC
Re^7: identifier too long error by Krambambuli (Curate) on Nov 05, 2008 at 08:10 UTC
Re^7: identifier too long error by mje (Curate) on Nov 05, 2008 at 22:37 UTC
Some notes below your chosen depth have not been shown here
Re^5: identifier too long error by mje (Curate) on Nov 05, 2008 at 22:28 UTC
No. The latest DBI needs at least 5.8.1 (see DBI's Makefile.PL). The number of changes in DBI and DBD::ODBC since the versions you are using is huge.	[reply]
Re^3: identifier too long error by toolic (Bishop) on Nov 04, 2008 at 20:41 UTC
completely unrelated to your problem, but this: `my $t = time; (my $sec, my $min, my $hour, my $mday, my $mon, my $year, my $wday, my + $yday, my $isdst)=localtime($t); $year = $year + "1900";` [download] can be replaced with: `my $year = (localtime)[5] + 1900;` [download] Since you are only using the year, there is no need to declare all those extra variables.	[reply] [d/l] [select]


Your skill will accomplish what the force of many cannot
	PerlMonks