Question Re: SQL Anywhere adding LAST_MODIFIED column to tables. in Technology Q&A

SQL Anywhere adding LAST_MODIFIED column to tables.

Former Member — Thu, 20 Oct 2011 14:19:03 GMT

I am working on a huge, messy, old and very ugly legacy system. We have created a remote application and are using UltraLIte as our DB there. Several of the forms in our system use INSERT statements without a list of columns. Now that SQL Anywhere has added this new column to the tables I am getting a mismatched columns error (ORA-009947).

So for example we have:

 INSERT INTIO table (SELECT col1, col2, col3 FROM table2)

We should have:

 INSERT INTO table (cola, colb, colc) (SELECT col1, col2, col3 from table2)

One solution to the problem will be to modify the INSERT statements in the forms to have the list of columns, but if we do that it adds a lot more work and testing to the project.

Is there a way to create the SQLAnywhere system without having the LAST_MODIFIED column added to the DB tables?

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

VolkerBarth — Thu, 20 Oct 2011 14:29:36 GMT

A wild guess: Possibly adding a ", NULL" to your SELECT statement might help: If the LAST_MODIFIED column is the last in the table's order and has a DEFAULT value, that would insert that default.

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

Former Member — Thu, 20 Oct 2011 15:08:59 GMT

I assume the LAST_MODIFIED column was added when you deployed a MobiLink synchronization model with default options for timestamp download. If you don't want to have that column added to the table, you can change the synchronization model to instead use a shadow table for the LAST_MODIFIED column. That choice can be made in the wizard or afterward in the Download options for a table mapping.

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

Former Member — Thu, 20 Oct 2011 15:12:42 GMT

FYI, since this is a forum specific to SQL Anywhere, you usually don't need to add a sqlanywhere tag to questions. I did add a mobilink tag to this and another question you asked, since both are related to MobiLink.

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

Breck_Carter — Fri, 21 Oct 2011 06:43:26 GMT

Ummmmm... if the download_cursor scripts are using LAST_MODIFIED to cut the download stream size, dumping that column might be fatal to synchronization performance... what you are suggesting in effect is for them to switch to "download all rows all the time" 🙂

Since your answer got the Big Green Checkmark, I assume getting rid of LAST_MODIFIED is appropriate in the case... but for OTHER PEOPLE, switching to a shadow table for the LAST_MODIFIED column might be more appropriate. The usual reason for that is "Oracle DBA".

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

Former Member — Fri, 21 Oct 2011 10:17:16 GMT

Check again. The LAST_MODIFIED column is still there, but in a shadow table column with a foreign key to the original table. The previous download_cursor query just needs to be converted to a join.

Re: SQL Anywhere adding LAST_MODIFIED column to tables.

Former Member — Fri, 21 Oct 2011 13:54:08 GMT

As Russ has pointed out, I did suggest moving the column to a shadow table. To get rid of the column completely and "download all rows all the time" you would choose snapshot download instead of timestamp-based download in the sync model, but I didn't suggest that.

When using a shadow table for timestamp-based download (as I suggested), the generated download_cursor would use the LAST_MODIFIED column in the shadow table through a join.

I also assumed that the project is still in development, so that he could deploy to a "clean" consolidated (i.e. without needing to keep LAST_MODIFIED column values or drop that column). But even if moving the column to a shadow table lost all the existing values and the shadow table's columns were initialized to 1900-01-01, all rows would only be downloaded once, not all the time.