Theme:
v
SQL DLL Tutorial
Other pages of interest:

Description

SQL is a C# ActiveX DLL which gives any APL+Win (or Dyalog APL, VB6 or C#) developer direct and full easy access to MS Access and SQL Server databases.

Tutorial

This page contains a quick Tutorial to get you started with the SQL DLL.

      ⍝ Before using the LC.Objects.SQL DLL you must first instantiate it (of course):

      'sql'⎕wi'*Create' 'LC.Objects.SQL.SQL'
sql

      ⍝ You can query the DLL version number with:

      'sql'⎕wi'Version'
2.22.0.0

      ⍝ Note that the best recommended way to call a DLL method is to use the *X prefix
      ⍝ and the best recommended way to call a DLL property is to use the *x prefix

      'sql'⎕wi'*xVersion'
2.22.0.0

      ⍝ The properties are:

      'sql'⎕wi'*properties'
 children class clsid data def description errorcode errormessage events instance interface links methods modified modifystop name obj opened pending progid properties self state suppress
       unicodebstr version xAccConString xAccConStrings xAccOpened xCalledFromAPL xConString xMsgBoxErrors xOpened xThrowErrors xVersion

      ⍝ The methods are:

      'sql'⎕wi'*methods'
 Close Create Defer Delete EnumEnd EnumNext EnumStart Event Exec Info Modify New Open Ref Send Set SetLinks XAbout XAccAddColumn XAccAddColumnDoc XAccAddData XAccAddDataDoc XAccCloseDatab
      ase XAccCloseDatabaseDoc XAccColumns XAccColumnsDoc XAccCompactDatabase XAccCompactDatabaseDoc XAccConStringsDoc XAccDropColumn XAccDropColumnDoc XAccDropTable XAccDropTableDoc XAcc
      ExecNonQuery XAccExecQuery XAccLinkedTables XAccNumRecs XAccOpenDatabase XAccPrimaryKeys XAccRelations XAccTables XAccUpdateData XAccUpdateDataDoc XAddData XAddDataDoc XAddPrimaryKe
      y XAddPrimaryKeyDoc XAddRelation XAddRelationDoc XAddTable XAddTableDoc XChangeNullable XChangeNullableDoc XCloseDatabase XCloseDatabaseDoc XColumnDescriptions XColumns XColumnsDoc
      XColumns_2 XConvertToDoubleMatrix XConvertToIntMatrix XConvertToStringMatrix XCreateDatabase XCreateDatabaseDoc XCreateRemoteDatabase XCreateRemoteDatabaseDoc XDatabases XDatabasesD
      oc XDbExists XDbExistsDoc XDropDatabase XDropDatabaseDoc XDropTable XDropTableDoc XExecNonQuery XExecNonQueryDoc XExecQuery XExecQueryDoc XForeignKeys XForeignKeysDoc XMdbStructure
      XMdbStructureDoc XNextId XNumRecs XNumRecsDoc XOpenDatabase XOpenDatabaseDoc XOpenedDoc XPrimaryKeys XPrimaryKeysDoc XRelations XRelationsDoc XRemoveRelation XRemoveRelationDoc XTab
      les XTablesDoc XUpdateData XUpdateDataDoc XVersionDoc

      ⍝ Or, if you want better formatted lists of properties and methods:

      ]sqlprops
xAccConString   xAccOpened      xConString      xOpened         xVersion
xAccConStrings  xCalledFromAPL  xMsgBoxErrors   xThrowErrors

      ]sqlmeths
XAbout                  XAccUpdateData          XDbExists
XAccAddColumn           XAddData                XDropDatabase
XAccAddData             XAddPrimaryKey          XDropTable
XAccCloseDatabase       XAddRelation            XExecNonQuery
XAccColumns             XAddTable               XExecQuery
XAccCompactDatabase     XChangeNullable         XForeignKeys
XAccDropColumn          XCloseDatabase          XMdbStructure
XAccDropTable           XColumnDescriptions     XNextId
XAccExecNonQuery        XColumns                XNumRecs
XAccExecQuery           XColumns_2              XOpenDatabase
XAccLinkedTables        XConvertToDoubleMatrix  XPrimaryKeys
XAccNumRecs             XConvertToIntMatrix     XRelations
XAccOpenDatabase        XConvertToStringMatrix  XRemoveRelation
XAccPrimaryKeys         XCreateDatabase         XTables
XAccRelations           XCreateRemoteDatabase   XUpdateData
XAccTables              XDatabases

      ⍝ You can get documentation for any property or method using the ]sqldoc User Command:

      ]sqldoc MdbStructure
