Bulk Load

Classic Servoy
1

I am trying to import the data using SQL statement ‘LOAD INTO table FROM filename’ where filename is a text file.

The code I’m using is shown below;

var query = "LOAD INTO TABLE sap_products (material_no, batch_number, batch_management, product_no, description, unit_of_measure, measurements, volume," +
            "unit_of_volume, weight, unit_of_weight, kind_of_package, barcode, comodity_code, prod_hierarchy_1, prod_hierarchy_2, prod_hierarchy_3," +
            "prod_hierarchy_4, prod_hierarchy_5, hierarchy_description_1, hierarchy_description_2, hierarchy_description_3, hierarchy_description_4," +
            "hierarchy_description_5, apo_relevant, ean_code, ce_mark, refrigerant_type, country_of_origin, availability_date, ex_ostend_sales_price," +
            "currency_key, valid_from, unit_multiplier, unit_of_measurement, validity_date) FROM '\\\\192.168.1.15\\temp\\UK_100845_BBS_MAT.txt' " +
            "DELIMITED BY ',' "

 var exec = plugins.rawSQL.executeSQL('spaceair', 'sap_products', query)
      //flush the client's cache
      plugins.rawSQL.flushAllClientsCache(controller.getServerName(), controller.getTableName())
      //show the status of the import after completion
	var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj
	plugins.dialogs.showErrorDialog('Error',  'SQL exception: '+msg,  'Ok')

This returns the error shown below;

2008-02-27 17:11:06,421 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Th
rowable
java.lang.RuntimeException: com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -19
3: Primary key for table ‘sap_products’ is not unique ASA Error -193: Primary ke
y for table ‘sap_products’ is not unique
at com.servoy.extensions.plugins.rawSQL.SQLProcessor.executeSQL(Unknown
Source)
at com.servoy.extensions.plugins.rawSQL.RawSQLProvider.js_executeSQL(Unk
nown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:33
4)
at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1254)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2031)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.j
ava:94)
at com.servoy.j2db.scripting.e.call(Unknown Source)
at com.servoy.j2db.develop.debugger.k.a(Unknown Source)
at com.servoy.j2db.develop.debugger.k.access$100(Unknown Source)
at com.servoy.j2db.develop.debugger.k$2.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Should the order in which I’m importing the columns be in the same order as it is in the text file?

I have also attatched the text file for your reference.

Help would be appreciated.

Thanks in advance…

UK_100845_BBS_MAT.zip (1.64 MB)

2

The error says you have a primary key constraint violation. That means you are trying to insert a record with a primary key that already exists. This is not a problem of any method, but your data.

3

One way that I use to test the data is to use the database vendor utilities to execute the SQL with the same file into another database/table. You should get the same PK non-unique error. You may need to run some offline application that checks for data integrity prior to loading?

4

We have tried running this from within Sybase central and you are correct it gets the same PK error. The problem is, the txt file we get sent doesn’t contain a PK but the table has sap_product_id which should be an auto-incremented vallue from the db. When loading the data - can the db not auto insert a pk for each row without it having to exisit in the text file?

Many thanks

5

So, for the next time - remind me to turn DEFAULTS to ON! ;-)

“DEFAULTS option By default, DEFAULTS is OFF. If DEFAULTS is OFF, any column not present in the column list is assigned NULL. If DEFAULTS is OFF and a non-nullable column is omitted from the column list, the database server attempts to convert the empty string to the column’s type. If DEFAULTS is ON and the column has a default value, that value is used.”

LOAD INTO TABLE sap_products (material_no, batch_number, batch_management, product_no, description, unit_of_measure, measurements, volume,
unit_of_volume, weight, unit_of_weight, kind_of_package, barcode, comodity_code, prod_hierarchy_1, prod_hierarchy_2, prod_hierarchy_3,
prod_hierarchy_4, prod_hierarchy_5, hierarchy_description_1, hierarchy_description_2, hierarchy_description_3, hierarchy_description_4,
hierarchy_description_5, apo_relevant, ean_code, ce_mark, refrigerant_type, country_of_origin, availability_date, ex_ostend_sales_price,
currency_key, valid_from, unit_multiplier, unit_of_measurement, validity_date) FROM ‘\\192.168.1.15\temp\UK_100845_BBS_MAT_1.txt’
DEFAULTS ON
DELIMITED BY ‘,’

Thanks though!