Importing straight to SQL
goravanis at gmail.com
Mon Jan 31 20:43:42 UTC 2022
I’m not having any luck.
I did the $excludefrominsert trick and it didn’t work, $doinserts included it anyways and I got the same error message about duplicate values in the primary key column.
Before that I tried assigning #NULL to the column and got an error "NotNull constraint violated"
Clearing the column doesn’t work, that assigns zeros, and then it gives the error that zero is already taken, duplicate value in other words
I wish that excludefrominserts worked.. but maybe it doesn’t work on $doinserts on a smartlist
Odd thing is that after it fails, I do a "Clear method stack" using the menu on the methods window, and a watch appears for a long time, I have to force quit, in that time, it went ahead and inserted records, even though I did a clear method stack.. the $doinserts command goes ahead and works, on SOME records.. perhaps ones that don’t violate any constraints.. this is odd behavior to me.. it has inserted up til now 34,000 records.. even though it "failed" every time with an error message in nativeerrortext.
Anyways, I’m very surprised that the "NotNull" thing comes up.. I realize you don’t want NULL values STORED in the primary key column on disk, but I thought passing a NULL to that column was what you DID during an insert command.. I thought you pass a null so that it assigns the value.. I’m surprised it is balking on null values in that column, the primary key column
As far as I can tell I am stuck.. can’t pass it zeroes, cannot pass it nulls, cannot exclude it from insert, what CAN I do? To get Data into Omnis using a smartlist and $doinserts…
Can you REMOVE a column from a SQL defined list and will it work thereafter? No, I just looked, no such beast.
So so far I am left only with allowing it to use the primary keys I already have from the previous SQLite table… I just have to delete the table in postgres, remake it, and import my data.. not going OVER keys already inserted.. the problem was that I was testing with the same file over and over so the keys were already inserted as per above where I state that $doinserts went ahead and inserted a lot of rows even though it gave a fatal error each time I tried it.. hence why I have to delete the data I have in there so far..
It is very odd to me that excludefrominsert doesn’t seem to work on a smartlist or $doinserts
And it is very odd to me that it complains on null values in the primary key column on inserting.. In SQLite you pass a null in the primary key to TELL IT to assign a primary key value to that column
More information about the omnisdev-en