Automating imports

Classic Servoy
1

For those using Sybase ASA with Windows, the dbisql command-line utility makes it possible to automate imports and other actions with SQL statements without having to open and view Sybase Central.

For example, when the following bat file is executed it will automatically import records from a csv file into the contacts table of the Servoy sample udm database:

dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost c:\temp\import_contacts_data.sql

It calls the following sql text file:

INPUT INTO "DBA"."contacts"
FROM c:\\temp\\contacts_data.csv
FORMAT ASCII
DELIMITED BY ','
(name_first,name_last)

Executing the following bat file deletes all the records:

dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost c:\temp\delete_contacts_data.sql

It calls the following sql file:

DELETE "DBA"."contacts"
WHERE contact_id > 0

We wanted our users to be able to import records while at the host computer by simply double-clicking a bat file. The dbisql utility makes this possible.

Note: In the above example the contact_id needs to be set to autoincrement within Sybase Central.

More information on the dbisql command-line utility can be found at:

http://www.ianywhere.com/developer/prod … 000590.htm

Dean Westover

2

Excellent tip!

You could probably also execute that sql code without isql straight with the rawsql plugin…

3

Or you could do that without creating a server for servoy using sql through the data plug-in.

4

Same approach works for automating exports, with the bat file looking like this:

dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost C:\\temp\\export_contacts_data.sql

and the sql file looking like this:

SELECT *
FROM contacts;
OUTPUT TO c:\\temp\\exported_contacts_data.csv
QUOTE '"' ALL
FORMAT ASCII

Remove the “ALL” if you do not want quotes around numbers in the csv file.

Dean

5

Yes, we use this method to import data into the db.

Regards, Robert

jaleman:
Excellent tip!

You could probably also execute that sql code without isql straight with the rawsql plugin…

6

jaleman:
Excellent tip!

You could probably also execute that sql code without isql straight with the rawsql plugin…

I have not been able to do this with the rawsql plugin. I suspect that I am not using the proper syntax. If someone can provide an example of syntax that will work with the rawsql plugin based on the above import example it would be greatly appreciated.

Dean

7

Hello Dean

Following an example including the file read in. In this example the SQL command is an “insert into …” statement, change it to what you need. It’s in var sql1.
I also use Java to read the file as buffered character-input stream as it can be a big file and you are limited with the Servoy JavaScript plugins.file.readTXTFile(dataFile) read as this cmd tries to read in everything into the memory. But you can try, the code is the 3 lines (comments in the code).

Best regards, Robert

Code:
/*
Title: Inserts data into a table defined by the filename.

Author: Birgit Rieder, Robert Huber
Created: 28.06.2007
Modified: -

Arguments: -
Returns: -
Notes: -
*/

var dataFile = plugins.file.showFileOpenDialog();
if (!dataFile) return 0;

var successes = 0;
var failedRows = 0;
var failedString = “”;

var filename = dataFile.getName();
var tablename = (filename.split(“.”))[0];

//var fileContent = plugins.file.readTXTFile(dataFile);
//var fileRows = fileContent.split(“\n”);
//var columns = fileRows[0];

// Open input file and read a buffered character-input stream
var fr = new Packages.java.io.FileReader(dataFile);
var br = new Packages.java.io.BufferedReader(fr);

globals.line = br.readLine();
if ( globals.line == null )
{
application.output(“File " + dataFile + " is empty”);
return false;
}

var lineLength = globals.line.length;

// Set date format to ddmmyyyy
var statement1 = “set option date_order = ‘dmy’”;
var success = plugins.rawSQL.executeSQL(“TrackIT”, tablename, statement1);

var sql1 = “insert into " + tablename + " (” + globals.line + “) values (”;
var sql2 = “);”;

