.png)
Select SQL - < odbc_select >() ... [Pro]
|
ODBC Select SQL
<odbc_select>(Database handle,"Select SQL",Variable for result)
Available in: Professional edition
|
|
This command executes an SQL select command on a database previosly open using the "ODBC OPEN" command. The selected data set then can be retrived using ODBC Select GET command and enumerated using ODBC Select NEXT command.
|
|
|
#
|
Parameter name
|
Parameter description
|
|
1
|
Database handle
|
Database handle. This is the value that was returned from ODBC OPEN command.
|
|
2
|
Select SQL
|
Select SQL command. For example:
UPDATE BookTable SET Count=5 WHERE ID=2
|
|
3
|
Variable for result
|
Variable that receives identifier of the selected data set. This value is used in the ODBC Select GET and ODBC Select NEXT commands. The value is non-zero if the data set is not empty.
|
|
|
|
|
Example (Macro Steps):
|
|
1
|
<#> <#> This example shows how to get values from the selected data set of the Microsoft Access database
|
|
2
|
Macro execution: ONLY COMMANDS
|
|
3
|
<#> <#> Show a form that lets a user to select the database file
|
|
4
|
Form FIELD "Microsoft Access database:" of type "File path" (Default value=*.accdb, Variable to save field value=vDbFile, Form identifier=f1)
|
|
5
|
Form OPEN "f1", Window title="Select Microsoft Access database to open"
|
|
6
|
IF %_vCanceled%==1
|
|
7
|
<#> <#> User canceled the selection form, no database file is selected
|
|
8
|
Macro EXIT
|
|
9
|
ENDIF
|
|
10
|
IF FILE "%vDbFile%" Not Exist ()
|
|
11
|
<#> <#> User entered a file that does not exist
|
|
12
|
Message SHOW "Error" : "The file '%vDbFile%' was not found." (other parameters: x = -100, y = -100, Window title = , Buttons = OK, Timeout (seconds) = 0, Always on top = No).
|
|
13
|
Macro EXIT
|
|
14
|
ENDIF
|
|
15
|
<#> <#> Build the Microsoft Access connection string
|
|
16
|
Variable SET "vConnectionString=Driver={MICROSOFT ACCESS DRIVER (*.mdb, *.accdb)}; Dbq=%vDbFile%;", Message text=""
|
|
17
|
<#> <#> Open the database
|
|
18
|
ODBC OPEN Connection string=%vConnectionString%, Variable for result=vDbOpen
|
|
19
|
IF %vDbOpen%!=0
|
|
20
|
<#> <#> Select whole table
|
|
21
|
ODBC Select SQL (Database handle = "%vDbOpen%", Select SQL = "SELECT * FROM TestTable1 ", Variable for result = "vDataSet")
|
|
22
|
<#> <#> Cycle in the data set
|
|
23
|
Repeat steps UNTIL "%vDataSet%!=0" (Counter variable initial value = "", Counter loop increment = "")
|
|
24
|
<#> <#> Get data from the record
|
|
25
|
ODBC Select GET Database handle = "%vDbOpen%", Select handle = "%vDataSet%", Field name = "FRIEND", Variable for result = "vFriend"
|
|
26
|
ODBC Select GET Database handle = "%vDbOpen%", Select handle = "%vDataSet%", Field name = "AGE", Variable for result = "vAge"
|
|
27
|
Message SHOW "Information" : "Friend=%vFriend% Age=%vAge%" (other parameters: x = -100, y = -100, Window title = , Buttons = OK, Timeout (seconds) = 0, Always on top = No).
|
|
28
|
<#> <#> Move to next record
|
|
29
|
ODBC Select NEXT Database handle=%vDbOpen%, Select handle=vDataSet
|
|
30
|
Repeat steps END
|
|
31
|
<#> <#> Close the database
|
|
32
|
ODBC CLOSE Database handle = %vDbOpen%
|
|
33
|
ENDIF
|
|
Example (Plain Text):
<#> This example shows how to get values from the selected data set of the Microsoft Access database
<cmds>
<#> Show a form that lets a user to select the database file
<form_item>("f1","Microsoft Access database:","EDIT_FILE","*.accdb","vDbFile",1)
<form_show>("f1","Select Microsoft Access database to open","",0,500,0,,,1,1)
<if>("%_vCanceled%==1")
<#> User canceled the selection form, no database file is selected
<exitmacro>
<endif>
<if_file>("%vDbFile%","NOTEXIST","")
<#> User entered a file that does not exist
<msg>(-100,-100,"The file '%vDbFile%' was not found.","",1,0,2,0)
<exitmacro>
<endif>
<#> Build the Microsoft Access connection string
<varset>("vConnectionString=Driver={MICROSOFT ACCESS DRIVER (*.mdb, *.accdb)}; Dbq=%vDbFile%;","")
<#> Open the database
<odbc_open>("%vConnectionString%",vDbOpen)
<if>("%vDbOpen%!=0")
<#> Select whole table
<odbc_select>(%vDbOpen%,"SELECT * FROM TestTable1 ",vDataSet)<#>
<#> Cycle in the data set
<for>("","%vDataSet%!=0","")
<#> Get data from the record
<odbc_select_get>(%vDbOpen%,%vDataSet%,"FRIEND",vFriend)
<odbc_select_get>(%vDbOpen%,%vDataSet%,"AGE",vAge)
<msg>(-100,-100,"Friend=%vFriend%
Age=%vAge%","",1,0,0,0)
<#> Move to next record
<odbc_select_next>(%vDbOpen%,vDataSet)
<for_end>
<#> Close the database
<odbc_close>(%vDbOpen%)
<endif>