Returns a nested matrix showing the MdbStructure for the entire database or for a given table

C# Syntax:   object MdbStructure(object param1, object param2)
APL Syntax:  'sql'⎕wi'*XMdbStructure' includeOrExclude includeOrExclude

includeOrExclude must be a nested vector of strings starting with the string 'include'
or with the string 'exclude', followed by one or more table names

Alternatively includeOrExclude can be a 2-element nested vector of empty strings, i.e. ('' '')
meaning that we want the structure of the whole database

The result is an 8-columns nested matrix, with the following columns:
[;1]= son id
[;2]= father id
[;3]= field type (one of: 'varchar', 'nvarchar', 'int', 'bigint', 'float', 'date', 'datetime', 'bit' or 'memo')
        (SQL codes are: -1=memo, 4=integer, -5=long, 6=double, -7=boolean, 11=datetime, 12=text, ??=bigint)
[;4]= name (file, table or column name)
[;5]= associated primary key (for a foreign key column)
[;6]= field size (-1 for memo fields, 1 for any date or numeric field)
[;7]= description
[;8]= queries (not used in this version)

Example:
        'sql'⎕wi'*XMdbStructure'('include' 'depts')('' '')
1 0         c:\temp\temp.mdf   0
2 1         depts              0
3 2 bigint  DeptId             1
4 2 varchar Name              20

        ]display 'sql'⎕wi'*XMdbStructure'('include' 'depts')('' '')
.→---------------------------------------------.
↓               .→---------------..⊖.    .⊖..⊖.∣
∣ 1  0          ∣c:\temp\temp.mdf∣∣ ∣ 0  ∣ ∣∣ ∣∣
∣               '----------------''-'    '-''-'∣
∣               .→----.           .⊖.    .⊖..⊖.∣
∣ 2  1          ∣depts∣           ∣ ∣ 0  ∣ ∣∣ ∣∣
∣               '-----'           '-'    '-''-'∣
∣               .→-----.          .⊖.    .⊖..⊖.∣
∣ 3  2  bigint  ∣DeptId∣          ∣ ∣ 1  ∣ ∣∣ ∣∣
∣               '------'          '-'    '-''-'∣
∣               .→---.            .⊖.    .⊖..⊖.∣
∣ 4  2  varchar ∣Name∣            ∣ ∣ 20 ∣ ∣∣ ∣∣
∣               '----'            '-'    '-''-'∣
'∊---------------------------------------------'

        'sql'⎕wi'*XMdbStructure'('' '')('' '')
    1  0          c:\temp\temp.mdf                  0
    2  1          personnel                         0
    3  2 bigint   PersonId                          1
    4  2 bigint   DeptId             depts.DeptId   1
    5  2 varchar  FirstName                        20
    6  2 varchar  LastName                         30
    7  2 datetime BirthDate                         1
    8  2 bigint   ColInteger                        1
    9  2 double   ColDouble                         1
   10  2 bit      ColBoolean                        1
   11  2 memo     ColMemo                          ¯1
   12  1          depts                             0
   13 12 bigint   DeptId                            1
   14 12 varchar  Name                             20


      ⍝ The ]sqldoc User Command is not case sensitive so you could as well type:

      ]sqldoc mdbstructure
Returns a nested matrix showing the MdbStructure for the entire database or for a given table

C# Syntax:   object MdbStructure(object param1, object param2)
APL Syntax:  'sql'⎕wi'*XMdbStructure' includeOrExclude includeOrExclude
...

      ⍝ To create a new SQL Server database:

      'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'

      ⍝ Note that you only need to provide the database full path name.

      ⍝ The SQL Server database name will be the filename (without the path and the extension)
      ⍝ so in this case it will be:  mydb

      ⍝ If you provide a non existing path or
      ⍝ if you attempt to create a database which name already exists
      ⍝ you get an error:

      'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
 Database 'mydb' already exists. Choose a different database name.

      ⍝ The error is displayed by default as a ⎕wi result (it is a 1x1x1 rank-3 array) in the APL Session
      ⍝ (i.e. no error is signaled in APL):

      ⍴'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
