How Do I Send My Own SQL Statements to the Database? (Magic xpa 4.x)
When you use Magic xpa with an SQL database, Magic xpa generates the SQL code needed. However, there are times when you may want to override the default code generation. For instance, you may want to call a stored procedure or tightly control how a group of records is retrieved.
This is easily done from within a Magic xpa program. There are several different ways you can send SQL statements, each of which is covered below.
If you only want to send a WHERE clause, you can do this in the Range/Locate dialog box. You can type in your Where statement in the DB SQL field, and you will see the result concatenated onto Magic xpa’s Where clause in the Full Where Clause field.
-
Go to Task->Range/Locate.
-
Click on the SQL Where tab header.
-
Enter the Where clause you want, either by typing it in the DB SQL field, or by zooming on the Exp field and entering an Expression.
-
The full where clause will be displayed below.
Note: This option is only valid when you are using Physical transactions (Task Properties->Data->Transaction Mode = Physical).
If you want to enter something more complex than the WHERE statement, you can do this using a Direct SQL statement.
-
Go to the Data View tab.
-
From line 1, select Direct SQL, then tab.
-
The SQL Command dialog box will appear. Here you can type in an SQL command. Clicking on the Assist button will bring up a dialog box that will help you format the SQL command.
-
Zoom from the Output Arguments field to map the fields from the SQL command onto virtuals in the task.
The Virtuals can be used in your program just as you would use a Real from a Main data source.
The Online and Rich Client Samples projects (program SQ01 and RSQ01)
