Learning Xbasic - Using Xbasic with SQL Tables

To follow the examples in this tutorial, create a named connection called "AlphaSportsXbasic". The connection type is 'Access' and the target .MDB file for the connection is the sample Alphasports.MDB file that is installed in the MDBFiles folder under your Alpha Five installation.

You can see this document at:

http://www.downloads.alphasoftware.com/Tutorials/UsingXbasicWithSQLTables.html

The videos for this training course are at:

Video - Part 1
Video - Part 2
Video - Part 3
Video - Part 4
Video - Part 5
Video - Part 6
Video - Part 7
Video - Part 8
Video - Part 9
Video - Part 10
Video - Part 11
Video - Part 12
Video - Part 13
Video - Part 14

 

Working with SQL Tables using Xbasic

Powerful ways to read and update SQL data from within Alpha Five

Xbasic provides powerful commands to work with data in SQL tables.

Active Link Tables vs. Direct SQL Commands

Two ways to work with data in SQL.

Active-link tables are an indirect way of working with SQL  data. An active-link table is really a native Alpha Five .dbf table that gets populated on the fly with data from the SQL table. When you make any edits to the active-link table, the edits are really being made to the temporary local .dbf table, and then behind the scenes, Alpha Five translates the changes that you made into SQL commands and executes those commands on the SQL table.

Direct SQL allows you to pass SQL commands directly to the SQL database.

Pros and Cons of Active-Link Tables

Can use your existing knowledge of using Xbasic to manipulate native tables

Updating an active-link table is identical to a native Alpha Five table.

Don't have to know any SQL!

dim tbl as p
tbl = table.open("customer")
tbl.change_begin()
tbl.firstname = "Fred"
tbl.change_end(.t.)
tbl.close()

Can be slow compared to direct SQL

Advantage of Direct SQL Commands

Very fast and exposes all the power of the SQL backend

AlphaDAO

A set of Xbasic classes that enable you to work with SQL Databases

Contrast with ADO

ADO is Microsoft technology. It stands for ActiveX Data Objects and you can use ADO in Xbasic to work with SQL databases, just as you can use AlphaDAO. However, AlphaDAO is significantly easier to work with, and to understand, than ADO.

The AlphaDAO Objects - (the ones you need to know about)

Connection Object

The connection object allows you to connect to a SQL database and then, once you are connected, to send commands to the SQL database. The 'commands' that you send to the SQL database are typically SQL (Structured Query Language) commands.

ResultSet Object

The ResultSet object contains the data that is retrieved from the SQL database after you execute a command that selects data.

Arguments Object

The arguments object is used to pass values to SQL commands. An argument is like a variable. You can use arguments in SQL commands rather than explicit values. For example:

Select * from customer where bill_state_region = 'MA'

Select * from customer where bill_state_region = :whatState

You should always use arguments when constructing SQL commands in Xbasic.

The Connection Object

Dimming a Connection Object

Dim cn as sql::connection
 

Opening a Connection

flag = cn.open("::Name::AlphasportsXbasic")

Executing a Command

dim sqlCommand as c
sqlCommand = "Select * from customer"
flag = cn.execute(sqlCommand)
?flag
= .T.
 

Checking for errors - CallResult.Text

You can always see if a SQL command executed successfully by checking the connection object's callResult property

?cn.callResult.text
= "Success"

'Now, make an intentional error - there is no table called MyCustomers
dim sqlCommand as c
sqlCommand = "Select * from mycustomer"
flag = cn.execute(sqlCommand)
?flag
= .f.
?cn.callResult.text
= Database API specific error

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

208 - '[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'mycustomers'.
SQL State is: 42S02'

Connecting to a Database

Connection string

The connection string tells the connection object how to connect to the SQL database. The connection string has information about the database type you are connecting to, the address of the SQL database, the name of the database you want to use, the user name, the password etc.

Here is an example connection string:

