Importing straight to SQL
david.a.davey at gmail.com
Tue Feb 1 01:30:16 UTC 2022
There's no reason to have to drop your table and recreate it. Simply do a
delete or truncate command to remove the existing data.
If the $excludefrominsert "trick" or assigning #null aren't working for
you, how about assigning the list's $line value to each record? You would
do this in a similar fashion as you did to assign #null to each line. It
seems that you don't care what value the PK gets so this would make it
unique. I am curious though why it seems that your file has duplicate PK
values, but possibly that is only due to running your import multiple times.
Finally, if something isn't working for you, please post the code you are
attempting to execute instead of saying that it doesn't work. Others are
using these commands successfully, so a second pair of eyes reading your
code may help spot the problem.
On Mon, Jan 31, 2022 at 3:43 PM Das Goravani <goravanis at gmail.com> wrote:
> 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
> 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
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
More information about the omnisdev-en