1 1 1

      ⍝ However, this is because the xThrowErrors is set to 0 by default.
      ⍝ You can set it to 1: this will result in all errors occuring in C# being signaled as errors in APL:

      'sql'⎕wi'*xThrowErrors'
0

      'sql'⎕wi'*xThrowErrors'1

      'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
⎕WI ERROR: LC.Objects.SQL exception 80131500 Database 'mydb' already exists. Choose a different database name.
      'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
           ^

      ⍝ You can use the SignalError utility to force APL to signal errors
      ⍝ even if xThrowErrors is set to 0:

      'sql'⎕wi'*xThrowErrors'0

      SignalError'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
LC.Objects.SQL v2.22.0.0: Database 'mydb' already exists. Choose a different database name.
      SignalError'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
      ^

      ⍝ If you additionally set xMsgBoxErrors to 1 (it is 0 by default),
      ⍝ SignalError will be display the error in a MessageBox, then signal it:

      'sql'⎕wi'*xMsgBoxErrors'1

      SignalError'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
LC.Objects.SQL v2.22.0.0: Database 'mydb' already exists. Choose a different database name.
      SignalError'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'
      ^

      ⍝ and a MessageBox with the error is also displayed.

      ⍝ Again there is a DropDatabase method (I needed that for my tests)
      ⍝ but you cannot use it after you have opened a database,
      ⍝ even if you close it before attempting to use DropDatabase

      'sql'⎕wi'*XDropDatabase' 'c:\temp\mydb.mdf'

      ⍝ CAUTION: there is NO warning: the entire SQL Server database is destroyed!

      ⍝ Creating a database does not open it so DropDatabase worked here
      ⍝ We can recreate the database.

      'sql'⎕wi'*XCreateDatabase' 'c:\temp\mydb.mdf'

      ⍝ Let's reset the errors properties:

      'sql'⎕wi'*xThrowErrors'1
      'sql'⎕wi'*xMsgBoxErrors'0

      ⍝ Once you have created a database, you must open it before using it:

      'sql'⎕wi'*XOpenDatabase' 'c:\temp\mydb.mdf'

      ⍝ You can then query its Tables:

      'sql'⎕wi'*XTables'

      ⍝ Of course there are none yet, as we have just created the database:
      ⍝ Let's add a new table:

      'sql'⎕wi'*XAddTable' 'depts'(('DeptId'1'int'1 1)('Name'0'varchar'20 1))

      ⍝ Each field must be described by a 5-element vector:
      ⍝ [1]= field name
      ⍝ [2]= primary key (1=PK, 0=not PK)
      ⍝ [3]= data type (supported are: 'varchar', 'nvarchar', 'int', 'bigint', 'float', 'date', 'datetime', 'bit' or 'memo')
      ⍝ [4]= field size (1 for numeric types, ¯1 for memo columns)
      ⍝ [5]= field can not be null (1=cannot be null, 0=can be null)

      'sql'⎕wi'*XTables'
 depts

      ⍝ Let's now add data to this table:

      depts←5 2⍴0
      depts[;1]←⍳5
      depts[;2]←(⊂'dept '),¨⍕¨⍳5
      ⎕←depts
 1 dept 1
 2 dept 2
 3 dept 3
 4 dept 4
 5 dept 5

      'sql'⎕wi'*XAddData' 'depts'depts

      ⍝ You can then immediately query the database with ExecQuery:

      'sql'⎕wi'*XExecQuery' 'select * from depts'
 1 dept 1
 2 dept 2
 3 dept 3
 4 dept 4
 5 dept 5

      ⍝ Adding many records at once is pretty fast:

      depts←50000 2⍴0
      depts[;1]←5+⍳50000
      depts[;2]←(⊂'dept '),¨⍕¨5+⍳50000

      'sql'⎕wi'*XAddData' 'depts'depts

      ⍝ Adding these 50000 records took just a little more than one second!

      ⍝ Querying the number of records in existing tables

      'sql'⎕wi'*XNumRecs' 'depts'
