Problems with Publications when migrating Ultralite DB v.11 to v.16

[Former Member]

Update: I have identified where is the problem located, is in this part of the Publication (where clause), but still don't have solution:

TABLE "TPDA_SERVICE_LINE_DETAIL" WHERE NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
                                  FROM TPDA_JOB JO, TPDA_SERVICE_LINE SL, TPDA_M_STATE_MACHINE SM, TPDA_M_STATE_MACHINE SM2
                                  WHERE SM.VSTM_ENTITY = 'J'
                                    AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
                                    AND JO.NSTM_STATUS_ID = SM.NSTM_STATUS_ID
                                    AND SL.NJOB_JOB_ID = JO.NJOB_JOB_ID
                                    AND SM2.VSTM_ENTITY = 'S'
                                    AND SM2.NSTM_NUMBER = 1 -- Processed. If the Service Line is Failed, its details won't be uploaded.
                                    AND SL.NSTM_STATUS_ID = SM2.NSTM_STATUS_ID ),

I have also changed the query to be more compliant, but no sucess:

NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
FROM
TPDA_SERVICE_LINE SL
inner join TPDA_JOB JO on JO.NJOB_JOB_ID = SL.NJOB_JOB_ID
inner join TPDA_M_STATE_MACHINE SM on SM.NSTM_STATUS_ID = JO.NSTM_STATUS_ID
inner join TPDA_M_STATE_MACHINE SM2 on SM2.NSTM_STATUS_ID = SL.NSTM_STATUS_ID
WHERE SM.VSTM_ENTITY = 'J'
AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
AND SM2.VSTM_ENTITY = 'S'
AND SM2.NSTM_NUMBER = 1) -- Processed. If the Service Line is Failed, its details won't be uploaded.

Hi All!

I'm having some problems on migrating an Ultralite database (v.11) to version 16 using sybase central on Windows 7 x64 (using both 32 and 64 bit versions). I had unload the database with the Sybase central wizard, and created an XML containing all data and schema, and also publications. When I try to load this XML into a new database version 16, the apps Crashes:

I've identified that if i don't include the Publications in the unload, then all works.

Also when I try to create a new publication the Sybase central crashes:

[ODBC] [UltraLite Database] Connection was terminated
SQLCODE: -308
SQLSTATE: ERROR
SQL Statement:

I have tried also to recreate the publications with this SQL Script:

CREATE PUBLICATION "fullSynchro_Pub" (
  TABLE "TPDA_ADJUSTMENT_LINE",
  TABLE "TPDA_JOB",
  TABLE "TPDA_M_ACTION_TYPE",
  TABLE "TPDA_M_ADJUST_TYPE",
  TABLE "TPDA_M_ASSIGNED_QUESTIONNAIRES",
  TABLE "TPDA_M_COUNTRY_PARAMETERS",
  TABLE "TPDA_M_DATA_USE_CONSENT",
  TABLE "TPDA_M_ELECTRIC_COMPANY",
  TABLE "TPDA_M_ELECTRIC_REFUND_TYPE",
  TABLE "TPDA_M_FAILURE_REASON",
  TABLE "TPDA_M_INDICATOR_TYPE",
  TABLE "TPDA_M_JOB_TYPE",
  TABLE "TPDA_M_LANGUAGE",
  TABLE "TPDA_M_MANUFACTURER",
  TABLE "TPDA_M_MODEL",
  TABLE "TPDA_M_PARAM_DOMINIUM",
  TABLE "TPDA_M_PARAMETER",
  TABLE "TPDA_M_PRODUCT",
  TABLE "TPDA_M_PRODUCT_ADJUST_NEED",
  TABLE "TPDA_M_PRODUCT_GROUP",
  TABLE "TPDA_M_PRODUCT_TYPE",
  TABLE "TPDA_M_QUESTIONNAIRE",
  TABLE "TPDA_M_RISK_ASSESSMENT_QUESTION",
  TABLE "TPDA_M_RISK_QUESTION_ANSWER",
  TABLE "TPDA_M_STATE_MACHINE",
  TABLE "TPDA_M_STREET_TYPE",
  TABLE "TPDA_M_THERAPY_SORT",
  TABLE "TPDA_M_TRIP_TYPE",
  TABLE "TPDA_M_UNSIGNED_REASON",
  TABLE "TPDA_M_VAN_OPERATIONS",
  TABLE "TPDA_M_VAN_STATUS",
  TABLE "TPDA_MATERIAL",
  TABLE "TPDA_NEXT_VISIT",
  TABLE "TPDA_PATIENT",
  TABLE "TPDA_PATIENT_ADDRESS",
  TABLE "TPDA_PATIENT_DOCUMENTS",
  TABLE "TPDA_PATIENT_INDICATOR",
  TABLE "TPDA_PATIENT_SIGNATORIES",
  TABLE "TPDA_PATIENT_SIGNATURE",
  TABLE "TPDA_READING_METER_LINES",
  TABLE "TPDA_RISK_ASSESSMENT_ANSWER",
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SERVICE_LINE" WHERE NSTM_STATUS_ID IN (11,12),
  TABLE "TPDA_SERVICE_LINE_DETAIL" WHERE NSLI_SERVICELINE_ID = ANY (SELECT SL.NSLI_SERVICELINE_ID
                                  FROM TPDA_JOB JO, TPDA_SERVICE_LINE SL, TPDA_M_STATE_MACHINE SM, TPDA_M_STATE_MACHINE SM2
                                  WHERE SM.VSTM_ENTITY = 'J'
                                    AND SM.NSTM_NUMBER = 2 -- Done. If the Job is Failed or Recalled, its service line details won't be uploaded.
                                    AND JO.NSTM_STATUS_ID = SM.NSTM_STATUS_ID
                                    AND SL.NJOB_JOB_ID = JO.NJOB_JOB_ID
                                    AND SM2.VSTM_ENTITY = 'S'
                                    AND SM2.NSTM_NUMBER = 1 -- Processed. If the Service Line is Failed, its details won't be uploaded.
                                    AND SL.NSTM_STATUS_ID = SM2.NSTM_STATUS_ID ),
  TABLE "TPDA_SUMMARY_MATERIAL",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_THERAPY",
  TABLE "TPDA_THERAPY_PARAMETER",
  TABLE "TPDA_TRIP",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1,
  TABLE "TPDA_W_IDENTIFIERS",
  TABLE "TPDA_W_SQL_SENTENCES"
)
GO
CREATE PUBLICATION "loadVanSynchro_Pub" (
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1
)
GO
CREATE PUBLICATION "loadVisionOx_Pub" (
  TABLE "TPDA_VISIONOX"
)
GO
CREATE PUBLICATION "openTrip_Pub" (
  TABLE "TPDA_SAFETY",
  TABLE "TPDA_SYNCHRO_INFORMATION",
  TABLE "TPDA_TRIP",
  TABLE "TPDA_VAN_LOADED_MATERIAL" WHERE BVLM_ACCEPTED = 1,
  TABLE "TPDA_VAN_RECOUNT_MATERIAL" WHERE BREC_ACCEPTED = 1
)
GO

