Sales Order Import

530 views
Skip to first unread message

enrique

unread,
Nov 22, 2013, 10:49:52 PM11/22/13
to idem...@googlegroups.com

Hi,

I was wondering if there were changes to the c_order table that requires a not-null field that I need to update to.
I'm trying to import a GardenWorld sales order using the Sales Order Insert template sample from the NF1.0 ImportCSV 
tutorial but have been getting the following error. 
'org.postgresql.util.PSQLException: Error: syntax error at or near "null" Position: 21.

I've been able to import records in the other transaction windows except this one.

I'm using java 7 and 1.0c. It was a fresh database install from the dump file and I ran migration scripts until 1.0c.

Thanks.

Thomas Bayen

unread,
Nov 23, 2013, 7:19:52 AM11/23/13
to idem...@googlegroups.com
Hi,

I am an experienced user (and lover) of the ImportCSV button. This is a
very powerful feature but my big love can be a diva. :-)

I tried to reproduce your problem but could not see it in my GardenWorld
(quite actual development version) or on http.demo.idempiere.com.

I downloaded the example file and had to delete two columns:

C_OrderLine>AD_Org_ID[Name]
C_OrderLine>M_Warehouse_ID[Value]

The error messages about that were clear. When I think about it it is
clear that these lines do not belong there. So I think this was not your
issue.


AFAIK the importer checks every input you make. He has some errors and
some flaws but every issue I had before gave errors inside the importer
and not in the produced SQL. Yor error message is strange for me.


To debug your issue you can do these things:

* try to reproduce the issue on demo.idempiere.com
* show us your input csv file if you changed it from the downloaded
example. It may be there is something interesting in your data that
triggers a bug
* look into the whole stacktrace of the Exception (in the server log)
and if this does not help show it to us.
* switch on the full logging in your PostgreSQL server
(log_statement='all') and show us the log of the erroreous sql statement.


I would like to hear from you either if you solve that alone. We should
use this to make a better documentation or keep the information when
someone reworks the importer code at some time. The best way would be to
document the solution in the wiki. But a mail here is also ok. :-)

Thomas
--
Jakob Bayen KG, Bleichpfad 20-22, 47799 Krefeld
AG Krefeld HRA 416 Tel +49/2151/65 80 9-0

enrique

unread,
Nov 25, 2013, 5:14:41 AM11/25/13
to idem...@googlegroups.com
Hi Thomas,

For the Sales Order Import, this is what happened:

1. Downloaded the SalesInsertExample.csv. This worked in the demo site.  
2. I used the same file (without changes) in my local set-up. 
3. I got the error  'org.postgresql.util.PSQLException: Error: syntax error at or near  "null".
4. This is the result of the posgresql log file:

2013-11-25 23:01:51.853 NZDT,"adempiere","idempiere",4181,"127.0.0.1:40327",52931c5c.1055,690,"INSERT",2013-11-25 22:46:04 NZDT,10/637,0,LOG,00000,"execute <unnamed>: INSERT INTO AD_Issue (Processing,JavaInfo,ReleaseTag,Processed,OperatingSystemInfo,DatabaseInfo,StatisticsInfo,Local_Host,AD_Issue_UU,Version,DBAddress,IssueSummary,Record_ID,ProfileInfo,SystemStatus,ReleaseNo,SourceClassName,SourceMethodName,LoggerName,Created,IsActive,CreatedBy,Updated,UpdatedBy,AD_Org_ID,Name,UserName,AD_Client_ID,AD_Issue_ID) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29)","parameters: $1 = 'N', $2 = 'OpenJDK Client VM 23.7-b01', $3 = 'iDempiere', $4 = 'N', $5 = 'Linux 3.5.0-23-generic unknown', $6 = '9.1.10;PostgreSQL 9.2 JDBC4 (build 1003)', $7 = 'C4U15B178P2235I8L24M28', $8 = 'adempiere-VirtualBox/127.0.1.1', $9 = '6257840d-5ec9-4c9e-ac29-54a5eae77b83', $10 = '2013-06-27', $11 = 'jdbc:postgresql://localhost:5432/idempiere?encoding=unicode', $12 = '>>org.adempiere.exceptions.AdempiereException: org.adempiere.exceptions.DBException: org.postgresql.util.PSQLException: ERROR: syntax error at or near ""null""
  Position: 21
>>org.adempiere.exceptions.DBException: org.postgresql.util.PSQLException: ERROR: syntax error at or near ""null""
  Position: 21
