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?

asked 20 Oct '11, 14:19

nbunney's gravatar image

nbunney
66248
accept rate: 100%

edited 15 Mar '13, 21:33

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272

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.

(20 Oct '11, 14:29) Volker Barth
1

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.

(20 Oct '11, 15:12) Graham Hurst

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.

permanent link

answered 20 Oct '11, 15:08

Graham%20Hurst's gravatar image

Graham Hurst
2.7k11843
accept rate: 30%

edited 20 Oct '11, 15:56

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".

(21 Oct '11, 06:43) Breck Carter
Replies hidden

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.

(21 Oct '11, 10:17) RussC_FromSAP

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.

(21 Oct '11, 13:54) Graham Hurst
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:

×295
×38
×29
×27

question asked: 20 Oct '11, 14:19

question was seen: 1,600 times

last updated: 15 Mar '13, 21:33