But I only can create the first one ("fullSynchro_pub").

When I try to delete it on the new database, i get the same error again...

There is a bug related or something that i'm doing wrong? Somebody can help?

Thanks a lot. I've attached the full SQL Script and XML File to help understanding.

Thanks a lot in advance.

Message was edited by: Naim Raja

OLDv11_PDAHealthCareBD.sql.txt.zip

[jeff_albion · Accepted solution]

Hello Naim,

See the documentation for the restrictions on creating publications. You cannot use a subquery here:

The search condition in the WHERE clause can only reference columns that are included in the article. In addition, you cannot use any of the following in the WHERE clause:

subqueries
variables
non-deterministic functions

These conditions are not enforced, but breaking them can lead to unexpected results.

Your definition is using a subquery:

WHERE NSLI_SERVICELINE_ID = ANY (SELECT ...

You will have to re-formulate how you specify this definition for the publication.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

HI Jeff! Thanks for your answer!

This publication was existing AS-IS in the previous version (v.11) with no known problems.

We are migrating all software (mobilink server and Ultralite client) to current version (v.16) and evaluating the migration effort and risk. This is a limitation of new version?

Now i have to emulate same behaviour on new migrated database... I'm thinking to add some .Net code on mobilink server to filter that registers. There is other way to do that?

Thanks a lot.

[jeff_albion · Accepted solution]

Hi Naim,

This is a limitation of new version?

I don't think so - version 11 had similar restrictions. What I would agree is that it's definitely being handled differently than in previous versions. I have opened CR #765434 to investigate this behaviour further - I will update this thread when we learn more and better understand the intended behaviour.

I'm thinking to add some .Net code on mobilink server to filter that registers. There is other way to do that?

Yes, you can do this on the server-side by writing the table upload event (in SQL, Java, or .NET) to filter out the rows explicitly. I would generally recommend a SQL stored procedure in the consolidated database for the best performance.

Regards,

Jeff Albion

SAP Active Global Support

[jeff_albion · Accepted solution]

Hi Naim,

Upon further investigation, this is actually a bug that has now been fixed in 12.0.1.4157 and 16.0.0.2006 with the publication clauses being too long -  see: CR #765434.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

Hi Jeff!

For some reason, i couldn't see the details for the CR #765434. in what EBF is it?

thanks a lot.

[jeff_albion · Accepted solution]

Hi Naim,

Yes, the Sybase 'Change Request' website has been retired as of October 1st, 2014 - see my response here: The 'Change Requests' site does not work properly - SQLA Forum

You can either view the README information when you download the SQL Anywhere Support Package (SP) (a.k.a. EBF) from the "Info" link on the SAP Support Portal / SAP Service Marketplace to download an HTML file, or you can use the two Notes we have created for the SQL Anywhere READMEs that will be kept up-to-date which you can search directly:

• SQL Anywhere 12 Release Notes - https://service.sap.com/sap/support/notes/2078167
• SQL Anywhere 16 Release Notes - https://service.sap.com/sap/support/notes/2075367

Thanks,

Jeff Albion

SAP Active Global Support