Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I have a database of several million subscribers to a couple of eletters. We host the various applications to manage the database, as well as the database itself. Every morning we have a process that does a query, saves all email addresses to a text file, then we FTP it to the email vendor where they load into their oracle database. Then send the emails, and they then send us an FTP file of bounces and other things we need to record to our database.

The problem is... that this just takes too long to create the file and send it. I'd rather get into a mode of just sending updates to them -- and vice versa.

Is this sort of data syncronization across different db platforms an appropriate task for Mobilink?

I should add that my database is SQLA 9.0.2 -- I don't know the version of the Oracle db.

asked 12 Oct '10, 14:26

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%

edited 12 Oct '10, 14:38


Technically speaking, it sounds like a job for MobiLink. If you have the ability to make DDL and other changes to the Oracle database to support the MobiLink server, it might even be easy... and it will run like a rocket, guaranteed.

But you don't, right? Have access to the Oracle database, I mean.

Practically speaking, unless you have a warm and fuzzy relationship with the email vendor, it's probably a non-starter. In the real world, "Oracle DBA" and "warm and fuzzy" are two phrases not often used together.

Or, you could hire a consultant who knows how to speak "Oracle DBA", someone from Sybase Professional Services, perhaps, or...

permanent link

answered 12 Oct '10, 18:46

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

1

You probably want a Henry Kissinger rather than John Bolton type of emissary.

(12 Oct '10, 18:48) Breck Carter

And how about the ability to sound ironic? Is that a must-have skill for the wanted consultant, too? Then I could name one:)

(12 Oct '10, 19:57) Volker Barth

Well, this is the next problem... don't even want to go down that path if it won't work technically. I'm keeping it as Plan B. They say "we have API's for the database!" Exploring that is plan A. Who knows what they really have. Thanks Breck.

(12 Oct '10, 20:07) Ron Hiner

Way off topic... but I was at Henry Kissenger's house a few years ago... very cool place. Huge yard. Really huge. Big enough so that three enormous military helicopters landed at the same time, out of one of them stepped Bush 43. Henry might be availble... I'll give him a ring. ;-)

(12 Oct '10, 20:11) Ron Hiner
1

@Ron: Maybe you can use SQL Anywhere's builtin web client functionality, if they have a web service open to the interweb. Maybe you can put a SQL Anywhere database in their shop and MobiLink to that... with whatever-is-necessary to go the last mile into their database. The possibilities are endless.

(12 Oct '10, 21:11) Breck Carter

@Breck. Wait. What? SQLA has web client? Don't tease me like this! Can you give me a hint -- like what it might be named so I could find it in the doc?

(12 Oct '10, 21:38) Ron Hiner
1

never mind... I found it... CREATE FUNCTION [ owner.]procedure-name ( [ parameter, ... ] ) RETURNS data-type URL url-string [ TYPE { 'HTTP[:{GET|POST}]' | 'SOAP[:{RPC|DOC}]' } ] [ NAMESPACE namespace-string ] [ CERTIFICATE certificate-string ] [ CLIENTPORT clientport-string ] [ PROXY proxy-string ]

can't wait to play... this is going to be cool!

(p.s. Thanks Jason! http://iablog.sybase.com/hinsperg/2008/06/accessing-web-services-from-sql-anywhere/ )

(12 Oct '10, 23:26) Ron Hiner
(14 Oct '10, 07:35) Breck Carter
More comments hidden
showing 5 of 8 show all flat view

There are two ways you can use MobiLink to sync to a database you don't have DDL access to.

The most straight forward is to use the -cs server option. This lets you put the server's system tables in a different database than the consolidated. In this case you would have an SA database you control which contains the ML system tables and the MobiLink server would have a -cs switch specifying an ODBC connection string to connect to that SA database, and a -c switch specifying an ODBC connection string to connect to the Oracle database. The big caveat with -cs switch is that it requires server to use the Microsoft Distributed Transaction Coordinator, which can be slow. The -cs switch was added in version 11 and you can use a version 11 server to sync with your version 9 SA database. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/ml-syncserver-s-5359674.html*d4e2495

The alternative is a bit more complicated. You can use the MobiLink direct row API to inspect the upload stream and do whatever you want with it. As with the first option, you'll need an SA consolidated for ML to use. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/handle-uploaddata-ml-scriptsref.html*d4e15667

There's an example from that page that will do almost what you want:

public void handleUpload( UploadData ut ) 
  throws SQLException, IOException {
  // Get an UploadedTableData instance representing the
  // email_addresses table.
  UploadedTableData addressesTable = ut.getUploadedTableByName("email_addresses");
  // Get inserts uploaded by the MobiLink client.
  java.sql.ResultSet results = addressesTable.getInserts();
  while( results.next() ) { 
    // You can reference column names here because SendColumnNames is on
    // Get the primary key.
    int pk = results.getInt("pk");

// Get the uploaded num_ordered value.
    String email = results.getString("email_address");

// The current insert row is now ready to be uploaded to wherever
    // you want it to go (a file, a web service, and so on).
    magic( pk, email );
  }

results.close();

}

This opened a JDBC result set over the data that was uploaded from the remote and then called the magic function for each row in it. You just have to write the magic function to do what you need done. For example, you could connect to the Oracle database and insert the row, or you could toss the rows in a file and later email them. If you also want to send a download to the remote, there is API for modifying the download stream. See http://dcx.sybase.com/index.html#1100en/mlserver_en11/ml-objectbased.html for the full direct row API docs.

permanent link

answered 13 Oct '10, 21:12

Bill%20Somers's gravatar image

Bill Somers
1.2k820
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×371

question asked: 12 Oct '10, 14:26

question was seen: 1,828 times

last updated: 13 Oct '10, 21:12