Is there a way to do a SQLstringconnect in Servoy. I need to connect to a remote database using a dsnless connection to a database that is not always available.
The connection needs to be held for the shortest time possible. It therefor esuits best to cretae the connection, verify it’s made, get or write the data, collect any returned messages, disconnect. All this is very easy in FoxPro, so hope to do equivalent.
Basic FoxPro code example is:
local lcConn, lcConStr
lcConStr = “DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;UID=root;PWD=Mark:0530;DATABASE=Myfix”
Connect to it and get a handle
lnConn = SQLSTRINGCONNECT(lcConStr)
select the database
? SQLEXEC(lnConn,“Select * from mytable”,‘tmptable’)
Using the maintenance plugin you can check whether a server is valid.
A server becomes invalid of an error occurs when connecting.
There is currently no way for a server to go valid again once invalidated.
Appreciate your response. Please could you supply a little more info.
I am really surprised this is so difficult.
Is this possible to request as a new feature or is there a problem with the basic design\architecture that makes this impossible?
I would guess many others would need this feature. Has no one requested it before?
For me this is a real show stopper as I believe that collecting and sharing data across remote databases is basic to many business processes. Eg collecting readings from logging tables attached to all sorts of devices. In my case collection electricity meter readings from many remote sites capturing multiple meters.
For me this is a real show stopper. I do need to find a solution.
Thought of creating a FoxPro com object using a plugin to return the data to an array.
I have had a look at the Com Plugin at ServoyForge. - Does not appear to work on 64 bit windows. Project seems to be in limbo. I’ve tried to contact Scott Butler, but had no response.
Any comments of ideas and support would be much appreciated.
Thanks Mark
We do also, query, remote databases (owned by ourselfs), but NEVER directly via SQL (SQL port have to be open, security conflicts, etc…etc…)
What we mostly do, create a small php script, or use restfull webservices, that returns the result in JSON or XML
Servoy is a 3-tier architecture (client-server-database). What you want to do is essentially a 2-tier architecture (client-database).
Doing that in a 3-tier architecture would mean adding connections in Server specifically for your client session.
Harjo. Unfortunately all you mention is way out of area of knowledge, so presume quite a learning curve. Databases I do understand, and setting up security for specific ip addresses, user etc etc I understand to be secure enough. One day maybe I can learn more about how you do this.
Rob. I don’t understand the Servoy architecture well enough yet, but as it’s fairly much an industry standard, hopefully one of the Servoy guru’s can figure out how to do it.
you could try contact Marcel of IT2BE (www.it2be.com)
If I remember right, he had some kind of plugin (not public) where you could query remote databases…
not sure, but you can try…
There was and is (although I must admit that I did not look at it for a long time) the ‘query’ method in the Data Plug-in that makes it possible to do such a thing without the need to use a form.
Currently you can send a sql command to the database and import data into the database but there is no method to retrieve data yet.
The query method makes use of a Server in the Servoy properties but it can be extended to work with your own properties or file if you need more flexibility.
Obviously I can add a method to retrieve data as well.
Another option: if you can SSH into your remote servers and the databases are scriptable, you can use application.executeProgram() to run remote sql queries. Using MySQL as an example:
1- Directly running a mysql query:
var results = application.executeProgram(
"/usr/local/mysql/bin/mysql", // connection details
"-u",
"root", // user name
"cms_linds_sutra_cms", // database to use
"-sN", // suppress column names and pseudo graphic characters
"-e", // run sql statement
"select id_session from web_session;")
2- Running a shell script:
var results = application.executeProgram("/Users/admin/Desktop/test2.sh")
Also if you really wanted to connect over jdbc you could just import java.sql into a javascript and hard code your way to it: just like your foxpro example does.
Jan Aleman:
Also if you really wanted to connect over jdbc you could just import java.sql into a javascript and hard code your way to it: just like your foxpro example does.
One would think. I’ve never been able to figure out how to load the JDBC driver though:
function JDBC_connect() {
importPackage(java.sql)
try {
java.lang.Class.forName("com.mysql.jdbc.Driver") // throws "JavaException: java.lang.ClassNotFoundException"
var url = "jdbc:mysql://localhost/cms_linds_sutra_cms"
var conn = DriverManager.getConnection(url,"user1","password")
}
catch (e) {
application.output(e)
}
}
We expose many of our Foxpro functions as webservices and call them from Servoy through the http plugin. You can pretty much do anything this way regardless of the platform Servoy is running on.
Jeroen de Vries:
We expose many of our Foxpro functions as webservices and call them from Servoy through the http plugin. You can pretty much do anything this way regardless of the platform Servoy is running on.
Hi Jeroen. I’m very interesred on this. Could you please post any little sammple?
Jan Aleman:
Also if you really wanted to connect over jdbc you could just import java.sql into a javascript and hard code your way to it: just like your foxpro example does.
One would think. I’ve never been able to figure out how to load the JDBC driver though:
if you really want to do that, first make sure that a plugin shippes the driver as a dependency jar.
then that should work fine and inside javascript like that or in a plugin you should be able to connect from a client to any db that is accessible from the client