var i = 1; // Initialize row counter
while ( globals.line != null )
{
globals.line = br.readLine();
if ( globals.line )
{
var statement = sql1 + globals.line + sql2;
var success = plugins.rawSQL.executeSQL(“TrackIT”, tablename, statement);
if ( success )
{
successes += 1;
}
else
{
failedRows += 1;
if (failedRows < 10)
{
var rowNumber = i + 1;
failedString += ’ ’ + rowNumber;
}
else if (failedRows == 10)
{
failedString += ‘…’;
}
}
}
i++
}

br.close();

plugins.dialogs.showInfoDialog(
“Import finished”,
"rows inserted: " + successes + “, rows failed: " + failedRows + " (” + failedString + “)”,
“OK”);

Westy:

jaleman:
Excellent tip!

You could probably also execute that sql code without isql straight with the rawsql plugin…

I have not been able to do this with the rawsql plugin. I suspect that I am not using the proper syntax. If someone can provide an example of syntax that will work with the rawsql plugin based on the above import example it would be greatly appreciated.

Dean

8

I like the rawSQL tip, thanks!
because this one also works in a client.
The code of Westy, only works on the db-server itself, or else you have to open a port also, to access the db outside, your server, which brings security risks also…

9

Yes, I agree. Thank you Robert!

Dean

10

Glad I could help, did you already try it? Does it work in your environment?

Best regards, Robert

Westy:
Yes, I agree. Thank you Robert!

Dean

11

We’ve tried it, but do not have it working yet. Could you explain:

// Open input file and read a buffered character-input stream 
var fr = new Packages.java.io.FileReader(dataFile); 
var br = new Packages.java.io.BufferedReader(fr); 

globals.line = br.readLine();
12

Hi Dean,

The mentioned methods are pure and plain java.
Basically, what happens there, is that a file is opened and streamed into Servoy (line per line).

13

Thanks you Marcel, you are quicker than me in replying :-)

As Marcel said, instead of reading the whole file into the memory, what the readTXTFile does and to memory overflow leads (as there is no possibility to assign more memory to this cmd or at least nobody could tell me how to), the file is read line by line. Therefor the fr (file reference, i. e. pointer to the file and buffer reference). The global.line just holds a line at a given time. It MUST be global!

Hope you get a step further.

Regards, Robert

IT2Be:
Hi Dean,

The mentioned methods are pure and plain java.
Basically, what happens there, is that a file is opened and streamed into Servoy (line per line).

14

why must it be a global?
I’m using a variable, which works also.

15

As far as I remember, it sais so somewhere (although I can’t find the reference at the moment, I may be wrong, I think it’s on SUN’s Java Doc site). But if you say it works with a local variable, that would be even better.

Best regards, Robert

HJK:
why must it be a global?
I’m using a variable, which works also.

16

From a javascript point of view, a variable is just a variable, whether it’s a global, a local or a variable generated on the fly…

Paul

17

Robert Huber:
…Does it work in your environment?

Still not working here. I am probably missing something that should be obvious.

Is the following correct? To bring comma separated values into a “contacts” table, name the source file “contacts.csv”. Then replace “TrackIT” with our database name. Then the resulting SQL statement should end up looking like:

insert into contacts (“John”,“Doe”) values (“John”,“Doe”);

and that should work?

Dean

18

In the first bit you have to spec the column names, not the values

insert into contacts (first,last) values (“John”,“Doe”);

19

Okay, so the first line of the csv file must contain the column headers. That makes sense. I will go back and try again.

20

I have it working now. Sybase ASA appears to require single quotes around the values, so I inserted a line to replace the quotes:

utils.stringReplace(globals.line,'"',"'")

and added a flushAllClientsCache at the end to redisplay the results within Servoy.

For a speed test I imported 111,000 records with a first name and a last name column. The import took about five minutes. During the import the javaw.exe and dbsrv9.exe CPU processes were in the 48 to 52 range.

What is a good way to give the user feedback on the progress of the import without slowing the import down too much?

Thank you again Robert for providing this import solution!

Dean