MobiLink Upgrade v12 - questions marks

[bgreiman]

It is my understanding that the use of question marks in MobiLink SQL scripts is no longer supported with version 12.

Here is a typical script (upload_insert):

INSERT INTO cn_city
  (zip_code, city_state, buy_7up, buy_sqt, buy_drpepper, county, area_letters,
   last_modified_datetime, last_modified_user, area_letters_old )
ON EXISTING UPDATE
VALUES( ?, ?, ?, ?, ?, ?, ?, GREATER(?, CURRENT TIMESTAMP), ?, ?);

Two Questions:

1. We have about 4000-5000 scripts total. I am assuming that to migrate to v12 (from v10), we will need to re-write each of these to used named parameters in place of the question marks. Is this assumption correct? Any painless way or helpful suggestions to make this migration.

2. Once our consolidated database is running under v12, is it possible to still run Mobilink under v10 (still use mlsrv10 against v12 database). We could then re-write all scripts as time permits?

Thanks for your help and suggestions.

Brian

[Former Member]

Question marks are indeed still supported as late as v12. It is undecided if they will be removed in later versions, but they may be.

Converting the scripts is unfortunately a manual process. Our software doesn't have enough knowledge of the consolidated schema to know how to perform the conversion, so we couldn't create a tool for that. FWIW we did consider the problem very seriously, but a general solution wasn't feasible.

There are opportunities for some automation, though. For example, converting data scripts requires knowledge of each table's column names, column ordering, and primary-key-ness. Each data script defines a deterministic but different ordering. If you look at the documentation (eg. http://dcx.sybase.com/index.html#1201/en/mlserver/upload-update-ml-scriptsref.html*d5e23969 for the upload_update script), in the "Parameters" section you will find a table. Look at the "Order" column, which effectively tells you what the Nth question mark in the script should be. Putting it all together, you use each table's schema, the script's name, plus the script's documentation, to decide what parameter to use for each question mark. Converting non-data scripts (eg. begin_upload) is easier because the list of parameters on most (all?) of these is static.

Alternatively, in the example INSERT you gave, if the remote column names match the consolidated column names, and the SQL is as explicit and regular as it can be, then it may be sufficient to create a custom scanner for each statement type, to pick out the names and replace the question marks. Again, this would work best if the vast majority of scripts consistently followed well-defined patterns.

FWIW, I'd recommend Python or Perl. Of course, if coding a converter up is too much effort, then pure manual works too.

[former_SQLA_member1694884]

Question marks are deprecated and discouraged, but still supported. See http://dcx.sybase.com/index.html#1201/en/mlserver/script-parameters-writing-synch.html

I'm not sure when, if ever, this functionality will be removed.

I'll leave it to someone Reg or Jeff answer your question about multiple versions of the ML server connecting to the same consolidated.