Commands & Syntax > Commands > ODBC >

www.perfectkeyboard.com

 

Execute SQL - < odbc_exec_sql >() ... [Pro]

 

ODBC Execute SQL
<odbc_exec_sql>(Database handle,"Execute SQL")
Available in: Professional edition

This command executes an SQL command on a database previosly open using the "ODBC OPEN" command.

 

#

Parameter name

Parameter description

1

Database handle

Database handle. This is the value that was returned from ODBC OPEN command.

2

Execute SQL

SQL command. For example:
UPDATE BookTable SET Count=5 WHERE ID=2

 

Example (Macro Steps):

 

1

<#> <#> This example shows how to execute an SQL command on opened 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

<#> <#> Set John's age to 33

21

ODBC Execute SQL Database handle=%vDbOpen%, Execute SQL=UPDATE TestTable1 SET AGE=33 WHERE FRIEND='John'

22

<#> <#> Close the database

23

ODBC CLOSE Database handle = %vDbOpen%

24

ENDIF

Example (Plain Text):

 

<#> This example shows how to execute an SQL command on opened 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")

  

   <#> Set John's age to 33

   <odbc_exec_sql>(%vDbOpen%,"UPDATE TestTable1 SET AGE=33 WHERE FRIEND='John'")  

 

   <#> Close the database

   <odbc_close>(%vDbOpen%)

<endif>