Exporting data

Exporting data, or saving data to a text file, saves all of the data in the current view to the text file, overwriting the existing data. This topic discusses several ways to export data. You can export data that has been imported from a text file back to that file or to another file. You can export data from a QueryDataSet to a text file. Or you can resolve data from a TableDataSet to an existing SQL table.

Exporting data to a text file is handled differently than resolving data to a SQL table. Both QueryDataSet and TableDataSet are StorageDataSet components. When data is provided to the data set, the StorageDataSet tracks the row status information (either deleted, inserted, or updated) for all rows. When data is resolved back to a data source like a SQL server, the row status information is used to determine which rows to add to, delete from, or modify in the SQL table. When a row has been successfully resolved, it obtains a new row status of resolved (either RowStatus.UPDATE_RESOLVED, RowStatus.DELETE_RESOLVED, or RowStatus.INSERT_RESOLVED). If the StorageDataSet is resolved again, previously resolved rows will be ignored, unless changes have been made subsequent to previous resolving. When data is exported to a text file, all of the data in the current view is written to the text file, and the row status information is not affected.

Data exported to a text file is sensitive to the current sorting and filtering criteria. If sort criteria are specified, the data is saved to the text file in the same order as specified in the sort criteria. If row order is important, remove the sort criteria prior to exporting data. If filter criteria are specified, only the data that meets the filter criteria will be saved. This is useful for saving subsets of data to different files, but could cause data loss if a filtered file is inadvertently saved over an existing data file.

Warning: Remove filter criteria prior to saving, if you want to save all of the data back to the original file.

Tutorial: Exporting data from a TableDataSet to a text file

When you export data from a TableDataSet to a text file, JBuilder creates a .SCHEMA file that defines the columns by name and data type. The next time you import the data into JBuilder, you do not have to define the columns, because this information is already specified in the .SCHEMA file.

Building on the example in Tutorial: Importing data from a text file, this tutorial demonstrates how to use the UI Designer to add a button for saving the data, with any changes, back to the same text file. The finished project is available in the samples/borland/samples/tutorial/dataset/ExportingData directory of your JBuilder installation under the name ExportingData.jpr.

  1. If you have not already done so, create the project in the Tutorial: Importing data from a text file topic. If you have created this project, open it now.

  2. In the Designer, add a ButtonControl component. With the ButtonControl selected, click the Properties tab of the Component Inspector. Set the text of the label property to Save changes.

  3. With the ButtonControl selected, click the Events tab of the Component Inspector. Double-click the actionPerformed() method. This changes the focus of the AppBrowser from the UI Designer to the Source pane and displays the stub for the actionPerformed() method.

  4. Add the following code to the actionPerformed() method:
      try {
          tableDataSet1.getDataFile().save(tableDataSet1);
          System.out.println("Changes saved");
      }
      catch (Exception ex) {
        System.out.println("Changes NOT saved");
        System.err.println("Exception: " + ex);
      }
    

  5. Run the application.

    When you run the application, if it compiles successfully, the application appears in its own window. Data is displayed in a grid, with a Save changes button.

  6. With the application running, select the string field in the first record of the Frame window and change the value in the field from A to Apple. Save the changes back to the text file by clicking the Save changes button.

  7. View the resulting text file in a text editor, such as Notepad. It will now contain the following data:

    1,"Apple"
    2,"B"
    3,"C"
    

    Close the text file.

  8. JBuilder automatically creates a .SCHEMA file to define the contents of the text file. View the .SCHEMA file in a text editor. Notice that this file contains information about the name of the fields that have been exported and the type of data that was exported in that field. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = 8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    

    Close the .SCHEMA file.

This concludes this part of the exporting data tutorial. You can continue to edit, insert, delete, and save data until you close the application, but you must click the Save changes button to write any changes back to the text file. When you save the changes, the existing file will be overwritten with data from the current view.

Tutorial: Using patterns for exporting numeric, date/time, and text fields

