Native SQL integration¶
How to run T-SQL from MVsharp and work with native SQL Server tables from TCL and BASIC.
Introduction¶
MVsharp is tightly integrated with SQL Server, allowing you to execute T-SQL statements directly from MVsharp TCL and from BASIC.
Additionally, native SQL tables can be accessed from TCL as if they were MVsharp files.
MVsharp BASIC has also been enhanced to allow you to access and update data in native SQL tables.
Multi valued data can also be exposed as normalized SQL tables by creating views on the multi valued data. These views can also be updated to change multi valued data from T-SQL. See MVsync data normalization.
The SQL command prompt¶
To enter the SQL command prompt, type SQL at TCL and press Enter:
You can then type the T-SQL across multiple lines. When you press Enter on a blank line, the T-SQL statement is executed.
>SQL
SQL: Create Table SqlCust (
SQL: Id int Primary Key,
SQL: FirstName varchar(25),
SQL: LastName varchar(25),
SQL: Dob DateTime,
SQL: Age int,
SQL: Department varchar(10),
SQL: )
SQL:
In the example above, a new SQL table SqlCust is created. If there are any errors, they are displayed on the screen.
You can also insert data from the SQL prompt:
>SQL
SQL: Insert Into SqlCust Values(1,'Grant','Hart','1963-11-19',61,'Fin')
SQL: Insert Into SqlCust Values(2,'Portia','Hart','1961-09-13',61,'Fin')
SQL: Insert Into SqlCust Values(3,'Ryan','Hart','1985-12-05',38,'Sal')
SQL:
Creating a SqlNative file pointer¶
To access a native SQL table, a file pointer needs to be created in the VOC. MVsharp requires that the table has a primary key. The format is:
For the table created above:
Here Id is the primary key of the table. The following VOC entry is created:
>CT VOC SqlCust
SqlCust
0001 File
0002 SqlCust
0003 SqlCust
0004 SqlNative
0005 MVsharp
0006 Id
Note
The CREATE.FILE statement does not create a SQL table. It only creates a pointer in the VOC that allows access to the SQL table from MVsharp TCL and BASIC.
The virtual dictionary¶
MVsharp automatically creates a dictionary definition based on the metadata of the SQL table. These dictionary items can be used like normal A, S, D or I types in any MVsharp query statement, including BREAK-ON and TOTAL. The SQL table can be treated like any MVsharp file.
The SQL table created in the previous example displays the following:
>LIST DICT SqlCust
list dict SqlCust DICTLIST 12:33:37 07-01-25 Page 1
SqlCust... Type.. Definition.......... Conversion Heading........ Format Depth
Id Q 1 int Id 12R S
FirstName Q 2 varchar FirstName 25L S
LastName Q 3 varchar LastName 25L S
Dob Q 4 datetime Dob 12R S
Age Q 5 int Age 12R S
Department Q 6 varchar Department 10L S
6 records listed.
The power of the MVsharp query language can now be used on SQL tables:
>SORT SqlCust FirstName LastName Department Total Age
SORT SqlCust FirstName LastName Department T 12:49:24 07-01-25 Page 1
SqlCust... FirstName........... LastName............ Department Age.........
1 Grant Hart Fin 61
2 Portia Hart Fin 61
3 Ryan Hart Sal 38
============
160
3 records listed.
>SORT SqlCust FirstName LastName Break-On Department Total Age
SORT SqlCust FirstName LastName Break-On Dep 12:52:21 07-01-25 Page 1
SqlCust... FirstName........... LastName............ Department Age.........
1 Grant Hart Fin 61
2 Portia Hart Fin 61
*** 122
3 Ryan Hart Sal 38
*** 38
============
160
3 records listed.
Using SqlNative tables in BASIC¶
Data in an MVsharp file is accessed by its ordinal position, whereas data in a SQL table is accessed by its column name. The dynamic array has been enhanced so that a record read from a SqlNative table can be accessed by column name.
Accessing MVsharp data in BASIC:
Open "Cust" To CustFile Else Stop 201,"Cust"
Read CustRec From CustFile , "1" Then
Crt "Name :":CustRec<1>
End
Accessing a SqlNative table in BASIC:
Open "SqlCust" To SqlCustFile Else Stop 201,"SqlCust"
Read SqlCustRec From SqlCustFile , "1" Then
Crt "Sql Name :":SqlCustRec<"FirstName">
End
These BASIC enhancements allow you to access, update and select SqlNative tables using the same syntax you use for MVsharp files.