Note that when server names have been switched (databasemanager.switchServer),the
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
With the following code:
var country = 'NL'
var done = plugins.rawSQL.executeSQL("example_data","employees","update employees set country = ?", [country])
if (done)
{
//flush is required when changes are made in db
plugins.rawSQL.flushAllClientsCache("example_data","employees")
}
...
}
So, I guess this is implying that I cant use ‘controller.getServerName()’ in the rawSQL string? I suppose it also suggests I cant use:
var sName = controller.getServerName() - either?
If that is the case how do I get the real name of the server once switched?
I am not sure if controller.getServerName() will show the design or real servername. I suggest you test it out by calling it for and after a switch and output it to console.
Also when it doesn’t show the real servername you can always put the real name in a global variable. I mean you do know what server you switch to when your code does the switch, doesn’t it ?
ROCLASI:
Also when it doesn’t show the real servername you can always put the real name in a global variable. I mean you do know what server you switch to when your code does the switch, doesn’t it ?
Hi Robert - yes I do know the server name and of course I can get it into a variable using a global or even ‘var nServer = controller.getServerName()’ (I think thats still available after a server switch - if not I’m in trouble) but the intimation is that the rawSQL wont take a variable for the server name???
It’s not really clear what:
Note that when server names have been switched (databasemanager.switchServer),the
real server names must be used here, plugins.rawSQL is not transparent to switched servers.
What it means is that unlike the controller the rawSQL plugin doesn’t ‘automagically’ use the correct server after you used switchServer. So you need to pass it the correct (and actual) servername.
So IF controller.getServerName() (I suggest you still test this) does return the actual servername then you can use the following syntax:
plugins.rawSQL.executeSQL(forms.myForm.controller.getServerName(),"employees","update employees set country = ?", [country])
Note that in Servoy 5 controller.getServerName and controller.getTableName is deprecated (but still works).
The syntax is then databaseManager.getDataSourceServerName(controller.getDataSource());
What it means is that unlike the controller the rawSQL plugin doesn’t ‘automagically’ use the correct server after you used switchServer. So you need to pass it the correct (and actual) servername.
So IF controller.getServerName() (I suggest you still test this) does return the actual servername then you can use the following syntax:
plugins.rawSQL.executeSQL(forms.myForm.controller.getServerName(),"employees","update employees set country = ?", [country])
I’ll test that thanks Robert - it would be interesting to hear from Servoy Tech on how they would handle that situation, I’m going to have to back-track over some previous code to make sure this works effectively, so it would be good to know the thoughts behind the design!
Any chance I could get some feedback from Servoy Tech on how this is intended to work??
How should I construct the Server Name to be used in rawSQL in a switchServer environment? Robert has suggested one approach though in at least one situation I wont have a form to check against.
Can you confirm we can use a variable in the rawSQL string for the server name?
I do use a variable and it works fine. The global var is populated at login time as soon as the server is switched.
Remember that you will have to use that variable for ALL your custom sql queries that use currentcontroller.getServerName() (basically the ones that you use in global methods not bound to forms), not only for RawSQL.
ngervasi:
I do use a variable and it works fine. The global var is populated at login time as soon as the server is switched.
Remember that you will have to use that variable for ALL your custom sql queries that use currentcontroller.getServerName() (basically the ones that you use in global methods not bound to forms), not only for RawSQL.
Wow - thats a shocker Nicola - I hadn’t realised that at all!!
Lots of code to re-trace in that case.
So, just to be clear Nicola, if I have a SQL query on a form (form code) I can use standard controller.getServerName and its OK, but code in a global function and it’s not - I then need the global variable?
Sorry Ian,
I stand corrected: currentcontroller.getServerName() works fine even after a switch.
The reason why I use a global var holding the name of the switched connection is that in my framework the main form is based on a server connection that is never switched so using currentcontroller was not an option and that tricked me and led to the mistake, sorry.
Good thing that I double checked it
ngervasi:
Sorry Ian,
I stand corrected: currentcontroller.getServerName() works fine even after a switch.
The reason why I use a global var holding the name of the switched connection is that in my framework the main form is based on a server connection that is never switched so using currentcontroller was not an option and that tricked me and led to the mistake, sorry.
Good thing that I double checked it
Thanks for that Nicola - unfortunately I’m obviously being especially dumb this week (nothing special in that some might say ) but I’m still confused as to what Servoy mean when they say ‘plugins.rawSQL is not transparent to switched servers’?
Because they also say ‘when server names have been switched (databasemanager.switchServer),the real server names must be used here’
Obviously I can only use a construction method (like current /controller.getServerName) or a variable pointing at that construction method (var sName = controller.getServerName etc) so is that acceptable after a switch server?
Sorry to take up your time Nicola - be great if you could clarify that for me - this must trip others up too!
Kahuna:
Because they also say ‘when server names have been switched (databasemanager.switchServer),the real server names must be used here’
Obviously I can only use a construction method (like current /controller.getServerName) or a variable pointing at that construction method (var sName = controller.getServerName etc) so is that acceptable after a switch server?
What they mean is that the rawSQL plugin is completely independent from switched servers, and that is a good thing because you are free to interact with all your servers not only with the active one.
I’ll try to explain my scenario so that you can figure out how thinks can work.
My framework is based on a server connection A, the customer data is using a set of servers connections: B for italy, C for France, D for Russia, E for Argentina.
At login time I check to which country the user that is logging in belongs and I switch the default connection B to the appropriate one: if the user is from the french branch I switch B with C, etc.
After having switched from B to C I save “C” in a global var.
After the switch all the forms that were designed to point to B will be pointing to C so using controller.getServerName() on these forms is safe.
If using global methods though I need to use the var because my main form that loads all the others is based on the framework connection A, not on customer connections B,C,D,E and so currentcontroller.getServerName() would always return ‘A’.
Being able to use rawSQL and getDataSetByQuery against ANY server connection allows me to interact with other countries DBs whenever I want independently from the active server and that is a good plus.
So, let’s get back to your scenario: how/why do you use rawSQL? Can you save the switched server name in a var at switch time as I do? Can you figure out at design time against which database you need to use rawSQL?
ngervasi:
Being able to use rawSQL and getDataSetByQuery against ANY server connection allows me to interact with other countries DBs whenever I want independently from the active server and that is a good plus.
So, let’s get back to your scenario: how/why do you use rawSQL? Can you save the switched server name in a var at switch time as I do? Can you figure out at design time against which database you need to use rawSQL?
Fantastic feedback Nicola - thanks for that clear description. Our model does something similar - where various clients have their own Db and we will switch to theirs on start-up / login (we only use one currently as we are building and testing for the most demanding client initially).
There is no problem placing the new server name in a global variable. I’m using rawSQL in several places as well as getDataSetByQuery.
There is no problem working out the server any of the methods need to work against - my consternation was that there seemed to be no way to place a ‘real’ server name against the method string - only a variable pointing to that real name.
From your explanation it seems all I need do is use the global variable in place of the server name on any global method where serverName is required!
my consternation was that there seemed to be no way to place a ‘real’ server name against the method string - only a variable pointing to that real name.
You can also use a string for the server name so if you already know that you want rawSQL to operate against connection “A” you just use the string “A” as the server name regardless of how servers have been switched at startup:
plugins.rawSQL.executeSQL('A',"employees","update employees set country = ?", [country])
the above will operate against the A db even if “A” has been switched to “B” at startup.
The var is only needed when you want to use rawSQL against the ACTIVE connection and obviously you cannot know in advance which one will be at runtime.
You can use both. It’s just that the plugin itself doesn’t know of any switched server INTERNALLY.
So whatever you pass it it uses as truth. May it be a server name as a string, variable or from a function.
As long as controller.getServerName() returns the connection name you need you’re fine, if the db has been switched from A to B and the form is based on A it will return B.