Tips and tricks of the brand new LOAD DATA command
Tags: beginners, tutorial, sql, jdbc
Published: 2023-02-20 19:16:23

The last days I've work with the great new feature: [LOAD DATA](https://docs.intersystems.com/iris20212/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_loaddata) With this post I would like to share my first experiences with you. The following points do not contain any order or other evaluation. These are only things that I noticed when using the **LOAD DATA** command. It should also be noted that these points are based on the IRIS Version 2021.2.0.617 which is a preview release. So it may be that my observations do not apply to newer IRIS versions. But maybe they are helpful for others.

#### 1) The file path is on server side

I have done my first tests via JDBC. The first trap I stumbled into: The file and the filepath must-, _of course ;-)_ be on serverside! The JDBC driver handle this **not** on client side. Probably this is obvious, but I had not considered this at first.

#### 2) The file suffix is not  relevant

The docs says:

> "_The file name must include a .txt or .csv (comma-separated values) suffix._"

According to my observation the behavior is not so. The suffix is irrelevant.

#### 3) Read the docs! ... or where are the error rows?

While loading some datafiles, i missed rows. If there is a problem with a line, this line is ignored. This happens silently in the background and the client is not actively notified. After viewing <https://https//youtu.be/jm7bDK0FoiI> I've noticed that I have to check %SQL\_Diag.Result and %SQL\_Diag.Message to see the problems in details. I also noticed that this behavior is already described on this page: <https://docs.intersystems.com/iris20212/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_loaddata> ... so RTFM ;-)  
  
Some examples about what you can see:

<pre>SELECT * FROM %SQL_Diag.Result ORDER BY createTime DESC </pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhlay85zhsz8ac1wxucv.png)

Check the errorCount column of your loading.

  
You can see (row) details in %SQL_Diag.Message

<pre>SELECT * FROM %SQL_Diag.Message ORDER BY messageTime DESC /></pre>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hoseldaa06ni88qrl8tu.png) 

You can filter for a specific _diagResult_ (%SQL\_Diag.Result.ID = %SQL\_Diag.Message.diagResult)

<pre>SELECT * FROM %SQL_Diag.Message
WHERE diagResult=4
ORDER BY messageTime DESC
/></pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c5y4k6vuo8c0v5ahi21i.png) 

 

#### 4) LOAD DATA is not supported by $SYSTEM.SQL.Schema.ImportDDL

For my sample app [Openflights Dataset](https://openexchange.intersystems.com/package/openflights_dataset) I've tried to load all external files with LOAD DATA. The Statements are bundled within a text (sql) file where I also create the tables before.

I've learn you can't do that via $SYSTEM.SQL.Schema.ImportDDL.

By the way the documentation of [ImportDDL](https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_IMPORT) says that not all SQL Statements are supported. Only a few sql statements are listed on this page.  
LOAD DATA unfortunately does not belong to... by the way USE DATABASE unfortunately also not.

#### 5) For unicode handling you have change a setting

To avoid problems with data encoding during loading, please make the following setting on the %Java Server: -Dfile.encoding=UTF-8  
See more details in this [post](https://community.intersystems.com/post/load-data-sqlcode-any-idea#comment-177086). This problem should be gone in a next IRIS release.

#### 6) Loading stops with error, but data are loaded

Loading data via JDBC stops with an %qparsets error. That looks like this:

<pre>Error: [SQLCODE: &amp;lt;-400>:&amp;lt;Fatal error occurred>]
[Error: &amp;lt;&amp;lt;UNDEFINED>zExecute+83^%sqlcq.OPENFLIGHTS.cls10.1 *%qparsets>]
[Location: &amp;lt;ServerLoop>]</pre>

But don't worry the data was loaded anyway :-)  See more details in this [post](https://community.intersystems.com/post/load-data-sqlcode-any-idea#comment-177076).  
This problem should be gone in a next IRIS release.  
  
Andreas