50005

      ⍝ Use the DropTable method to drop a table

      'sql'⎕wi'*XDropTable' 'depts'

      'sql'⎕wi'*XTables'

      ⍝ Let's recreate the table as we will need it later in this demo

      'sql'⎕wi'*XAddTable' 'depts'(('DeptId'1'int'1 1)('Name'0'varchar'20 1))
      depts←5 2⍴0
      depts[;1]←⍳5
      depts[;2]←(⊂'dept '),¨⍕¨⍳5
      'sql'⎕wi'*XAddData' 'depts'depts

      ⍝ Let's create another table:
      ⍝ Create and populate the <Personnel> table

      cols←⊂'PersonId'1'int'1 1
      cols←cols,⊂'DeptId'0'int'1 1
      cols←cols,⊂'FirstName'0'varchar'20 0
      cols←cols,⊂'LastName'0'varchar'30 0
      cols←cols,⊂'BirthDate'0'datetime'1 0
      cols←cols,⊂'ColInteger'0'int'1 0
      cols←cols,⊂'ColDouble'0'float'1 0
      cols←cols,⊂'ColBoolean'0'bit'1 0
      cols←cols,⊂'ColMemo'0'memo'1 0
      ⎕←⊃cols
 PersonId   1 int       1 1
 DeptId     0 int       1 1
 FirstName  0 varchar  20 0
 LastName   0 varchar  30 0
 BirthDate  0 datetime  1 0
 ColInteger 0 int       1 0
 ColDouble  0 float     1 0
 ColBoolean 0 bit       1 0
 ColMemo    0 memo      1 0

      'sql'⎕wi'*XAddTable' 'personnel'cols

      'sql'⎕wi'*XTables'
 personnel depts

      ⍝ Let's add some data to this table, one record at a time:

      ⍝ Adding incomplete records would work with an MS Access database

      ⍝ data←(1,⍴data)⍴data←1 1'Eric' 'Lescasse' 18751                                 ⋄ SignalError'sql'⎕wi'*XAddData' 'personnel'data
      ⍝ data←(1,⍴data)⍴data←2 2'Eric' 'Lescasse' 18751 10                              ⋄ SignalError'sql'⎕wi'*XAddData' 'personnel'data
      ⍝ data←(1,⍴data)⍴data←3 2'Eric' 'Lescasse' 18751 10 10.1                         ⋄ SignalError'sql'⎕wi'*XAddData' 'personnel'data
      ⍝ data←(1,⍴data)⍴data←5 1'Eric' 'Lescasse' 18751 10 10.1 0                       ⋄ SignalError'sql'⎕wi'*XAddData' 'personnel'data
      ⍝ data←(1,⍴data)⍴data←6 2'Eric' 'Lescasse' 18751 10 10.1 0 'This is a long memo' ⋄ SignalError'sql'⎕wi'*XAddData' 'personnel'data

      ⍝ provided that you do not omit a field in the middle of other fields
      ⍝ and that the fields be provided in the same order as the columns in the database

      ⍝ With SQL Server, you must provide a value for each field:

      data←5 9⍴0
      data[;1]←1 2 3 5 6
      data[;2]←1 2 2 1 2
      data[;3]←'Eric' 'Christian' 'Bob' 'Doug' 'Ken'
      data[;4]←'Lescasse' 'Charles' 'Smith' 'Ostien' 'Iverson'
      data[;5]←18750+⍳5
      data[;6]←10
      data[;7]←10.1
      data[;8]←0
      data[;9]←'' '' '' '' 'This is a long memo'

      SignalError'sql'⎕wi'*XAddData' 'personnel'data

      'sql'⎕wi'*XNumRecs' 'personnel'