{A5API='SQLServer',A5Syntax='SQLServer',A5DBVersion='2008',A5ANSINullPadWarn='Default',
Database='AlphaSports',Server='selwyndell\sqlexpress',Trusted_connection='yes'}

To build a connection string, you use the built-in Connection String builder.

You can give a connection string a name so that it can be easily reused.

To manage your connection strings, go to Tools, External Databases, AlphaDAO Connection Strings

Notice the Trace SQL checkbox. It allows you to turn on SQL tracing so that you can go to the Trace Window and see all of the SQL commands that get executed.

Named connections vs. ad-hoc connections

An ad-hoc connection is an explicit connection string - not a named connection.

A named connection is a connection string that has a name.

You should always try to use named connection strings when developing applications. Then if you have to change the location or type of the SQL database after you have built the application, you just change the definition of the named connection string that you are using and then your entire applications points to a new database.

Checking for connection errors

Every time you execute a SQL you should always check to see if the command was successful.

Executing a Select Command

Simple Select

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select * from customer where bill_state_region = 'MA'"
flag = cn.execute(sqlCommand)
?flag
= .t.

Note that literal stings in SQL are single quoted, not double quoted!

 

Using Arguments

In the next slide we will use an argument instead of hard coding the where clause value into the SQL statements

Using Arguments in a Select Command

Dimming a SQL::Arguments object

Before you can use the Arguments object, you have to dim a variable as an Argument object.

Dim args as SQL::Arguments

Setting Argument Values

Once you have an Arguments object, you can call its methods to set argument values. The .Add() and .SetNull() methods of the Arguments object are used to set the value of arguments. .SetNull() is a special method to create arguments that have a value of NULL.

'create an argument called 'WhatState' and give it a value of 'MA'
args.add("WhatState","MA")
 

'Create a character argument called CompanyName and give it a value of NULL
args.SetNull("CompanyName","C")
'Create a numeric, NULL valued argument
args.SetNull("Salary","N")
 

Arguments have a data type. The standard Xbasic data types are supported. These are character, date, time, numeric, logical. To create an argument of a particular type, you just set its value to a value of that type. For example:

args.add("whatTime",now())
args.add("whatNumber",23)
args.add("whatDate",date())
args.add("isMarried",.t.)

Passing in the Arguments object to the .Execute() Method

Note how you can pass in the arguments object to the .execute() method as the second argument.

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select * from customer where bill_state_region = :whatstate"

flag = cn.execute(sqlCommand,args)
?flag
=.t.

 

Persisting to and from XML

You can save argument values in an XML string and you can recreate an argument object from an XML string.

dim sting as c
string = args.XML
?string
<SQLArguments>
    <SQLArgument>
        <Name>companyname</Name>
        <Data Type="C"></Data>
        <IsNull Type="L">1</IsNull>
        <Usage>Input</Usage>
    </SQLArgument>
    <SQLArgument>
        <Name>whatstate</Name>
        <Data Type="C">MA</Data>
        <IsNull Type="L">0</IsNull>
        <Usage>Input</Usage>
    </SQLArgument>
</SQLArguments>
 

'Now, populate a new arguments object from this XML string
dim args2 as sql::arguments
args2.xml = string

'prove that args2 has been populated
?args.ArgumentNumber("whatstate")
= 2

?args2[2].data
= "MA"
 

 

The ResultSet Object

Getting a ResultSet

After a SELECT command has been successfully executed, you can get a ResultSet object.

The ResultSet is a property of the connection object.

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select * from customer where bill_state_region = 'MA'"
flag = cn.execute(sqlCommand)
dim rs as sql::ResultSet
rs = cn.ResultSet
 

 

SQL_ResultSet_Preview() Function

The sql_ResultSet_preview() function is a built-in Xbasic function for getting a quick view of a ResultSet.

sql_ResultSet_preview(rs)

Here is the result of executing this command.

And here is the result of executing the command immediately again.

