I have a client that needs a website that gets data from an external software. The data can be up to 1-day old. Is there any way to have the data from the external database synced every night to a Wix collection (so that the website itself only “talks” with an internal Wix Collection, but that wix collection is updated automatically from the external software)?
The external software stores its data on a Pervasive DB. Ideally, the sync would be done directly from there. The data is also synced to MSSQL daily, so the sync could also be done from the MSSQL DB.
1 Like
Hi tal,
you can use wix code http functions as a way to expose an api in which you can receive the data to be synched, wither on every change event or in a batch of the delta that was changed since last execution.
good luck,
Shlomi
Hi Shlomi, thanks for the response.
Let me see if I understand correctly:
What you’re saying is that I need to make the function that queries the external DB and updates the Wix collection part of a site’s HTTP functions, and then have a scheduled task outside of Wix that calls that HTTP function?
this is one option (in the future we will also offer ability to run tasks which we currently do not have)
another one is push the data from another system towards your wix new api you create.
but basically yes
Hey. I didn’t understand that second option. What do you mean “push the data toward the wix api”?
Hi Tal,
depends on the volume of data, you might want to optimize the process to only send the delta of the changes from the mssql db to Wix, rather than asking for all of the data every day.
such delta can also be sent upon changes and not just in a batch every interval. streaming rather than batching
good luck!
Shlomi
I think I understand now. Anyway the MSSQL is only updated in batches. The actual changes are done on a pervasive DB, which is synced every night to the MSSQL. The person who is in charge of the Pervasive DB doesn’t really know how to set up REST API for it, so I guess Wix will not be able to get the info from it, would it? Meaning that streaming would probably be pointless, right?
(or maybe I’m totally wrong, and the Pervasive DB can call the API function on my site - thus streaming the data directly, without the need for MSSQL and without the need to set up REST API on the Pervasive DB?)
The data must have some kind of API to make it accessible at all. If no API is available you can’t get the data. In mssql you might be able to create a REST API (http://blog.dreamfactory.com/add-a-rest-api-to-any-sql-db-in-minutes) but in Pervasive I have no idea. The best would be to create some local script on the pervasive machine that takes the latest data, makes a JSON file of it and drops it on a public drive accessible from the web. Or maybe a Dropbox folder, then you can connect to it.
When the JSON file is online you can download it using Wix Code and fetch and sync the Data Collections.
Tal, i’d check on the second option you thought of.
Some dba allow you to setup a trigger to invoke an external http request
Alright. I’ll check it next week. Thanks!
A semi-related question: can two wix websites access the same wix DB, or do I have to make a copy of the DB for each website?
Hi Tal,
The db is per website and isn’t shared, interesting as we didn’t come across such a request before 
Can you please share some more about the application you are building? Sounds very interesting
Shlomi
I’m actually not building it yet - waiting for the client to give me their OK, but essentially it’s a project that will involve a few different websites, each meeting different needs with different functions, but all are based on the same data (some only use a subset of the data). When it does happen - I’ll post the websites here.
Real shame that DBs can’t be shared. Such a waste of storage and API calls…
Hi Shlomi,
After talking to the client, it is clear to us that the right solution here would be to have the data pushed to the Wix DB every night from the MSSQL. Could you please point me to info on how to do that? What are the steps that need to be taken here?
Please see my recommendation above from Jan 24 regarding the http function
Yes. I remember that recommendation. What I mean now is:
- How do I call the HTTP function and where do I call it from?
- How do I pass the new data to that function?
(I’ll probably have more questions later on, but this can at least get me started)
Hi Tal,
please see a link again to the details:
wix-http-functions - Velo API Reference - Wix.com - also contains some example. we currently support HTTP GET and POST
it will expose a REST API to Your site and will invoke a serverless environment (node.js) your site backend have, try it out it is really cool 
you can invoke it from anywhere in the web and implement the internal code to suit your need. you can access your db which is available for your site.
please let me know if you’ll need further assistance,
Shlomi
Maybe I don’t know enough about HTTP GET and POST. I’ll read a bit about them, and try to see if then it makes more sense to me.
(That is, I do get how to pass a small number of parameters to my HTTP function, just as in the example - but I still have no idea how to pass something like all the changes that happened throughout the day - it could be a not-so-small amount of data + I can’t know in advance how many changes there will be).
Tal, use the http request post body https://www.wix.com/code/reference/wix-http-functions.WixHttpFunctionRequest.html#body and split to multiple requests if needed.
you can always use postman plugin to test your code
Shlomi
Alright.
I’ll do some more reading and get back to you with more questions, if I have them.
Thanks!