Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    please help me with writing event in mysql

    I worte some query in my database that all of them run with out any problem. but when I put them in an event that do them regularly, I face with this error. I tried this event several times and I understood that from where I put "insert into" code it doesnt work. I cant understand what is the problem. please guide me.
    error in mysql:
    #1048 - Column 'userid' cannot be null
    query:
    select @year:=extract(year from CURDATE());
    select @month:=extract(month from CURDATE());
    select @day:=extract(day from CURDATE());
    select @id:=thanksid2, @uid:=userid, @sname:=sendername,@rname:=recivername,@remail:=reciveremail,@text:=text,@attach:=attach,@score:=sco re,@cost:=cost,@syear:=sendyear,@smonth:=sendmonth,@sday:=sendday,@subj:=subject,@tempid:=tempid,@dr aftid:=draftid from thanksmsg2 where sendyear=@year and sendmonth=@month and sendday=@day;
    insert into thanksmsg1(thanksid1,userid,sendername,recivername,reciveremail,text,attach,score,cost,sendyear,send month,sendday,subject,tempid,draftid)values(@id,@uid,@sname,@rname,@remail,@text,@attach,@score,@cos t,@syear,@smonth,@sday,@subj,@tempid,@draftid);
    delete from thanksmsg2 where thanksid2=@id;
    event:
    CREATE EVENT madi ON SCHEDULE EVERY 1 DAY DO
    select @year:=extract(year from CURDATE());
    select @month:=extract(month from CURDATE());
    select @day:=extract(day from CURDATE());
    select @id:=thanksid2, @uid:=userid, @sname:=sendername,@rname:=recivername,@remail:=reciveremail,@text:=text,@attach:=attach,@score:=sco re,@cost:=cost,@syear:=sendyear,@smonth:=sendmonth,@sday:=sendday,@subj:=subject,@tempid:=tempid,@dr aftid:=draftid from thanksmsg2 where sendyear=@year and sendmonth=@month and sendday=@day;
    insert into thanksmsg1(thanksid1,userid,sendername,recivername,reciveremail,text,attach,score,cost,sendyear,send month,sendday,subject,tempid,draftid)values(@id,@uid,@sname,@rname,@remail,@text,@attach,@score,@cos t,@syear,@smonth,@sday,@subj,@tempid,@draftid);
    delete from thanksmsg2 where thanksid2=@id;

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,178
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Why are you doing a select followed by an insert? Why not write it as a single statement?
    Code:
    INSERT INTO  thanksmsg1( thanksid1, userid, sendername, recivername,reciveremail,
                             text, attach, score, cost, sendyear, sendmonth, sendday, subject,
                             tempid, draftid )
    SELECT thanksid2, userid, sendername, recivername, reciveremail,
           text, attach, score, cost, sendyear, sendmonth, sendday, subject,
           tempid, draftid 
    FROM thanksmsg2 
    WHERE sendyear=YEAR(CURDATE()) 
    AND sendmonth=MONTH(CURDATE())
    AND sendday=DAY(CURDATE())
    AND userid IS NOT NULL;
    
    DELETE FROM thanksmsg2 
    WHERE sendyear=YEAR(CURDATE()) 
    AND sendmonth=MONTH(CURDATE())
    AND sendday=DAY(CURDATE());
    I tacked on the userid IS NOT NULL on the assumption that indeed a NULL userid could be causing your problem. But it could also be that, in your original code, your SELECT was returning NO RECORDS AT ALL. But then you went ahead and did the INSERT anyway and...kablooey. If you use the above code, that would never happen. If there are no matching records from the SELECT, then nothing gets INSERTed, and there is no error. And then the DELETE will delete no records, but that's also not an error.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    madi (12-11-2011)

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,
    I have another problem with this code:
    I pasted your suggested code in mysql, they ran with out any problem but when I add "create event ..." , DELETE codes ,delete all of the records that condition is true for them before inseting them in next table and also another problem is that EVENT doesnt work regulary.
    thanks for your help

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,178
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    We can't help with code we can't see.

    I have no idea what "CREATE EVENT..." does in your code.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    madi (12-22-2011)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •