User Tools

Site Tools


displaying_mysql_data

Direct (read only) access to MySQL databases is available through the AL_SetMySQLSource command.

Associated properties are ALP_MySQLErrorNumber (error code from last MySQL call) and ALP_MySQLErrorMessage (error text from last MySQL call).

See the specific demonstration database that illustrates this feature.

The syntax for AL_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-nullsBoolean (“true” or “1”)
compress-connectionBoolean (“true” or “1”)
automatic-reconnectionBoolean (“true” or “1”)
connect-timeoutLong int
read-timeoutLong int
write-timeoutLong int
asynchronous-queryBoolean (“true” or “1”)
enable-keywordsBoolean (“true” or “1”)
sslBoolean (“true” or “1”)
ssl-verifyBoolean (“true” or “1”)
socket-fileText

The statement parameter is the full SELECT statement including ORDER BY.
The statement is not parsed by AreaList Pro: you must tell the plugin what is the sort order:

$err:=AL_SetObjects ($area;ALP_Object_SortListNS;$arraysSortOrder)

or

AL_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:=AL_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:=AL_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:=AL_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:=AL_GetAreaLongProperty (areaRef;ALP_MySQLErrorNumber)
 $mySQLMsg:=AL_GetAreaTextProperty (areaRef;ALP_MySQLErrorMessage)
 TRACE
End if
displaying_mysql_data.txt · Last modified: 2017/09/27 15:16 by alp_admin