Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Insert Invoice Problem with dates |
Author |
Message |
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-17 21:07:44 |
Using QODBC with ALPHA5, I can succesfully insert invoice (header and lines) except when I include any Dates, so I can only assume my date format is incorrect.
Below are the error codes and variations I have tried.
Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has
returned the following error code and description to Alpha Five."#0d#0a#09"Consult your
database documentation for further information."#0d#0a#0d#0a"11030 - '[QODBC] Insert value
must be a simple value"#0d#0a"SQL State is:
42S00',NativeCode=11030.000000,NativeText="[QODBC] Insert value must be a simple
value"#0d#0a"SQL State is:
42S00,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road
","Newton Abbot ","Devon ","
","XXX 4SQ ","UK",0,"80000006-1197628269",2007-12-11,"
",0)
------------------------------------------------------------------------------------------
Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has
returned the following error code and description to Alpha Five."#0d#0a#09"Consult your
database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected
lexical element not found: <identifier>"#0d#0a"SQL State is:
42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:
<identifier>"#0d#0a"SQL State is:
42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road
","Newton Abbot ","Devon ","
","XXX 4SQ
","UK",0,"80000006-1197628269",{d'2007-12-11'}," ",0)
-------------------------------------------------------------------------------------------
Code=28.000000,Text="Error parsing SQL statement"#0d#0a#0d#0a#09"Your database has returned
the following error code and description to Alpha Five."#0d#0a#09"Consult your database
documentation for further information."#0d#0a#0d#0a"514 - 'line 1:513: unexpected char:
'{'',NativeCode=514.000000,NativeText="line 1:513: unexpected char:
'{',Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road
","Newton Abbot ","Devon ","
","XXX 4SQ
","UK",0,"80000006-1197628269",{'2007-12-11'}," ",0)
-------------------------------------------------------------------------------------------
Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has
returned the following error code and description to Alpha Five."#0d#0a#09"Consult your
database documentation for further information."#0d#0a#0d#0a"11024 - '[QODBC] Invalid
operand for operator: <assignment>"#0d#0a"SQL State is:
42S00',NativeCode=11024.000000,NativeText="[QODBC] Invalid operand for operator:
<assignment>"#0d#0a"SQL State is:
42S00,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road
","Newton Abbot ","Devon ","
","XXX 4SQ
","UK",0,"80000006-1197628269",'2007-12-11'," ",0)
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-17 21:40:04 |
QODBC is looking for:
INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES ('800000F3-1197629414', '80000055-1197631694', 'OP/1030616', 'Shaldon Road', 'Newton Abbot', 'Devon', ' ', 'XXX 4SQ', 'UK' ,0, '80000006-1197628269', {d'2007-12-11'} ,' ', 0)
but you can't do a insert into the Invoice table without doing cached InvoiceLine inserts first.
See: How can I create multiple lines in the Invoice, Purchase, Journal and other tables? for more. |
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-17 21:49:18 |
Thank you for your reply.
My invoice header insert statement is executed after the cached Invoice lines, and as I said if I dont include a date it works correctly.
Unless my eyes deceive me my attempt:-
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road
","Newton Abbot ","Devon ","
","XXX 4SQ
","UK",0,"80000006-1197628269",{d'2007-12-11'}," ",0)
Has the date in the format you have posted but results in "Expected lexical element not found: "
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-17 21:58:14 |
All the string values have single quotes, not double quotes. |
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-17 22:35:12 |
Here are the results after I changed the quotes. I have included the invoice line statement and result.
INSERT INTO InvoiceLine( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate,
InvoiceLineAmount, FQSaveToCache ) VALUES ("80000014-1197629891","900.326 ",
6, 322,1)
Code=0.000000,Text="Success,NativeCode=0.000000,NativeText=,Syntax=,API=,RowsAffected=1.000
000,Success=T,Error=F,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
('800000F3-1197629414','80000055-1197631694','OP/1030616','Shaldon Road
','Newton Abbot ','Devon ','
','XXX 4SQ
','UK',0,'80000006-1197628269',{d'2007-12-11'},' ',0)
Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has
returned the following error code and description to Alpha Five."#0d#0a#09"Consult your
database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected
lexical element not found: <identifier>"#0d#0a"SQL State is:
42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:
<identifier>"#0d#0a"SQL State is:
42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-18 07:45:54 |
All the string values in INSERT statements have single quotes, not double quotes, this applies to the InvoiceLine insert too. |
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-18 20:43:37 |
Bearing in mind that my INSERT statement works fine with double quotes, if no date is used, and that the line INSERT results in "Success=T", the changing of the double quotes to single quotes didnt seem likely to have much effect. However, one never knows for sure so I changed them.
INSERT INTO InvoiceLine( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate,
InvoiceLineAmount, FQSaveToCache ) VALUES ('80000014-1197629891','900.326 ',
6, 322,1)
Code=0.000000,Text="Success,NativeCode=0.000000,NativeText=,Syntax=,API=,RowsAffected=1.000
000,Success=T,Error=F,Canceled=F,Warnings=F
INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES
('800000F3-1197629414','80000055-1197631694','OP/1030632','Shaldon Road
','Newton Abbot ','Devon ','
','XXX 4SQ
','UK',0,'80000006-1197628269',{d'2007-12-12'},' ',0)
Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has
returned the following error code and description to Alpha Five."#0d#0a#09"Consult your
database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected
lexical element not found: <identifier>"#0d#0a"SQL State is:
42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:
<identifier>"#0d#0a"SQL State is:
42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F
Same result as before. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-18 23:15:06 |
Please look at the end of the QODBC Message log by clicking on "Review QODBC Messages" for the real errors and what the actual problem is with the SQL Statements you are executing.
|
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-18 23:51:58 |
The QODBC message log is empty. I cleared it yesterday and it hasnt added any messages since even though I have been running the process.
However what may be a better clue is from the Trace log. Please note - this is a snippet of what i believe to be the relevant part.
INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID", "RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", "BillAddressState", "BillAddressPostalCode", "BillAddressCountry", "IsPending", "TermsRefListID", "ShipDate", "Memo", "IsToBePrinted") VALUES ('8000004E-1197628865', '80000055-1197631694', 'OP/1030822', 'Siskin Drive Parkway West ', 'Middlemarch Business Park ', 'Coventry ', ' ', 'CV3 4PW ', 'UK', 0, '80000006-1197628269', {ts '{d'2-00-7- 12:-1:3'.}'}, ' ', 0)
It is looks like its trying to convert the date to time. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-19 07:31:18 |
Correct: {ts '{d'2-00-7- 12:-1:3'.}'} is what is giving you a "[QODBC] Expected lexical element not found: " error. |
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-19 11:17:55 |
"Correct: {ts '{d'2-00-7- 12:-1:3'.}'} is what is giving you a "[QODBC] Expected lexical element not found: " error. "
Yea right I figured that out thanks. What i dont understand is WHY im getting {ts '{d'2-00-7- 12:-1:3'.}'} in the trace log when im passing {d'2007-12-13''}. As far as I can see I am passing the date in the format you say it is supposed to be. I have spent the best part of a day messing with that format to see what the trace log produces and I am no closer to figuring out HOW TO MAKE THIS WORK.
I have managed to insert 540 invoice in my test company but without being able to enter any dates. Getting the invoices to work took me about 2 hours. Getting a date to work has so far taken me over a week and its getting dull.
If you are able to tell me WHY my dates arent working id be obliged but at the moment its looking like Im doing the right things and qodbc isnt.
I can only pass qodbc the date in one correct format - if it isnt {d'2007-12-13''} then id really like to know what it is, and if it is {d'2007-12-13''}. then id like to know why it doesnt work.
Thank you.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-19 14:02:06 |
|
|
|
md |
|
Group | : Members |
Posts | : 7 |
Joined | : 2007-12-17 |
|
Profile |
|
Posted : 2007-12-19 19:48:06 |
I am not using a date/time variable because it wouldnt be the correct format.. I am using a string constructed from elements of a date. I get the {ts in the trace log when I put the date in the format you specify, and I have tried it using your example date cut and pasted - hardcoded - into the insert statement.
If QODBC isnt reformatting it to add the {ts then im at a loss to where it comes from.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-20 08:01:57 |
| | |