Notice that there is no data shown. That's because after the first sql_ResultSet_preview() command was executed, the record pointer was at the last row of the ResultSet. A ResultSet is 'forward only'. You would have to re-execute the query and then get a new ResultSet if you wanted to see the results a second time.

 

Read only - can't update like a .dbf table

The ResultSet if readonly. Unlike a native table pointer which you get by executing a table.open("tablename") command, a ResultSet cannot be updated, and you can only move through it in the forward direction. With a .dbf table you can do this:

dim tbl as p
tbl = table.open("customer")
tbl.change_begin()
tbl.firstname = "Jay"
tbl.change_end(.t.)
tbl.fetch_next()
tbl.fetch_next()
tbl.fetch_first()

If you want to update a SQL table, you execute SQL commands. You don't try to modify values in the ResultSet!

ResultSet Object Methods

The ResultSet object has many methods. If does NOT have a reliable way of finding out how many records are in the ResultSet though. You must use a COUNT query to find out how many rows are in a SELECT statement.

.NextRow()

  Use to find out if there are records in the ResultSet. This is an easy way to find out if the ResultSet has any records in it without having to first execute a COUNT query.

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select * from customer where 1=2"
flag = cn.execute(sqlCommand)
dim rs as sql::ResultSet
rs = cn.ResultSet
flag = rs.NextRow()
?flag
 = .f.
 

When you first get a pointer to a ResultSet, the current row pointer in the ResultSet is positioned BEFORE the first records in the ResultSet. So, the first time you execute .NextRow(), it will position the pointer on the FIRST records in the ResultSet.

