Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re^6: identifier too long error

by grashoper (Monk)
on Nov 04, 2008 at 23:34 UTC ( [id://721531]=note: print w/replies, xml ) Need Help??


in reply to Re^5: identifier too long error
in thread identifier too long error

yeah I tried it on my laptop with version 5.10 of perl and no its not the version of dbi I am at 1.601 upgraded to 1.602 but no difference at all, here is my code + my error file. 
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, m
+y $yday, my $isdst)=localtime($t);
 $year = $year + "1900";
 my $SQLServer = "servername";
 my $DB = "dbname";
 my $SQLUname = "username";
 my $SQLpass="password";  
 my $field1="ClassSchedule.EventDate AS EventDate";
        my $field2="DATEDIFF(Day,{fn NOW()},ClassSchedule.EventDate) A
+S Until,";
 my $field3="DATEDIFF(Day,ClassRoster.Enrolled, ClassSchedule.EventDat
+e) AS Since,";
 my $field4="Classes.Duration AS Duration, Classes.Topic AS Topic, Cla
+sses.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";
 my $field5="ClassSchedule";
 my $field6="Classes ON ClassSchedule.id_Class = Classes.idClass";
 my $field7="ClassRoster ON ClassSchedule.idEvent = ClassRoster.id_Eve
+nt";
 my $field8="ClassLocation ON ClassSchedule.id_Location = ClassLocatio
+n.idLocation";
        my $field9condition="ClassSchedule.EventDate > { fn NOW() }) A
+ND 
              (ClassRoster.Cancelled IS NULL) AND 
              (ClassRoster.Reminded IS NULL) AND 
              (DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.EventD
+ate) > 7) AND 
              (NOT (DATEPART(dw, { fn NOW() }) IN (1, 7))) AND 
              (DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDate) < 7
+)";
 
 # Connect to the database
# See footnote 
 
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\n";
 