[SQL: 0, 42601]
>>org.postgresql.util.PSQLException: ERROR: syntax error at or near ""null""
>>  Position: 21
>> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
>> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
>> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
>> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
>> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
>> at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
>> at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>> at reflect.Method.invoke(Method.java:606)
>> at org.compiere.db.StatementProxy.invoke(StatementProxy.java:126)
>> at com.sun.proxy.$Proxy0.executeQuery(Unknown Source)
>> at org.compiere.util.DB.getSQLValueEx(DB.java:1268)
>> at org.adempiere.impexp.GridTabCSVImporter.resolveForeign(GridTabCSVImporter.java:1272)
>> at org.adempiere.impexp.GridTabCSVImporter.preprocessRow(GridTabCSVImporter.java:780)
>> at org.adempiere.impexp.GridTabCSVImporter.fileImport(GridTabCSVImporter.java:284)
>> at org.adempiere.webui.panel.action.FileImportAction.importFile(FileImportAction.java:329)
>> at org.adempiere.webui.panel.action.FileImportAction.onEvent(FileImportAction.java:247)
>> at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2742)
>> at org.zko', $13 = '1', $14 = 'MH Food Corporation|TestClient|GardenWorld|SYSTEM|', $15 = 'E', $16 = '1.0c', $17 = 'org.zkoss.zk.ui.impl.UiEngineImpl', $18 = 'handleError:1359', $19 = 'org.zkoss', $20 = '2013-11-25 23:01:51.743', $21 = 'Y', $22 = '101', $23 = '2013-11-25 23:01:51.743', $24 = '101', $25 = '0', $26 = '?', $27 = '?', $28 = '11', $29 = '1002864'",,,,,,,,""

This is the result of another test: 

2013-11-25 21:43:50.076 NZDT,,,1038,"[local]",52930dc6.40e,1,"",2013-11-25 21:43:50 NZDT,,0,LOG,08P01,"incomplete startup packet",,,,,,,,,""
2013-11-25 21:43:50.078 NZDT,,,1037,,52930dc6.40d,1,,2013-11-25 21:43:50 NZDT,,0,LOG,00000,"database system was shut down at 2013-11-25 21:42:41 NZDT",,,,,,,,,""
2013-11-25 21:43:50.455 NZDT,"postgres","postgres",1054,"[local]",52930dc6.41e,1,"",2013-11-25 21:43:50 NZDT,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
2013-11-25 21:43:50.471 NZDT,,,838,,52930dc3.346,1,,2013-11-25 21:43:47 NZDT,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2013-11-25 21:43:50.472 NZDT,,,1059,,52930dc6.423,1,,2013-11-25 21:43:50 NZDT,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2013-11-25 22:00:16.202 NZDT,"adempiere","idempiere",2506,"127.0.0.1:40109",529310df.9ca,1,"PARSE",2013-11-25 21:57:03 NZDT,11/988,0,ERROR,42601,"syntax error at or near ""null""",,,,,,"SELECT null_ID FROM null WHERE Value=$1 AND IsActive='Y' AND AD_Client_ID=$2",21,,"


5. I tested importing in other windows (i.e. Inventory Move, Production (Single Product), Freight Category etc.) and I was able to import the files. It's only in the Sales Order window. 

6. This is my set-up  - Version: 1.0.0.qualifier; Database Version: 201311061318_IDEMPIERE-1083.sql; Created the database from adempiere dump file and migrated to 1.0c.

I am still trying to solve this and would appreciate any suggestions. 

Kind Regards


On Saturday, November 23, 2013 4:49:52 PM UTC+13, enrique wrote:

Hi,

Carlos Antonio Ruiz Gomez

unread,
Nov 25, 2013, 11:01:40 AM11/25/13
to idem...@googlegroups.com
Hi, can it be replicated in https://demo.idempiere.com/webui ?

If yes, can you please give steps to reproduce.

If not:
Did you add new columns to the tables being imported?

If yes:
check the added columns, the error is appearing on GridTabCSVImporter.resolveForeign
so, could be the problem is a column wrongly defined

Regards,

Carlos Ruiz

Thomas Bayen

unread,
Nov 25, 2013, 11:30:01 AM11/25/13
to idem...@googlegroups.com
Hi Enrique,

just one question to be sure I understood you right: You tried the very
same file on demo.idempiere.com and it worked and you tried it on your
home installation and it did not work. Right?

I walked through the code and have the following hints:

The error occurs because a foreign key table of a column is not defined.
For me it looks like you created a new column or changed a column and
forgot to set the right reference (e.g. a Column of type "Table" but no
entry in the field for the exact Table that is used.)