5

      'sql'⎕wi'*XExecQuery' 'select * from personnel'
 1 1 Eric      Lescasse 18751 10 10.1 0
 2 2 Christian Charles  18752 10 10.1 0
 3 2 Bob       Smith    18753 10 10.1 0
 5 1 Doug      Ostien   18754 10 10.1 0
 6 2 Ken       Iverson  18755 10 10.1 0 This is a long memo

      ⍝ You can use the DeNull2 utility to replace <null> values by APL "null" values

      DeNull2'sql'⎕wi'*XExecQuery' 'select * from personnel'
 1 1 Eric      Lescasse 18751 10 10.1 0
 2 2 Christian Charles  18752 10 10.1 0
 3 2 Bob       Smith    18753 10 10.1 0
 5 1 Doug      Ostien   18754 10 10.1 0
 6 2 Ken       Iverson  18755 10 10.1 0 This is a long memo

      ⍝ Additionnally to the Tables method, there's a Columns method
      ⍝ Using it with an empty argument retrieves all columns from the database

      'sql'⎕wi'*XColumns' ''
 mydb personnel PersonId   int         NOT NULL
 mydb personnel DeptId     int         NOT NULL
 mydb personnel FirstName  varchar  20 NULL
 mydb personnel LastName   varchar  30 NULL
 mydb personnel BirthDate  datetime    NULL
 mydb personnel ColInteger int         NULL
 mydb personnel ColDouble  float       NULL
 mydb personnel ColBoolean bit         NULL
 mydb personnel ColMemo    varchar  ¯1 NULL
 mydb depts     DeptId     int         NOT NULL
 mydb depts     Name       varchar  20 NOT NULL

      ⍝ Using it with a table name

      'sql'⎕wi'Columns' 'depts'
 mydb depts DeptId int        NOT NULL
 mydb depts Name   varchar 20 NOT NULL

      ⍝ Creating a Relation

      'sql'⎕wi'*AddRelation' 'depts' 'deptid' 'personnel' 'deptid'
¯1

      ⍝ Listing all Foreign Keys in the database

      'sql'⎕wi'*XForeignKeys' ''
 depts DeptId personnel DeptId FK_personnel_depts

      ⍝ Listing Foreign Keys for a given table

      'sql'⎕wi'*XForeignKeys' 'personnel'

      'sql'⎕wi'*XForeignKeys' 'depts'
 depts DeptId personnel DeptId FK_personnel_depts

      ⍝ Using MdbStructure

      'sql'⎕wi'MdbStructure' ('' '') ('' '')
  1  0          c:\temp\mydb.mdf               0
  2  1          personnel                      0
  3  2 int      PersonId                       1
  4  2 int      DeptId           depts.DeptId  1
  5  2 varchar  FirstName                     20
  6  2 varchar  LastName                      30
  7  2 datetime BirthDate                      1
  8  2 int      ColInteger                     1
  9  2 float    ColDouble                      1
 10  2 bit      ColBoolean                     1
 11  2 memo     ColMemo                       ¯1
 12  1          depts                          0
 13 12 int      DeptId                         1
 14 12 varchar  Name                          20

      ⍝ MdbStructure arguments are:
      ⍝ [1]=nested vector of includes or excludes
      ⍝ [2]=nested vector of includes or excludes

      'sql'⎕wi'MdbStructure' ('include' 'depts') ('' '')
 1 0         c:\temp\mydb.mdf   0
 2 1         depts              0
 3 2 int     DeptId             1
 4 2 varchar Name              20

      ⍝ For now MdbStructure always returns SIZES, DESCRIPTIONS and RELATIONS

      ⍝ You can use the PrimaryKeys method to get all the primary keys in the database:

      'sql'⎕wi'*XPrimaryKeys' ''
 depts     DeptId
 personnel PersonId

      ⍝ or only the ones in a given table:

      'sql'⎕wi'*XPrimaryKeys' 'personnel'
 personnel PersonId

      ⍝ Use the UpdateData method to bulk update data in the database

      na←'sql'⎕wi'*XExecQuery' 'select * from personnel where personId in (2,5)'
      na
 2 2 Christian Charles 18752 10 10.1 0
 5 1 Doug      Ostien  18754 10 10.1 0

      na[1;7 8 9]←999.1 1 'This is the memo for personId=2'
      na[2;9]←⊂'This is the memo for personId=5'

      na
 2 2 Christian Charles 18752 10 999.1 1 This is the memo for personId=2
 5 1 Doug      Ostien  18754 10  10.1 0 This is the memo for personId=5

      'sql'⎕wi'*XUpdateData' 'personnel' na
