Build stored procedures for the DataLoader

The ipushpull DataLoader lets you pull data from ipushpull and push it to a stored procedure in your database. This article describes the format of the data that the DataLoader sends to the database and sample stored procs for Oracle and MySQL databases.

Note that example stored procedures are provided for information purposes only.

 

Data Formats

The data pulled from your ipushpull page will be passed to the stored procedure in a JSON array format. The data is a 2d representation of the page you are pulling and is not keyed. Each cell will contain a pair of values, the raw data as was given to the ipushpull page and the formatted representation of that data, as defined by formatting information sent to the page. ipushpull holds no type information for the data so both will just be strings. Your stored procedure will need to convert this data if you do not want to simply store these strings. Example:

{ [ [["VARCHAR_COL", "VARCHAR_COL"], ["NUMBER_COL", "NUMBER_COL"], ["DATE_COL", "DATE_COL"]],  [["A", "A"], ["1.1234", "1"], ["1234567890", "Fri, 13 Feb 2009 23:31:30 GMT"]], [["B", "B"], ["2.3688", "3"], ["9876543210", "Fri, 22 Dec 2282 20:13:30 GMT"]]   ] }

 

Stored Procedures - General Principles

All stored procedures called by the Database loader will need a single 'IN' parameter named 'ippJsonData'. For MySQL this should be of type 'JSON', for Oracle type 'CLOB', and for MS SQL Server & Access type 'text'.

MS SQL Server, MySQL and Oracle (12.1 upwards) have in built JSON support - see examples below. If you have a different database your stored procedure will need to parse the JSON string.



Oracle - Basic Example

This example parses a single row of data pulled from ipushpull and inserts it into a table called example_table.

CREATE OR REPLACE PROCEDURE usp_ConvertJSONToMixedTypes ( ippJsonData IN CLOB -- In parameter must be named this. CLOB to allow larger data sets ) AS sample_vchar VARCHAR2(1000); sample_number NUMBER; sample_date VARCHAR2(1000); converted_date DATE; BEGIN     -- Access first row of data, assuming we've included headers,     -- process each column and extract the first of the raw and formatted value pair     -- If we go outside bounds of array we will get an error,     -- so this approach is ok if were happy with dimensions of data and table we're sending it to.     -- See other example for methods to determine if we're at a valid point in array     SELECT JSON_VALUE(ippJsonData, '$[1][0][0]') INTO sample_vchar FROM dual;        -- Can explicitly tell JSON_VALUE to return a NUMBER (default VARCHAR2)     SELECT JSON_VALUE(ippJsonData, '$[1][1][0]' RETURNING NUMBER) INTO sample_number FROM dual;     SELECT JSON_VALUE(ippJsonData, '$[1][2][0]') INTO sample_date FROM dual;     -- Will explicitly need to do type conversion here, based on prior knowledge of what to expect     converted_date:= TO_DATE(sample_date, 'DD/MM/YYYY HH24:MI:SS');     INSERT INTO example_table (varchar_col, number_col, date_col)                       VALUES (sample_vchar, sample_number, converted_date); END usp_ConvertJSONToMixedTypes;

 

MySQL - Basic Example

This example extracts two character strings from the first row of data and inserts them into a table called example_table.

CREATE PROCEDURE usp_ImportJSON(IN ippJsonData JSON) BEGIN DECLARE chartest varchar(255) default null; DECLARE chartest2 varchar(255) default null; set chartest1 = JSON_UNQUOTE(JSON_EXTRACT(ippJsonData, '$[1][2][1]')); set chartest2 = JSON_UNQUOTE(JSON_EXTRACT(ippJsonData, '$[2][3][0]')); INSERT INTO example_table (col1, col2) VALUES (chartest1, chartest2); END

Microsoft SQL Server - Basic Example

This example is pushing the following ipushpull page into the database table example_table:

ProductCode

Quantity

Price

FGBL

500

113.9800

FGBN

1000

114.9900

 

The following stored procedure reads in the JSON and then extracts each formatted string item from the JSON and stores these into the table example_table:

The resulting table in MS SQL Database, the column types have also be shown to show how the data if in the correct format has been automatically interpreted into the correct format:

ProductCode

(nvarchar(50))

Quantity

(int)

Price

(Decimal(12,6))

FGBL

500

113.9800

FGBN

1000

114.9900


Enter search text