FrontAccounting forum — SQL Database Error on Direct Invoice Entry

Re: SQL Database Error on Direct Invoice Entry

null@example.com (zeroone2020) — Mon, 30 Mar 2009 01:15:16 +0000

I am having this problem too. to reproduce the error:

1. install version 2.0.x (i m using 2.0.4 and 2.0.7 to be exact).
2. upgrade to 2.1
3. follow the upgrade procedure here: https://frontaccounting.com/wbt/pages/download/update-guide.php
4. login to frontaccouting as admin
5. click on "Direct Invoice"

------------------------- Error Message ----------------------------------------------------------
DATABASE ERROR : could not get POS definition
error code : 1064
error message : You have an error in your SQL syntax near 'AND pos.pos_location=loc.loc_code AND pos.pos_account=acc.id' at line 1
sql that failed was : SELECT pos.*, loc.location_name, acc.bank_account_name, acc.account_code FROM 0_sales_pos as pos,0_locations as loc,0_bank_accounts as acc WHERE pos.id= AND pos.pos_location=loc.loc_code AND pos.pos_account=acc.id
------------------------- Error Message ----------------------------------------------------------

Hope can help.

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Sun, 29 Mar 2009 19:52:43 +0000

In post 19, item 4, you must also change all other pos to other locations (da) do this before you go and delete locations,

I think this is were the problem was, but works now... on my local that is, I still get the error no my hosted, same db file, same scripts?

May there is some server side cache or something, I will try later... ok, maybe some browser cache, but all is good, I think this error is gone for good.

Alvin and

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Sun, 29 Mar 2009 09:15:16 +0000

Well, I'm lost. I have checked procedure from post 19 together with manual setting described in post 21 using sql data file you've send me, and all works right now. I can entry Direct Invoices and read all sales orders without errors.

In post 19 point 3 is obsolete, as voided SO are also in inquiry result and without fixing from_stk_loc are read with errors. So change form post 21 is also necessary.

Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Sun, 29 Mar 2009 01:18:55 +0000

There are no sales orders in the the system, `0_sales_orders`MySQL returned an empty result set (i.e. zero rows).

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Sat, 28 Mar 2009 21:10:53 +0000

Well, in fact broken sales orders still can be not readable. I guess the records are also unusable, as they are simply artifacts of failed Direct Invoice entry. If you want get rid with them - do it in phpmyadmin. If you prefer to keep them for some reason change all empty from_stk_loc to some real location.loc_code value from your database.
Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Sat, 28 Mar 2009 16:45:10 +0000

no, that did not do it, still got the error

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Sat, 28 Mar 2009 13:41:00 +0000

1. update source to latest main CVS version - Default POS will be visible
2. enter all defined users, set right POS and update them
3. void broken sales orders
4. delete Default location and POS if you don't want to use them.
Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Fri, 27 Mar 2009 19:06:32 +0000

So how can I delete default location and not get this error?

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Thu, 26 Mar 2009 16:03:31 +0000

Hello,
This ugly upgrade bug has been finally localized and fixed. Main CVS branch updated.

Thanks Alvin for your kindly help.

Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Wed, 25 Mar 2009 08:30:33 +0000

You can send my the db files by email, but once again the date of broken records entry in relation to version upgrade date is important

You have found that the source of error is empty sales_orders.stock_from_loc in some records. Can you find whether those records were added to database BEFORE upgrade, or AFTER? You certainly have db dump made before version upgrade, so it should be easy to find.

When the empty stock_from_loc were in original database, we should deal with bad data during upgrade. Otherwise we have some bug in new code resulting with incorrect sales_order records.

Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Wed, 25 Mar 2009 03:45:29 +0000

I am not sure, the file producing the error is a new file. I have a db file that produces the error (new) and one the does not (old), I did a text diff on them

this is non error diff

for table `0_chart_master`
`tax_code` int(11) NOT NULL default '0',

for table `0_company`
`custom0_name` varchar(60) NOT NULL default '',
`custom0_value` varchar(100) NOT NULL default '',

I tried to get the non error file to produce the error, but could not. I tried to get the error file to not produce the error but could not.

I tried everything I could, the results are the same using script 2.1 and 2.2

Maybe I could send you the db files and you could play with them?

Let me know, thanks

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Tue, 24 Mar 2009 21:27:22 +0000

Were the fatal records with empty from_stk_loc added after upgrade, or before? Anyway it is not good, but we should know this to decide how to deal with this problem.

Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (alvin) — Tue, 24 Mar 2009 15:08:34 +0000

Janusz,

The error: (MySQL returned an empty result set (i.e. zero rows)) is coming from statement (AND 0_locations.loc_code = 0_sales_orders.from_stk_loc) in looking at the tables, even thou enters are being made in 0_sales_orders, there are no enters in column (from_stk_loc)

What you think?

Alvin

Re: SQL Database Error on Direct Invoice Entry

null@example.com (itronics) — Thu, 19 Mar 2009 22:03:49 +0000

Could you explain me how have you fixed the problem? I still cannot find the source of the trouble nor reproduce it.

Janusz

Re: SQL Database Error on Direct Invoice Entry

null@example.com (ScorpKing) — Thu, 19 Mar 2009 21:40:42 +0000

The error in the first post. It was a database error. I've exported all the important info and recreated the database. I've noticed that some of the functions like Cash/Delayed in Direct Invoice didn't show up until I fixed the DB.