# Prepare the SQL query for execution
my $sth=$dbh->prepare("SELECT $field1,$field2,$field3,$field4 from $fi
+eld5 INNER JOIN $field6 INNER JOIN $field7 INNER JOIN $field8 WHERE $
+field9condition");
# Execute the query
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop
+ped";
while (my ($field1)=$sth->fetchrow_array())
{
print "Field 1: $field1 \n";
}
$sth->finish();
 

$dbh->disconnect();
 
    DBI 1.602-ithread default trace level set to 0x0/1 (pid 5692) at t
+est3.pl line 12
    -> DBI->connect(dbi:ODBC:driver=SQL Server;Server=VASVCSSQL;databa
+se=MLXhelp;uid=Frank;pwd=fu3go;, , ****)
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.010000 pid=5692 ru
+id=0 euid=0
       install_driver: DBD::ODBC version 1.15 loaded from C:/Perl/site
+/lib/DBD/ODBC.pm
    <- install_driver= DBI::dr=HASH(0x3376c8c)
    !! warn: 0 CLEARED by call to default_user method
    <- default_user(undef, undef, ...)= ( undef undef ) [2 items] at D
+BI.pm line 625
    <- connect('driver=SQL Server;Server=VASVCSSQL;database=MLXhelp;ui
+d=Frank;pwd=fu3go;', undef, ...)= DBI::db=HASH(0x33771ac) at DBI.pm l
+ine 637
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
    <- STORE('PrintError', 1)= 1 at DBI.pm line 689
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
    <- STORE('AutoCommit', 1)= 1 at DBI.pm line 689
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
    <- STORE('Username', undef)= 1 at DBI.pm line 692
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
    <> FETCH('Username')= undef ('Username' from cache) at DBI.pm line
+ 692
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
#removed connectstring for security
    <- connected('dbi:ODBC:driver=SQL;')= undef at DBI.pm line 698
    <- connect= DBI::db=HASH(0x33771ac)
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database context to 'MLXhelp'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_english. (SQL-01000)' (err#0)
    <- STORE('dbi_connect_closure', CODE(0x3376bdc))= 1 at DBI.pm line
+ 707
    !! info: '' CLEARED by call to prepare method
    <- prepare('SELECT ClassSchedule.EventDate AS EventDate,DATEDIFF(D
+ay,{fn NOW()},ClassSchedule.EventDate) AS Until,,DATEDIFF(Day,ClassRo
+ster.Enrolled, ClassSchedule.EventDate) 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, 
    Cl...')= DBI::st=HASH(0x33773dc) at test3.pl line 55
    !! ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1
+: Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 
+be prepared. (SQL-42000)' (err#2)
    <- execute= undef at test3.pl line 57
       ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1
+: Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 
+be prepared. (SQL-42000)' (err#2)
    <- DESTROY(DBI::st=HASH(33772ec))= undef at test3.pl line 57
       ERROR: 1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1
+: Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 
+be prepared. (SQL-42000)' (err#0)
    <- DESTROY(DBI::db=HASH(337711c))= undef at test3.pl line 57
    !! ERROR: 1 CLEARED by call to disconnect_all method
    <- disconnect_all= '' at DBI.pm line 715
!   <- DESTROY(DBI::dr=HASH(3376c8c))= undef during global destruction

[download]

Replies are listed 'Best First'.
Re^7: identifier too long error
by Krambambuli (Curate) on Nov 05, 2008 at 08:10 UTC
    This here is a different issue, as you've edited the query and now have an extra comma in it:

    [...],ClassSchedule.EventDate) AS Until,, [...]
                                      ^^^^

    [download]
    If it still doesn't work after correcting this, you might also try to increase the debug level; make it 2, then 3, 4, ...
    Might be that that would show more to get a clou about what's wrong.


    Krambambuli
    ---
[reply]
[d/l]
Re^7: identifier too long error
by mje (Curate) on Nov 05, 2008 at 22:37 UTC

    As Krambambuli says this is now a different issue. If you get back to the original issue and post a trace with a larger trace level I'm sure we can get to the bottom of this.

    Forgot to say, 42000 is Syntax error or access violation which suggests a SQL issue

[reply]
      I went back to the original script, found the original problem was that someone had removed a value which needed to be there, so I stuck a login/password back in and that fixed the issue, now all I need to do is correct a minor issue with a webform field, validation was not used to verify the input field so I have some bad invalid entries for email which of course die on the send email portion. here is the original script along with the field that was the problem..etc. 
      ####
#### Filename: reminder.pl
#### Version: .1.0
#### Written By: Matthew Lukaszewicz
#### Written Date: 03/22/06
#### Last Change Date: 12/19/06 by Francisco Oquendo
#### Comments: 03/22/06 This had to be completly re-written due to the
+ excahnge server change
#### Comments: 03/27/06 Fixed logging to include date/timestamp and se
+perate days with newlines
#### Comments: 10/28/08 Updated From Email address to generic training
+@marketlinx.com due to exchange changes,added ### myself for debuggin
+g purposes.
#### 12/19/06 Removed all references to Matt's IDs
####

#### Modules ####
    use OLE;
    use Mail::Sender;

#### 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="pass";

#### Get List of Users to Remind ####
    open(LOGFILE,">>Reminder.log");
    print LOGFILE "\n\n".localtime($t)."\n";
    
    my $Conn = CreateObject OLE "ADODB.Connection";
    $Conn->Open("PROVIDER=SQLOLEDB;DATA SOURCE=$SQLServer;UID=$SQLUnam
+e;PWD=$SQLpass;DATABASE=$DB");
    my $RS_UsersToRemind = CreateObject OLE "ADODB.Recordset";
    my $RS_UserUpdate = CreateObject OLE "ADODB.Recordset";
    
    if ($Conn)
    {
        my $SQL_UsersToRemind = "
            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)";
                    
        $RS_UsersToRemind->Open($SQL_UsersToRemind, $Conn);
        if (!defined href $RS_UsersToRemind){
         ref $RS_UsersToRemind={};
        print "this is defined ";
        }
        if ($RS_UsersToRemind)
        {
            while(!$RS_UsersToRemind->EOF())
            {
                my $SQL_UserUpdate = "update ClassRoster set Reminded=
+{ fn NOW() } where idEnrollee = '".$RS_UsersToRemind->Fields('idEnrol
+lee')->{Value}."';";
                my $sender = new Mail::Sender
                {
                    smtp => 'vaexch001.firstamericanmls.com', 
                    from => 'training@marketlinx.com'
                };
                (
                    ref ($sender->MailMsg( 
                            {
                        to => "".$RS_UsersToRemind->Fields('emailAddre
+ss')->{Value}."",
                                bcc => "somuser\@somewhere.com",
                                subject => "REMINDER: ".$RS_UsersToRem
+ind->Fields('Topic')->{Value}."",
                                msg => 
"This message is to remind you that you are registered to attend ".$RS
+_UsersToRemind->Fields('Topic')->{Value}.". 

Date/Time: ".$RS_UsersToRemind->Fields('EventDate')->{Value}."

Location: ".$RS_UsersToRemind->Fields('Location')->{Value}."

Address : ".$RS_UsersToRemind->Fields('Address')->{Value}."

Seating is limited, so if you no longer plan to attend the class, plea
+se cancel on www.MLXhelp.com",
                                auth => 'LOGIN',
                                authid => 'username',
                                authpwd => 'password',
    ##This field was missing which caused the problem!                
+        })
                        ) and print LOGFILE "Mail sent to ".$RS_UsersT
+oRemind->Fields('emailAddress')->{Value}." OK.\n" ) or die "$Mail::Se
+nder::Error\n";
                $Conn->Execute($SQL_UserUpdate);    
                print LOGFILE "Updated ".$RS_UsersToRemind->Fields('Sy
+stem')->{Value}.",".$RS_UsersToRemind->Fields('Account')->{Value}.","
+.$RS_UsersToRemind->Fields('PublicID')->{Value}.",".$RS_UsersToRemind
+->Fields('Name')->{Value}.",".$RS_UsersToRemind->Fields('emailAddress
+')->{Value}." for ".$RS_UsersToRemind->Fields('EventDate')->{Value}."
+,".$RS_UsersToRemind->Fields('Topic')->{Value}.",".$RS_UsersToRemind-
+>Fields('Location')->{Value}." OK.\n" or die "Unable to Update\n";
                $RS_UsersToRemind->MoveNext();
            }
        $RS_UsersToRemind->Close();
        }
    $Conn->Close;
    close(LOGFILE);
    }
    }

      [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://721531]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (8)
As of 2024-04-19 14:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found