2

      ⍝ The UpdateData method returns the number of records affected in the database
      ⍝ Note: this is always equal to the number of lines in the <na> argument

      ⍝ Note: You must be careful to provide a nested array right argument which:
      ⍝ 1. contains as many columns as there are columns in the table to update
      ⍝ 2. contains columns in the same order as the table to update
      ⍝ 3. does not contain any "null" value

      ⍝ There also is an ExecNonQuery method which you can use when you want
      ⍝ to run an SQL statement which does not return a record set, for example
      ⍝ if you want to delete rows from a table:

      'sql'⎕wi'*XExecNonQuery' 'delete from personnel where personId in (1,3,4,7,9)'
2

      'sql'⎕wi'*XExecQuery' 'select * from personnel'
 2 2 Christian Charles 18752 10 999.1 1 This is the memo for personId=2
 5 1 Doug      Ostien  18754 10  10.1 0 This is the memo for personId=5
 6 2 Ken       Iverson 18755 10  10.1 0 This is a long memo

      ⍝ ExecNonQuery always returns the number of rows affected by the command

      ⍝ At any time you may know which database is opened:

      'sql'⎕wi'*xOpened'
c:\temp\mydb.mdf

      ⍝ Finally you must not forget to close the database

      'sql'⎕wi'*XCloseDatabase'

      ⍝ Extras
      ⍝ ---------------------------------------------------------
      ⍝ Let's reopen the database

      'sql'⎕wi'*XOpenDatabase' 'c:\temp\mydb.mdf'

      ⍝ You can use ExecQuery to query the SQL Server System Tables!

      ⍝ Use ExexQuery to  query the SQL Server data types and data type codes:

      ('sql'⎕wi'ExecQuery' 'select * from sys.types')[;1 3]
 image             34
 text              35
 uniqueidentifier  36
 date              40
 time              41
 datetime2         42
 datetimeoffset    43
 tinyint           48
 smallint          52
 int               56
 smalldatetime     58
 real              59
 money             60
 datetime          61
 float             62
 sql_variant       98
 ntext             99
 bit              104
 decimal          106
 numeric          108
 smallmoney       122
 bigint           127
 hierarchyid      128
 geometry         129
 geography        130
 varbinary        165
 varchar          167
 binary           173
 char             175
 timestamp        189
 nvarchar         231
 nchar            239
 xml              241
 sysname          256

      ⍝ Use ExecQuery to query the SQL Server tables:

      'sql'⎕wi'ExecQuery' 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
 mydb dbo personnel      BASE TABLE
 mydb dbo tempBulkUpdate BASE TABLE
 mydb dbo depts          BASE TABLE

      ⍝ Use ExecQuery to query the SQL Server Columns:

      ('sql'⎕wi'ExecQuery' 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS')[;(⍳23)~11]
 mydb dbo personnel      PersonId   1    NO  int            10  0
 mydb dbo personnel      DeptId     2    NO  int            10  0
 mydb dbo personnel      FirstName  3    YES varchar  20 20                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo personnel      LastName   4    YES varchar  30 30                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo personnel      BirthDate  5    YES datetime              3
 mydb dbo personnel      ColInteger 6    YES int            10  0
 mydb dbo personnel      ColDouble  7    YES float           2
 mydb dbo personnel      ColBoolean 8    YES bit
 mydb dbo personnel      ColMemo    9    YES varchar  ¯1 ¯1                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo tempBulkUpdate PersonId   1    NO  int            10  0
 mydb dbo tempBulkUpdate DeptId     2    NO  int            10  0
 mydb dbo tempBulkUpdate FirstName  3    YES varchar  20 20                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo tempBulkUpdate LastName   4    YES varchar  30 30                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo tempBulkUpdate BirthDate  5    YES datetime              3
 mydb dbo tempBulkUpdate ColInteger 6    YES int            10  0
 mydb dbo tempBulkUpdate ColDouble  7    YES float           2
 mydb dbo tempBulkUpdate ColBoolean 8    YES bit
 mydb dbo tempBulkUpdate ColMemo    9    YES varchar  ¯1 ¯1                iso_1       SQL_Latin1_General_CP1_CI_AS
 mydb dbo depts          DeptId     1    NO  int            10  0
 mydb dbo depts          Name       2    NO  varchar  20 20                iso_1       SQL_Latin1_General_CP1_CI_AS