Upload and download scripts

When a table contains spatial columns, the upload and download scripts may vary greatly depending on the type of consolidated database being used. The following examples show some sample upload and download scripts for spatial data for the consolidated databases currently supported by the MobiLink server.

For the examples below, the synchronization table is assumed to be as follows:

create table test (c1 int not null primary key, c2 st_geometry )

SQL Anywhere sample scripts

The following is a sample upload_insert script for SQL Anywhere:

INSERT INTO test VALUES( {ml r.c1}, ST_Geometry::ST_GeomFromBinary({ml r.c2:data},{ml r.c2:srid}) )

The following is a sample download_cursor script for SQL Anywhere:

SELECT c1, c2.ST_AsBinary(), c2.ST_SRID() FROM test

Microsoft SQL Server sample scripts

The following is a sample upload_insert script for Microsoft SQL Server:

BEGIN
      DECLARE @c1 INTEGER
      DECLARE @v1 VARBINARY(max)
      DECLARE @s1 INTEGER
      DECLARE @g1 geometry
      SELECT @c1 = {ml r.c1}
      SELECT @v1 = {ml r.c2:data}
      SELECT @s1 = {ml r.c2:srid}
      IF @v1 IS NULL
            SELECT @g1 = NULL
      ELSE
            SELECT @g1 = Geometry::STGeomFromWKB(@v1,@s1)
      INSERT INTO test VALUES( @c1, @g1 )
END

The following is a sample download_cursor script for Microsoft SQL Server:

SELECT c1, c2.STAsBinary(), c2.STSrid FROM test

Oracle sample scripts

The following is a sample upload_insert script for Oracle:

DECLARE 
     v_c1 INTEGER; 
     v_v1 sdo_geometry; 
     v_s1 INTEGER; 
     v_u1 blob;
BEGIN
     v_c1 := {ml r.c1};
     v_u1 := {ml r.c2:data};
     v_s1 := {ml r.c2:srid};
     IF v_u1 IS NULL THEN
           v_v1 := NULL;
     ELSE
           v_v1 := sdo_geometry( v_u1, v_s1 );
     END IF;
     INSERT INTO test VALUES( v_c1, v_v1 );
END;

The following is a sample download_cursor script for Oracle:

SELECT c1, g.c2.Get_WKB(), g.c2.sdo_srid FROM test g

IBM DB2 sample scripts

The following is a sample upload_insert script for IBM DB2:

BEGIN ATOMIC
      DECLARE v_c1 INTEGER;
      DECLARE v_v1 BLOB(10m);
      DECLARE v_s1 INTEGER;
      SET v_c1 = {ml r.c1}; SET v_v1 = {ml r.c2:data}; SET v_s1 = {ml r.c2:srid};
      INSERT INTO test VALUES( v_c1, ST_Geometry( v_v1, v_s1 ) );
END

The following is a sample download_cursor script for IBM DB2:

SELECT c1, ST_AsBinary( c2 ), ST_SRID( c2) FROM test

MySQL sample scripts

The following is a sample upload_insert script for MySQL:

INSERT INTO test VALUES( {ml r.c1}, GeometryFromWKB({ml r.c2:data},{ml r.c2:srid}) )

The following is a sample download_cursor script for MySQL:

SELECT c1, AsBinary( c2 ), SRID( c2 ) FROM test

For specific information about each type of consolidated database, see:

Discuss this page in DocCommentXchange.