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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Aug 2008
    Location
    Massachusetts
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Error 022 in MySQL using Frontbase

    I am trying to insert a record in Frontbase using the SQL tab. Here is the command:
    INSERT INTO club.club_member_charge (charge_id, registration_id, charge_date, charge_amount, charge_successful, next_charge_date) VALUES (1000778, 1000660, 2008-08-19 16:30:00, -9.95, 'y', 2008-08-19 16:30:00);

    and it returns syntax error 022
    expecting ')'
    expecting ';'

    I know it has something to do with the date formating because I have successfully added records and edited other fields successfully, but cannot get the date field to either edit nor insert.

    Can someone please help me with the correct syntax for dates in Frontbase?

    I have tried it with ' ' but then I get an error saying value does not match column charge_date.

    If I use MySQL on my other database the insert works perfectly but there is something about the date formatting in Frontbase that won't allow this work.

    Any advice would be greatly appreciated.

    Amy

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You need quotes around your datetime strings.

  • #3
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    I think it's double quotes try this.

    Code:
    INSERT INTO club.club_member_charge (charge_id, registration_id, charge_date, charge_amount, charge_successful, next_charge_date) VALUES ("1000778", "1000660", "2008-08-19 16:30:00", "-9.95", 'y', "2008-08-19 16:30:00");

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Double quotes would only be a requirement of Frontbase if anything, because normal MySQL syntax handles single quotes fine.

  • #5
    New to the CF scene
    Join Date
    Aug 2008
    Location
    Massachusetts
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you all for your suggestions. I was able to find the answer from another source. But for future reference for other people here is what I had to do:

    insert into club_member_charge (registration_id,charge_date,charge_amount,charge_successful,next_charge_date) values(1000660,cast('2008-08-20 13-04-56' as timestamp),-9.95,'y',cast(current_timestamp as timestamp))

    because the date fields are formatted as timestamp the cast function needed to be used to insert dates. I did have to use the single quote around the date, but the big one was using cast('xxx' as timestamp).

    Thanks again. Hope this helps others who are using Frontbase.

    AE Chin


  •  

    Posting Permissions

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