The column you are looking for can be in the SalesOrder table or in one
of the tables that are shown as tabs in the SalesOrder window.

AFAIS the column that has the problem is used in your file. Try with a
very short file and slowly add columns up to the point where the problem
arises.

In the code a bit before the Exception point I see a logging statement
that is:

if (log.isLoggable(Level.FINE)) log.fine("Setting " + columnName + "
to " + value);

If you set the logging to "fine" (in zk client click on the logo
top-left on the screen and go to tab "Errors" to do that) chances are
that the logging will tell you the name of the column. It is the last
logging output before the Exception.

regards,
Thomas
> <http://www.google.com/url?q=http%3A%2F%2Fhttp.demo.idempiere.com&sa=D&sntz=1&usg=AFQjCNEjN4ufb4uxe9VnPfWYYr10ldWB5A>.
>
>
> I downloaded the example file and had to delete two columns:
>
> C_OrderLine>AD_Org_ID[Name]
> C_OrderLine>M_Warehouse_ID[Value]
>
> The error messages about that were clear. When I think about it it is
> clear that these lines do not belong there. So I think this was not
> your
> issue.
>
>
> AFAIK the importer checks every input you make. He has some errors and
> some flaws but every issue I had before gave errors inside the importer
> and not in the produced SQL. Yor error message is strange for me.
>
>
> To debug your issue you can do these things:
>
> * try to reproduce the issue on demo.idempiere.com
> <http://www.google.com/url?q=http%3A%2F%2Fdemo.idempiere.com&sa=D&sntz=1&usg=AFQjCNFFB8YcBbfupywm0Rk-qkNQoQLhDg>

enrique

unread,
Nov 27, 2013, 3:36:52 AM11/27/13
to idem...@googlegroups.com
Hi Thomas/Carlos,

There seems to be an issue with the  C_Bpartner_ID in my input file or my installed database table. As Thomas suggested, I added the fields one-by-one 
to find the source of the issue.  My last Sales import file contained the following fields: ad_org_id, c_doctypetarget_id,  c_bpartner_d and c_bpartner_location_id,
m_warehouse_d.  It was always C_BPartner_ID that stopped the import.

I have limited knowledge of Eclipse and the iDempiere source code but I tried debugging the code.

In GridTabCSVImporter's  'preProcessor' method, a null value is returned to the 'foreignTable' variable after calling 'MColumn.getReferenceTableName();.
Ad_Org_ID, C_DocTypeTarger_ID returns a value.  However, when the column 'C_Bpartner_ID' column is passed to 'MColumn.getReferenceTableName()',
it returns a null.  I'm  not really sure but the 'getAD_Reference_Value_ID' was not returning a value for this column.
It is this null value that is passed to 'GridTabbCSVImporter.resolveForeign' method and which returns the following  "SELECT null_ID FROM null WHERE Value=$1 AND IsActive='Y' AND AD_Client_ID=$2",21," This now shows the 'null' error I've been getting in the Sales Order window. I changed the 'foreignTable' value from null to 'C_Bpartner' manually during the debugging process to test my theory and it went past the issue.   This is the part of the code in getReferenceTableName which I think is causing my issue. 
'rt' is always null below: 

"if (X_AD_Reference.VALIDATIONTYPE_TableValidation.equals(ref.getValidationType())) {
    MRefTable rt = new MRefTable(getCtx(), getAD_Reference_Value_ID(), get_TrxName());
if (rt != null)
foreignTable = rt.getAD_Table().getTableName();
}"

I've been trying to figure out what was wrong with the C_Order, AD_Reference or C_BPartner tables in my installation
but couldn't figure out why.

Just to give further information: 

1. I did not add any new columns in these tables. I checked each field in all these tables vis-a-vis the schema in QSS and it seems to be similar.
2. The import seems to work in the demo site.
3. I created a fresh database based on the QSS instructions. 
4. I created the database from Adempiere_pg.dmp and ran all the migration scripts in the 1.0c folder. The import seems to be consistent with
   the the items in the ad_migration table.
5. As part of my testing,  I randomly removed/added fields in the import file and got the past the issue but got 'AD_Org_ID is read only, M_Warehouse_ID is read only' etc.
    It also imported the file with 'issotrx' = 'N'.  I guess this is because the correct sequence wasn't followed?

I would appreciate any other suggestion to get past this. Can I recreate these tables with the issues only?


Kind Regards.










On Saturday, November 23, 2013 4:49:52 PM UTC+13, enrique wrote:

Carlos Antonio Ruiz Gomez

