Subscribe to this thread
Home - General / All posts - Question - How does Manifold Send bulk insert SQL Statement
firsttube


1,439 post(s)
#12-Jun-15 21:16

I have a drawing linked from SQL Server 2008. When I copy multiple objects from a local drawing into this linked drawing, what statement would manifold be sending to the server?

The reason I ask is that I have a trigger on the table that fires after insert. The trigger is supposed to updateanother column in the same table with a value. The trigger works for a single inserted record, but not for multiple records inserted at one time in manifold. Inserting multiple records from within SQL does fire the trigger for each row.


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

artlembo


3,400 post(s)
#13-Jun-15 00:42

Can you issue the SQL via VBScript in a loop?

for I=0 to n

Qry = "some statement"

Qry.run

Next

jkelly


1,234 post(s)
#14-Jun-15 23:55

If you are running Sql Server 2008 (not express), you should have access to the Profiler (it comes bundled). If you fire that up and watch it while you use Manifold, you will see the flow of Sql statements.


James Kelly

http://www.locationsolve.com

firsttube


1,439 post(s)
#15-Jun-15 18:20

I don't have the ability to run Profiler because of my permissions. I was just curious if anyone knew off-hand what SQL statement Manifold would issue at the server for multiple-object insert. I'd like to know if Manifold uses "BULK INSERT" statement for this, as this would disable the trigger (I think).

This trigger creates an ID value based on an object's location within a grid. It would need to run without the use of VBscript within Manifold.


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

jkelly


1,234 post(s)
#15-Jun-15 23:56

I'm pretty sure that Manifold doesn't use the BULK INSERT statement.

I can't remember exactly how Manifold does it, but I do remember that it wasn't as I expected. Something to do with inserting with nulls, and then updating. I know I have had trouble implementing triggers in the past because of this unexpected behaviour.

Even if you can get hold of someone that does have permissions, and get them to extract and send you a trace from the profiler, that's better than stabbing around in the dark.


James Kelly

http://www.locationsolve.com

walter
20 post(s)
#16-Jun-15 13:55

As far as I know

1. Manifold inserts all Geometry (runs quite fast), but with empty table -> you possibly run into trouble with unique constraints

2. then Manifold updates the table, record by record (lasts forever for big data sets)

firsttube


1,439 post(s)
#16-Jun-15 14:44

To the point about unique constraints -- there is an auto-incrementing ID field on this table. As soon as records are inserted, wouldn't the ID be populated? And then the After Insert trigger should run. But it is almost like Manifold is using Bulk Insert, which as I understand disables triggers.


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

firsttube


1,439 post(s)
#16-Jun-15 17:21

I got another user to run a trace and I am seeing an insert statement for each record I am inserting. This does not explain why the trigger will fire when I insert just one object into the drawing, but when I insert many objects at once through a copy/paste operation in Manifold, the trigger does not fire.

When I insert multiple rows at once from within SQL the trigger fires on each record. When I do the same through Manifold it does not fire. This leads me to believe that Manifold is not inserting multiple records using the same method as when I do it manually.

I noticed in the trace that it looks like Manifold is called "sp_execute" stored procedure.


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

firsttube


1,439 post(s)
#16-Jun-15 19:23

I think I see what is going on here.

From the trace it appears that Manifold does initial insert statements, one for each record. This would cause my rigger to fire once for each inserted record, which is the expected behaviour. Then Manifold issues update statements for each inserted record where it updates the values each of the columns, which in this case, destroys what my trigger updated on the initial inserts.

I noticed that when I initially exported the drawing from Manifold to SQL Server, it created two triggers:

[TableName]_ins

[TableName]_upd

When I disabled the "ins" trigger that Manifold created, and performed the multi-record insert through Manifold, my trigger worked as expected!

The "ins" trigger that Manifold created, sets the value of the Version column to zero when a new record is inserted. I can combine this into my insert trigger so that this requirement is upheld.


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.