NOTE: If you read data (using the ResultSet's .Data(), or .DataIsNull() method) from a ResultSet - WITHOUT FIRST USING .NextRow() to position the pointer on the first row in the Resultset - then Alpha Five automatically does a .NextRow() to position the pointer on the first record in the Resultset. Therefore, the following two sets of commands are equivalent:

 

cn.execute("Select * from customer")
rs = cn.resultset
rs.nextRow()
?rs.data("Lastname")
= "Graham"

And

cn.execute("Select * from customer")
rs = cn.resultset
?rs.data("Lastname")
= "Graham"

 

Forward only - unlike a .dbf table

No .PrevRow() command!

Looping through a ResultSet

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select * from customer where bill_state_region = :whatstate"
dim args as sql::arguments
args.add("whatstate","CA")
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
 

'This actually moves to the first row in the ResultSet, NOT the second row as you would excpect
flag = rs.nextRow()

'We are now positioned on the first row
dim txt as c
while flag
    txt = txt + "Lastname: " + rs.data("Lastname") + crlf()
    'when there are no more records in the ResultSet, executing .nextRow()
    'will return .f., so flag will be .fl and the while loop will end
    flag = rs.nextRow()
end while
 

How many records are in the ResultSet

There is no way of knowing. You have to do a COUNT query.

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select count(*) from customer where bill_state_region = :whatstate"
dim args as sql::arguments
args.add("whatstate","CA")
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
count = rs.data(1)
?count
= 6
 

ResultSet Object Methods - continued

.Data(number) or .Data("columnname") (tbl.Data() for Native tables)

To read the data for a particular column in the ResultSet, use the .data() method. You can pass in a column number, or a column name.

flag = cn.open("::Name::AlphasportsXbasic")
dim sqlCommand as c
sqlCommand = "Select count(*) from customer where bill_state_region = :whatstate"
dim args as sql::arguments
args.add("whatstate","CA")
flag = cn.execute(sqlCommand,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
?rs.data(2)
= "Michael"
?rs.data("Firstname")
= "Michael"
 

You can use a similar approach to reading data from a .dbf table.

Notice that the 'traditional' method for reading a field from a .dbf table returns trailing spaces!

dim tbl as p
tbl = table.open("customer")
?tbl.data("Firstname")
= "Michael"
?tbl.Firstname
= "Michael                     "

.DataIsNull()

To test is data is NULL, use the DataIsNull() method.
 

?rs.DataIsNull("Firstname")
= .f.
 

ResultSet Object Methods - continued 2

Here are some useful methods of the ResultSet object.

.ToString()

flag = cn.open("::Name::AlphasportsXbasic")
cn.Execute("select firstname, lastname, bill_city from customer where bill_state_region = 'ca'")
rs = cn.ResultSet
?rs.toString()
= Willy Winka Los Angeles
Yvonne Harrington Los Angeles
Joan McAndrews Los Angeles
Leonard Burtonski Santa Clarita
Peter Harrison Los Angeles
James Mahoney Long Beach

 

.ToDBF()

Create a .dbf table from a Resultset

flag = cn.open("::Name::AlphasportsXbasic")
cn.Execute("select firstname, lastname, bill_city from customer where bill_state_region = 'ca'")
rs = cn.ResultSet
?rs.ToDBF("c:\myfiles\testtable.dbf")
= .T.
file_add_to_db("c:\myfiles\testtable.dbf")
 

 .ToExcel()

Create an Excel file from a Resultset

 .ToJSONObjectSyntax()

JSON is very useful in Web applications. Create a JSON object from the data in a resultset.

flag = cn.open("::Name::AlphasportsXbasic")
cn.Execute("select firstname, lastname, bill_city from customer where bill_state_region = 'ca'")
rs = cn.ResultSet
?rs.ToJSONObjectSyntax()
{FIRSTNAME : 'Willy', LASTNAME : 'Winka', BILL_CITY : 'Los Angeles'}
{FIRSTNAME : 'Yvonne', LASTNAME : 'Harrington', BILL_CITY : 'Los Angeles'}
{FIRSTNAME : 'Joan', LASTNAME : 'McAndrews', BILL_CITY : 'Los Angeles'}
{FIRSTNAME : 'Leonard', LASTNAME : 'Burtonski', BILL_CITY : 'Santa Clarita'}
{FIRSTNAME : 'Peter', LASTNAME : 'Harrison', BILL_CITY : 'Los Angeles'}
{FIRSTNAME : 'James', LASTNAME : 'Mahoney', BILL_CITY : 'Long Beach'}
 

Executing Insert, Updates and Deletes

 

Syntax of an Update Statement

Be sure to put in a WHERE clause or else you will update every record in the table!

Update customer set lastname = 'Rabins', firstname = 'Selwyn' where customer_id = 1

Same command, this time using arguments.

Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id

 

Syntax of an Insert Statement

  Insert into customer (firstname, lastname, company) Values ('Jay', 'Talbot', 'Alpha Software')

Same command, this time using arguments.

  Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)

 

Syntax of a Delete Statement

Be sure to put in a WHERE clause or else you will delete every record in the table!

Delete from Customer where customer_id = 23

Same command, this time using arguments.

Delete from Customer where customer_id = :whatCustomerId

 

Did it work?

To see if an Insert, Update or Delete worked, you check the callResult.RowsAffected property. (After first making sure that the .execute() method did not return .f.)

If the .execute() method returns .f., then the SQL command had a syntax error.

If the .execute() method returns .t., then the SQL command executed, but might have done nothing.

 

How many rows were affected?

The callResult has a .RowsAffected property that tells you how many records were updated, deleted, or inserted by the last SQL command.

sqlUpdate = "Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id"
dim args as sql::arguments
args.add("newFirstname","Selwyn")
args.add("newLastname","Rabins")
args.add("oldCustomer_id",1)
?cn.Execute(sqlUpdate,args)
= .T.
?cn.CallResult.RowsAffected
= 1

 

What was the value of the auto-increment field?

The callResult has a .LastInsertedIdentity that tells you the value of the auto-increment field for the new record created by the last INSERT statement.

sqlInsert =  "Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)"
dim args as sql::arguments
args.add("newFirstname","Jay")
args.add("newLastname","Talbott")
args.add("newCompany","Alpha")
?cn.Execute(sqlInsert,args)
=.T.
?cn.CallResult.RowsAffected
= 1
?cn.CallResult.LastInsertedIdentity
= 61

 

Using the Grid Component to Show SQL Statements

An easy way to get the syntax for a SQL statement is to let the Grid component show you what SQL it generated.

 

 

 

Getting your Data into a SQL Database

Export Operation

You can export tables from Alpha Five to a SQL database. You can export multiple tables at once.

Upsize Genie

The Upsize Genie does much more than just export tables - it prepares the tables before exporting. e.g. make sure that all auto-increment fields are numeric, etc.

Built-in Tools

SQL Query Genie

Allows you to build SQL Select commands

Command Window

Allows you to execute SQL commands directly against the SQL database much like we have been doing in the Interactive window

Database Explorer

Allows you to see what's in a SQL database

Action Scripting

Can write simple Xbasic script to manipulate SQL databases

Portable SQL vs. Native SQL

Unfortunately, SQL is not standard across all different Database vendors. There are different conventions for qualifying object names (e.g. [first name] in SQL Server/Access, `first name` in MySQL, "first name" in Oracle), different command syntaxes, etc.

Alpha Five defines a special 'Portable SQL' syntax so that you can write you SQL without worrying about the differences between SQL databases. When Alpha Five executes a Portable SQL command it automatically translates the SQL to the correct 'native' SQL just before sending the command to the SQL database.

Portable Functions

When you write Portable SQL, you can use Portable Functions (e.g. concat() ) in your commands.
To get a list of portable sql functions:

dim cn as sql::connection
?  cn.ListPortableSQLFunctions()

Currently about 90 portable SQL functions.

Portable Syntax

When you using the Query Builder, Alpha Five generates Portable SQL syntax.

The SQL Query Genie will show you the Native SQL for a given Portable SQL query.

Telling Alpha Five which type of SQL to use

When you execute a SQL command you can tell Alpha Five if your command is native or portable SQL

Here we are connected to a MySQL database. In MySQL object names are quotes using a backtick.

 

'a Native sql select statement
sqlNative = "select `firstname`, `lastname` from customer"
cn.PortableSQLEnabled = .f.
?cn.Execute(sqlNative)
= .T.
 

 

'Now, turn on portable sql and see the command fail
cn.PortableSQLEnabled = .t.
?cn.Execute(sqlNative)
= .F.
?cn.CallResult.text
= Error parsing SQL statement

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

8 - 'line 1:8: unexpected char: '`''

 

'Now, write the SQL as a portable SQL statement
sqlPortable = "select [firstname], [lastname] from [customer]"
cn.PortableSQLEnabled = .f.
?cn.Execute(sqlPortable)
= .f.
?cn.CallResult.text
= Database API specific error

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

1064 - 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[firstname], [lastname] from [customer]' at line 1'
 

'Now, turn portable SQL on
cn.PortableSQLEnabled = .t.
?cn.Execute(sqlPortable)
= .t.

 

Portable SQL Conventions - Use [ ] for object names

Using the Query Genie to Generate Native Syntax

If you want to generate Native syntax, use the Query Builder and then go to the Native Syntax window.

The TableInfo Object

What is a TableInfo Object

TableInfo object contains information about a SQL table.

Getting a Table Info Object

Dim ti as sql::tableInfo
Dim cn as sql::connection
flag = cn.open("::Name::AlphasportsXbasic")
?cn.getTableInfo(ti,"Customer")
= .t.
 

 

'There is a lot of information about the table in the tableInfo. Let's poke around in the Interactive window
?ti.Column.Count
= 21

?ti.Column[1].name
= "CUSTOMER_ID"

?ti.Column[1].nativetypename
= "numeric"
?ti.Column[1].dbftype
= "N"

'Let's see all of the properties in the tableInfo.
?ti
= L AddColumn(Column as SQL::DataTypeInfo) 'Add a Column.
L AddIndex(Index as SQL::IndexInfo) 'Add a Index.
V Clear() 'Reset the contents of the TableInfo as if it had just been declared.
P Clone() 'Create a copy of an object instance.
L ColumnIsInPrimaryKey(N Index) 'Returns true if the column matching the index requested is part of the table's primary key
N ColumnNumber(Name as C) 'Get the index of a Column from the name.
L DeleteColumn(Index as N) 'Delete a Column.
L DeleteIndex(Index as N) 'Delete a Index.
N IndexNumber(Name as C) 'Get the index of a Index from the name.
L InsertColumn(Column as SQL::DataTypeInfo [, InsertBefore as N = 1]) 'Insert a new Column.
L InsertIndex(Index as SQL::IndexInfo [, InsertBefore as N = 1]) 'Insert a new Index.
C ListColumns([L IncludeTypes = .f.]) 'Create a list of the table columns (optionally including types).
L MoveColumn(MoveTo as N, MoveFrom as N [ItemsToCopy as N = 1]) 'Move a block of Column items.
L MoveIndex(MoveTo as N, MoveFrom as N [ItemsToCopy as N = 1]) 'Move a block of Index items.
P NewInstance() 'Create a new object instance of the same type.
L UpdateStatistics(P Connection) 'Gather statistical information about the actual table.
ActiveLinkDBFRowSyntax = CUSTOMER_ID,N,9,0,"notnullable,constant,setnull,showautonumber"
FIRSTNAME,C,20,0,"nullable,updateable,setnull,shownull"
LASTNAME,C,20,0,"nullable,updateable,setnull,shownull"
COMPANY,C,32,0,"nullable,updateable,setnull,shownull"
PHONE,C,20,0,"nullable,updateable,setnull,shownull"
FAX,C,20,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS_1,C,40,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS_2,C,40,0,"nullable,updateable,setnull,shownull"
BILL_CITY,C,20,0,"nullable,updateable,setnull,shownull"
BILL_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
BILL_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
BILL_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS_1,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS_2,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_CITY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
SHIP_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_SAME,L,1,0,"nullable,updateable,setnull,shownull"
EMAIL,C,60,0,"nullable,updateable,setnull,shownull"
CREDITRATING,N,3,0,"nullable,updateable,setnull,shownull"

Alias = "
CanUpdateInTransaction = .T.
Catalog = "
+Column.
Comment = "
DBFRowSyntax = CUSTOMER_ID,N,9,0,"notnullable,constant,generate"
FIRSTNAME,C,20,0,"nullable,updateable,setnull,shownull"
LASTNAME,C,20,0,"nullable,updateable,setnull,shownull"
COMPANY,C,32,0,"nullable,updateable,setnull,shownull"
PHONE,C,20,0,"nullable,updateable,setnull,shownull"
FAX,C,20,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS_1,C,40,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS_2,C,40,0,"nullable,updateable,setnull,shownull"
BILL_CITY,C,20,0,"nullable,updateable,setnull,shownull"
BILL_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
BILL_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
BILL_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS_1,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS_2,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_CITY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
SHIP_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_SAME,L,1,0,"nullable,updateable,setnull,shownull"
EMAIL,C,60,0,"nullable,updateable,setnull,shownull"
CREDITRATING,N,3,0,"nullable,updateable,setnull,shownull"

IdentityColumnSubscript = 1
IdentitySequenceName = "
+Index.
LastKnownRowCount = -1
Name = "dbo.customer"
NativeAPI = "SQLServer"
NativeSyntax = "SQLServer"
Owner = "dbo"
PrimaryKeyExpression = "CUSTOMER_ID"
PrimaryKeySubscript = 1
QueryAlias = "
Schema = "
SelectRowLimit = -1
SizeToFit = .F.
TableName = "customer"
TableType = 0
TableTypeName = "Table"
UniqueName = "dbo.customer"
Updateable = .T.


 

Using the Connection Object to Generate SQL Commands

Example:  cn.GenerateUpdateStatement(tableInfo)