Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

identifier too long error

by grashoper (Monk)
on Nov 04, 2008 at 00:20 UTC ( #721246=perlquestion: print w/ replies, xml ) Need Help??
grashoper has asked for the wisdom of the Perl Monks concerning the following question:

I am getting an error that identifier is too long and my statement cannot be prepared there are a couple joins and I am using datediff but I have no idea how to fix. I get sql-42000 and am using dbi dbd:st_execute/SQLExecute err=-1 here is my query for reference it does run on the db just fine.
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)

Comment on identifier too long error
Download Code
Re: identifier too long error
by thezip (Vicar) on Nov 04, 2008 at 00:35 UTC
    1. This is not a Perl question.
    2. If it were, you could use DBI->trace($level, $filename) to dump the trace info into a file for debugging purposes.

    What can be asserted without proof can be dismissed without proof. - Christopher Hitchens
      thezip does this mean its a sql problem?

        Pretty much.

        Somewhere near the top of your script, do this:

        my $trace_level = 1; my $trace_file = 'tracefile.txt'; DBI->trace($trace_level, $trace_file); # ... then the rest of your script
        You might want to delete the $trace_file prior to each run. This will make it easier to find where your problem is.

        What can be asserted without proof can be dismissed without proof. - Christopher Hitchens
Re: identifier too long error
by Krambambuli (Deacon) on Nov 04, 2008 at 08:22 UTC
    It would help to see not only the SQL statement, but also how exactly you embed it into Perl. I think there might be the real problem. If you print out the SQL statement Perl has right before calling prepare, does it look exactly as the one you showed ?


    Krambambuli
    ---
      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();
      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
        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
        ---
        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";

        can be replaced with:

        my $year = (localtime)[5] + 1900;

        Since you are only using the year, there is no need to declare all those extra variables.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://721246]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (15)
As of 2014-07-10 17:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (214 votes), past polls