Problem Description 1:

I'm working in PHP and using the QODBC Test Tool. I am trying to Insert value into the Customer table.

But I am getting the below error:

Expected lexical element not found:

My insert statement is:

Insert into Customer ('CompanyName', 'Phone', 'Email') values('adsf', '235632', 'afdsf');

Won't work for me. Any suggestions as to why?

Solution 1:

QODBC will issue this error when there is a syntax error in your SQL statements.

Your insert statement should be like this:

Insert into Customer ("CompanyName", "Phone", "Email") values('Test', '235632', 'abc@def.com');

Problem Description 2:

I have constructed an insert statement that is getting this error, in order to narrow it down, I modified the values portion of my query to be a select inserted and tested the different data types that I was trying to select.

After doing so, I was able to determine that the problem is with both the ts and d functions that we have here, and I had to assume that it was the inputs fault and not yours since the function is referenced everywhere. I seemed to remember reading somewhere that you guys do use the computer's regional time settings to figure out how to parse out times, but after my test, I found this not to be the case.

For example
select {ts'8/20/2006 9:26:15 AM'} from customer
and
select {d'9/15/2006'} from customer
both give the lexical element error.

My "Regional and Language settings" in "Control Panel" say my date-time format looks like so:

Time : 10:04:29 AM
Short Date: 8/10/2006
Long Date: Thursday, August 10, 2006

Which I was hoping that you guys were paying attention to, after all, that's what .NET does and it seems to work great.

select {d'2006-06-26'} from customer

This results in "6/26/2006 12:00:00 AM" which is expected, but having to format the time for the select/insert/update/delete statement, just so it can be formatted back seems like a lot of overhead...

For those of us using .Net (or any language that supports date/time formatting via a format string), you should be able to use the following:

string date= System.DateTime.Now.ToString("yyyy-MM-dd");
string datetime= System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt");

which will give you:
2006-08-10
and
2006-08-10 10:10:39 AM

Respectively, which can be used in your SQL Statement afterward.

Solutions 2:

QODBC will issue this error when there is a syntax error on your SQL statements.

So please check your statements & try again.

QODBC uses the standard SQL Date {d'YYYY-MM-DD'} and Timestamp {ts 'YYYY-MM-DD HH:mm:SS.zzz'} formats and is the same worldwide regardless of the region: See also: How are dates formatted in SQL queries when using the QuickBooks generated time stamps