sqlQuery

sqlQuery ( string driver , string urlOfConnection , string login , string password , string _sqlQuery , string|default:execute _returnType ) : string

Allows to execute something in a distant database. This function can return a result.

Table of correspondance driver/url

RDBMS JDBC driver name URL format Default port
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/databaseName
For UTF8: jdbc:mysql://hostname/databaseName?useUnicode=true&characterEncoding=UTF-8 		3306
ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port_Number:databaseName 1521
DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port_Number/databaseName 50000
Sybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname:port_Number/databaseName 2638/5020/3638/5200
MangoDB mongodb mongodb://hostname/databaseName
Be careful, MangoDB works differently from other systems. With MangoDB the arguments _returnType and sqlQuery are useless. The function will return a MongoDatabase object after identification on the database. You can then use pointers on this object to insert data or to consult them. Ex: sqlQuery("mangodb", ...).getCollection("sampleCollection"). Check these Java examples. 		27017/27018/27019


Debug

To use a remote connection on MySQL you need to :
- comment bind_address=xxxx in /etc/mysql/mysql.conf.d/mysqld.cnf (or associated files) and restart mysql
- Execute in SQL GRANT ALL PRIVILEGES ON myTable.* TO 'myUser'@'%' IDENTIFIED BY 'myPassword' WITH GRANT OPTION
- Does not use special caracters in the password


See also

sqlEscape
sql

Example


 
        // Execute a simple query to retrieve data 
        sqlQuery("org.gjt.mm.mysql.Driver", "jdbc:mysql://localhost/myBase", "myUser", "myPassword", "SELECT * FROM myTable", "set");
        
 
        // Insert new data and retrieve the generated ID 
        sqlQuery("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/myBase", "myUser", "myPassword", "INSERT INTO myTable (name, age) VALUES ('John', 30)", "insert")
        
 
        // Updating existing data 
        sqlQuery("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@localhost:1521:myBase", "myUser", "myPassword", "UPDATE myTable SET age = 31 WHERE name = 'John'", "execute")
        
 
        // Deleting data 
        sqlQuery("COM.ibm.db2.jdbc.net.DB2Driver", "jdbc:db2:localhost:50000/myBase", "myUser", "myPassword", "DELETE FROM myTable WHERE name = 'John'", "execute")
        
 
        // Recovery of a single value 
        sqlQuery("com.sybase.jdbc.SybDriver", "jdbc:sybase:Tds:localhost:5020/myBase", "myUser", "myPassword", "SELECT COUNT(*) FROM myTable", "value")
        
 
        // Using MongoDB 
        sqlQuery("mongodb", "mongodb://localhost/myDatabase", "myUser", "myPassword", null, null).getCollection("sampleCollection").find()

Parameters

driver

Defines your database technology. Examples of driver values :
• com.microsoft.sqlserver.jdbc.SQLServerDriver : Microsoft SQL
• org.gjt.mm.mysql.Driver : MySQL

urlOfConnection

Examples of url values :
• jdbc:odbc:myBase;CacheSize=30;ExtensionCase=LOWER
• jdbc:mysql://localhost/myBase
• jdbc:oracle:oci8@:myBase
• jdbc:oracle:thin:@localhost:8000:myBase
• jdbc:sybase:Tds:localhost:5020/myBase

You can add some arguments to your link, ex : jdbc:mysql://toto.mysql.database.azure.com:3306/my_database?useSSL=true&requireSSL=false&allowMultiQueries=true

login

password

_sqlQuery (optional)

Your SQL query

_returnType (optional)

• set : return all rows of a query (array of maps. ex : [["name":"Dupont","age":"15"],["name":"Smith","age":"28"]] ). Note: this option is required for "CHECK TABLE" and "OPTIMIZE TABLE"
• row : return just a row (map. ex : ["name":"Dupont","age":"15"] )
• value : return a value (ex: "Dupont")
• execute : return null. Necessary for delete, update and insert
• insert : return id of the created entry. For INSERT INTO request