Direct (read only) access to MySQL databases is available through the PL_SetMySQLSource command.
Associated properties are PLP_MySQLErrorNumber (error code from last MySQL call) and PLP_MySQLErrorMessage (error text from last MySQL call).
See the specific demonstration database that illustrates this feature.
The syntax for PL_SetMySQLSource is (areaRef:L; host:T; user:T; password:T; schema:T; options:T; port:L; statement:T; parameter1:Z; parameter2:Z; … parameter17:Z) → error:L
The option parameter is a list of desired options passed as 'option1=value option2=value'.
If the option can contain spaces, enclose it in double-quotes. Space character ends the option parsing.
Possible options are:
| allow-nulls | Boolean (“true” or “1”) |
|---|---|
| compress-connection | Boolean (“true” or “1”) |
| automatic-reconnection | Boolean (“true” or “1”) |
| connect-timeout | Long int |
| read-timeout | Long int |
| write-timeout | Long int |
| asynchronous-query | Boolean (“true” or “1”) |
| enable-keywords | Boolean (“true” or “1”) |
| ssl | Boolean (“true” or “1”) |
| ssl-verify | Boolean (“true” or “1”) |
| socket-file | Text |
The statement parameter is the full SELECT statement including ORDER BY.
The statement is not parsed by PrintList Pro: you must tell the plugin what is the sort order:
$err:=PL_SetObjects ($area;ALP_Object_SortListNS;$arraysSortOrder)
or
PL_SetAreaTextProperty ($area;ALP_Area_SortListNS;$sortList)
The statement can contain parameters using question mark (in form 'WHERE tbl.fld = ?'), provide the parameter values using pointers.
- when the query is successfully executed, columns from the query are created, headers are set
- automatic-reconnection and enable-keywords defaults to true, all other Boolean flags defaults to false
- connect-timeout defaults to 5
- read-timeout & write-timeout default to zero (no timeout)
Up to 17 parameters are allowed.
In case several parameters are used, each one of them is referred as a question mark, in the parameter order (see “simple query with two parameters” example below).
// simple asynchronous query $err:=PL_SetMySQLSource (areaRef;"localhost";"root";"";"";"asynchronous-query=true allow-nulls=0";0;\ "SELECT * from MySQLdb ORDER BY c1")
// simple query with parameter $date:=!00/00/00! $err:=PL_SetMySQLSource (areaRef;"localhost";"root";"";"";"";0;\ "SELECT * from MySQLdb WHERE c3 <> ? ORDER BY c1";->$date)
// simple query with two parameters $date1:=!00/00/00! $date2:=!24/08/2017! $err:=PL_SetMySQLSource (areaRef;"localhost";"root";"";"";"";0;\ "SELECT * from MySQLdb WHERE c3 > ? AND c3 < ? ORDER BY c1";->$date1;->$date2)
// then test the result If ($err#0) $mySQLErr:=PL_GetAreaLongProperty (areaRef;PLP_MySQLErrorNumber) $mySQLMsg:=PL_GetAreaTextProperty (areaRef;PLP_MySQLErrorMessage) TRACE End if
Page Tools