By default, JBuilder expects data entry and exports data of date, time, and currency fields according to the locale property of the column. You can use the exportDisplayMask property to read or save date, time, and number fields in a different pattern. Complete the example in Tutorial: Exporting data from a TableDataSet to a text file, close the running application, then complete the following steps in JBuilder. These steps demonstrate creating an exportDisplayMask for a new column of type DATE.

  1. Select Frame1 in the Structure pane. Expand the TableDataSet component in the tree by clicking on the + icon to its left, select <new column>, then modify the column's properties as follows:

  2. Run the application. In the running Frame window, enter a date in the locale syntax of your computer in the my_date column of the first row. For example, with the locale property set to English (United States), you must enter the date in a format of MM/dd/yy, like 11/16/95. Click Save changes to save the changes back to the text file.

  3. View the text file in a text editor, such as Notepad. It will now contain the following data:

    1,"Apple",11/16/95
    2,"B"
    3,"C"
    

    Close the text file.

  4. View the .SCHEMA file in a text editor. Notice that the new date field has been added to the list of fields. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = 8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    FIELD2 = my_date,Variant.DATE,-1,-1,
    

    Close the .SCHEMA file.

  5. Close the application and the text files and return to the JBuilder Designer. Select the my_date column and enter the following pattern into the exportDisplayMask property in the Component Inspector: MM-dd-yyyy. The syntax of patterns is defined in Edit/display mask patterns. This type of pattern will read and save the date field as follows: 11-16-1995.

  6. The application would produce an error now if you tried to run it, because the format of the date field in the text file does not match the format the application is trying to open. Manually edit the text file and remove the value ",11/16/95" from the first row.

    Instead of the above step, you could manually enter code that would establish one exportDisplayMask for importing the data and another exportDisplayMask for exporting the data.

  7. Run the application. In the running Frame window, enter a date in the my_date column of the first row using the format of the exportDisplayMask property, such as 11-16-1995. Click the Save changes button to save the changes back to the text file.

  8. View the text file in a text editor, such as Notepad. It will now contain the following data:

    1,"Apple",11-16-1995
    2,"B"
    3,"C"
    

    Close the text file.

  9. View the .SCHEMA file in a text editor. Notice that the date field format is displayed as part of the field definition. When the default format is used, this value is blank, as it is in the FIELD0 definition. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = 8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    FIELD2 = my_date,Variant.DATE,-1,-1,MM-dd-yyyy
    

    Close the .SCHEMA file.

This concludes this part of the exporting data tutorial track. When the text data file is imported next, the data will be imported from the information in the .SCHEMA file. To view data in the grid in a different pattern, set the displayMask property. To modify data in the grid using a different pattern, set the editMask property. These properties affect viewing and editing of the data only; they do not affect the way data is saved. For example, to enter data into a currency field without having to enter the currency symbol each time, use a displayMask that uses the currency symbol, and an editMask that does not contain a currency symbol. You can choose to save the data back to the text file with or without the currency symbol by setting the exportDisplayMask.

Exporting data from a QueryDataSet to a text file

Exporting data from a QueryDataSet to a text file is the same as exporting data from a TableDataSet component, as defined in Tutorial: Exporting data from a TableDataSet to a text file. JBuilder will create a .SCHEMA file that defines each column, its name, and its data type so that the file can be imported back into JBuilder more easily.

Note: BLOB columns will be ignored on export.

Saving changes from a TableDataSet to a SQL table

Use a QueryResolver to resolve changes back to a SQL table. For more information on using the QueryResolver to save changes to a SQL table, see Customizing the resolver.

Prior to resolving the changes back to the SQL table, you must set the table name and column names of the SQL table, as shown in the following code snippet. The SQL table and .SCHEMA file must already exist. The applicable .SCHEMA file of the TableDataSet must match the configuration of the SQL table. The variant data types of the TableDataSet columns must map to the JDBC types of server table. By default, all rows will have a status of INSERT.

tabledataset1.setTableName(string);
tableDataSet1.SetRowID(columnName);