Bulk API usage

[cbsteven]

I've been playing around with the API more and more for querying the database. Does anybody out there use it extensively for writing to the database?
I currently have a custom solution set up to handle my thousands of daily product updates and I was looking at the API to see if it could do the same job. At first glance I don't think so - the normal API doesn't have any methods for writing/creating products. Maybe with the advanced API, but it seems... dangerous for much more than simple queries.

[Evolution]

I had inquired to support about a year ago because I wanted to use the API to delete/add products daily based on a feed and they said that it couldn't be done because of the way the API was designed.

Perhaps they have made improvements in that time. Have you checked with them? I'd be interested in hearing the latest news if you do.

[cbsteven]

How many products are you talking about? The Advanced API allows direct database manipulation with SQL statements, so in theory you can just create a new record in the PRODUCTS table. I am not sure it is a good idea to use it for this in large scale operations, though.

[jleclair]

we have plans to implement this type of thing you are talking about in the near future for automatic catalog pushes when an item is created in our inventory managment program. sql is sql when you get right down to it, and there's no reason you cant use the advanced api for what you are thinking of doing. In fact, that's why it was exposed the way it was exposed. Be sure to download a local copy of your db and test thoroughly before sending data to your real db.

[cbsteven]

jleclair - The reason I am hesitant to recommend to Advanced API this way is that the back end is a MS Access database. Not exactly the most robust database type. Sure, you could create an SQL statement that creates the right records, but I am not sure how well Access handles things like multiple simultaneous database writes.

[jleclair]

stone edge non-enterprise is built on access also, its not the fastest thing in the world but it can still handle what you want to throw at it. we used that for 3 years without issue. i would expect the same out of 3dcart's api. now, 100 of you throwing the same thing at the same time is probably a different story.

i don't know how 3dcart's backend works exactly, but i'm willing to bet their import tool takes a similar if not identical approach to inserting your data anyway. theres only so many ways to insert records.

it sounds like you have more doubts about access itself then the api. i would research some docs on access itself and check its limitations against the actions you wish to perform

[cbsteven]

I've been playing with this a little bit more trying to efficiently update one field in all of my 30,000 products. To update 1,000 products via the API takes about 5 minutes. That's doing one UPDATE call per product. It could probably be made a little more efficient by updating more than one products per API call, but at some point the call will just get too large.

The thing is that "SQL is SQL" isn't really true. If you were accessing the database directly you could do things like a Prepared Statement which would vastly improve the efficiency. There's no way to do something like that with the API.

[MaxCPR]

I've been playing with this a little bit more trying to efficiently update one field in all of my 30,000 products. To update 1,000 products via the API takes about 5 minutes. That's doing one UPDATE call per product. It could probably be made a little more efficient by updating more than one products per API call, but at some point the call will just get too large.

The thing is that "SQL is SQL" isn't really true. If you were accessing the database directly you could do things like a Prepared Statement which would vastly improve the efficiency. There's no way to do something like that with the API.

I, too, am hesitant to use the advanced API. Because of that, I just use my own app to create an import list. This way I can view it before it goes in, and I know it will work. I do this for tracking imports and product updates. It adds an extra step, but it still saves time!

[lukiegames]

cbsteven,

I'm not sure if you are still on an old backend or something but ours is on MSSQL, not Access. I think they replaced Access quite a while ago, or maybe it's just for certain plans. Ours is 2008R2. If your backend isn't actually SQL you might want to contact tech support and see if they can upgrade you.


Jesse

[cbsteven]

Yeah, MSSQL is only on some plans.

Plus, there is still the fact that the Advanced API doesn't let you use techniques you would normally use to do efficient SQL queries, like Prepared Statements.