unread,
Nov 27, 2013, 8:30:52 AM11/27/13
to idem...@googlegroups.com
Enrique, if the import is running fine on demo but not on your local installation, is it possible that is data related?

Are you testing with GardenWorld or your own BPs?

Regards,

Carlos Ruiz
--

Thomas Bayen

unread,
Nov 27, 2013, 3:37:34 PM11/27/13
to idem...@googlegroups.com
Hello Enrique,

I see you are very comfortable with Eclipse and the debugger. Everything
in your analysis seems right for me and you describe the code parts I
myself looked into when I wrote my last posting.

So you really should be on the right track to find the problem by
yourself. ;-)

You should check again in the "Tables & Columns" Window the table
"C_Order", column "C_BPartner":

* Is the "Reference" set to "Search"? (Not "Table" or others)

See exactly which value is read in your code and what happens. If your
debugger is in the very exact code you wrote here you should see
everything you need when stepping through this part.

Double-check if the tables/columns you are talking about are the same
tables/columns you open in the "T&C" window are the same tables/columns
your java code addresses.

If this does not work you may try as a test to set the "Reference Key"
of BPartner to "C_BPartner (Trx)". Depending on the cause of the problem
it may be that this works for you.


Thomas
> --
> You received this message because you are subscribed to the Google
> Groups "iDempiere" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to idempiere+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

mit freundlichen Gr��en,

Thomas Bayen

enrique

unread,
Nov 30, 2013, 5:13:13 AM11/30/13
to idem...@googlegroups.com
Hi Thomas/Carlos,

Thank you for your help.

When Thomas said check the tables - I knew what went wrong.

In the C_Order table (among others), I changed the C_Bpartner_ID  Reference to 'Table' from 'Search'. 
The reason was I didn't want users to see the Search window. It had information such as Open Balances etc.
which I wanted to be confidential.  I changed it to 'Table' so that it will show a dropbox instead.

I didn't know changing the 'Reference' affected importation.  To solve this, I decided to set it back to 'Search'.
However, this affected my original intention of hiding information from users. 
In the end, I discovered that setting the 'Reference Key' to a 'C_BPartner (trx)' together with 
the change to 'Reference' will make work. Hopefully, this is a correct approach as I was able to import the 'GardenWorld'
file after.

On to my next problem - 'Inset Not allowed in TabNo=1' / 'Was not able to create a record'  which happened now and then
with a new file I created (i.e. the same new file sometimes imported successfully and sometimes did not import successfully).  
All related to the C_Orderline table now.  Still need to figure this out.

Anyway, thank you for your help again.

Kind Regards.


On Saturday, November 23, 2013 4:49:52 PM UTC+13, enrique wrote:

Thomas Bayen

unread,
Nov 30, 2013, 6:44:28 AM11/30/13
to idem...@googlegroups.com
Hi Enrique,

as I see it this is a bug. I filed an issue at
https://idempiere.atlassian.net/browse/IDEMPIERE-1586 for that.

To answer your next question about the "Inset not allowed in TabNo":
Yes, this is a bug too. I triggered it often and made a comment in
https://idempiere.atlassian.net/browse/IDEMPIERE-454 too. The fact that
IDEMPIERE-454 is still open says that the importer is still not ready.

As far as I figured out it depends on what content your window had
before you started the import. If you see an empty table without
records, the sub-tabs are kind of "locked" and a security mechanism
forbids to enter data into them. If now you start the importer it uses
parts of the window's infrastructure to import the data. But if it tries
to import sub-table data the "lock" is still active.

So I use as a workaround when I trigger this bug that I create one
dummy-record in the table, make sure that it is shown and then use the
importer. After that I delete the dummy record.

If this solution helps for you please tell it here or better in the jira
issue tracker so it might help someone who will work on the importer
code to reproduce and solve the bug.

Thomas
> --
> You received this message because you are subscribed to the Google
> Groups "iDempiere" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to idempiere+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/idempiere/79ac1467-65c5-4423-bbff-73ed6d2f136f%40googlegroups.com.

enrique

unread,
Dec 4, 2013, 5:31:56 AM12/4/13
to idem...@googlegroups.com
Hi Thomas,

I'd like to confirm your work-around solution to the bug you mentioned. 

I was able to consistently create a sales import when I created a dummy sales record
prior to importing.  I did that when I encountered the 'Inset not allowed..' error.

Thanks to this work-around until the bug is fixed.

Kind Regards.


On Saturday, November 23, 2013 4:49:52 PM UTC+13, enrique wrote:
Reply all
Reply to author
Forward
0 new messages