So, you've heard about some new ODBC features that were introduced in Windows 7 beta and you wanna know more about it. There's a great blog posting on our sister blog site describing the features. Please go to http://blogs.msdn.com/data/archive/2009/07/06/odbc-dm-3-80-in-windows-7-and-windows-server-2008-r2.aspx for all the goodies.
Jimmy Wu
SQL Server Native Client Team
(Anton Klimov, a developer on the SQL Server Native Client team, wrote the following article.)
Connection strings for an OLE DB provider is a concept that causes a lot of confusion. One problem is that OLE DB itself does not define connection string as a provider concept.
The documentation at Connection String Syntax (http://msdn.microsoft.com/en-us/library/ms722656(VS.85).aspx) says:
"A connection string is a string version of the initialization properties needed to connect to a data store and enables you to easily store connection information within your application or to pass it between applications. Without a connection string, you would be required to store or pass a complex array of structures to access data. When accessing OLE DB directly, using IDataInitialize (http://msdn.microsoft.com/en-us/library/ms714296%28VS.85%29.aspx), the connection string is passed as a parameter to create an OLE DB data source object."
“when accessing OLE DB directly” actually means that the application doesn’t directly instantiate a corresponding provider object, but rather goes through the “service component”, (otherwise known as “core services”, layer (oledb32.dll) by doing something like:
hr = CoCreateInstance(
CLSID_MSDASC, // CLSID of “service component”
NULL, // pUnkOuter
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(void**)&m_pIDataInitialize)
;
This way, you get additional services from oledb32 like session pooling, automatic transaction enlistment, cursor engine, and support for additional interfaces.
This also applies to ADO; so for an ADO programmer, a connection string is a similar concept but the way the provider looks at it is frequently misunderstood.
From the provider point of view there is no such thing as a connection string, instead the provider understands the initialization properties, which are hinted to in the above passage as “complex array of structures to access data”. See also OLE DB Initialization Properties: Quick Reference (http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx).
What makes the situation especially confusing is that one of the properties is similar to a connection string. These are the so called “Extended Properties” (DBPROP_INIT_PROVIDERSTRING).
http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx for it says:
"A string containing provider-specific, extended connection information. Use of this property implies that the consumer knows how this string will be interpreted and used by the provider. Consumers should use this property only for provider-specific connection information that cannot be explicitly described through the property mechanism."
However, in practice, lack of understanding leads consumers to use a combination of keywords both corresponding to the generic OLE DB properties and to provider specific keywords making sense only in the context of the provider string.
For example the site listing lots of examples of connection strings has the following for SQL Server Native Client 10.0 OLE DB Provider (http://www.connectionstrings.com/sql-server-2008):
|
Provider=SQLNCLI10; Server=myServerAddress; Database=myDataBase; Trusted_Connection=yes; MarsConn=yes; |
|
Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes" |
Here “Provider” keyword is used by oledb32 to find a CLSID of the provider to instantiate. “Server”, “Database”, “Trusted_Connection” and “MarsConn” are not the names of the OLE DB initialization properties (which would be “Data Source”, “Integrated Security”, and “Mars Connection”). Moreover, MarsConn=yes will have no effect, see explanation below.
MultipleActiveResultSets is a keyword for ADO.NET , and MARS_Connection is for ODBC.
oledb32 process a connection string as follows: it tries to find the keywords corresponding to the generic OLE DB initialization keywords (corresponding to DBPROPSET_DBINIT), and it also queries a provider by using IDBProperties::GetPropertyInfo (http://msdn.microsoft.com/en-us/library/ms718175(VS.85).aspx) to see which initialization properties provider supports (for SQL Server Native Client it is DBPROPSET_SQLSERVERDBINIT). For matched keywords, the corresponding properties will be set, everything else goes into “Extended Properties”.
Consider the following example in VBScript:
set connection = CreateObject("ADODB.Connection")
connection.ConnectionString = "provider=sqlncli10;data source=akl6; integrated security=sspi;initial catalog=tempdb"
connection.Open
WScript.Echo connection.ConnectionString
Here is the result of the execution:
Provider=SQLNCLI10.1;Integrated Security=sspi;Initial Catalog=tempdb;Data Source=akl6;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
If we try the following connection string
"provider=sqlncli10;server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes"
we will get:
Provider=SQLNCLI10.1;Extended Properties="server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes";Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connect
ion=False;DataTypeCompatibility=0;Trust Server Certificate=False
As you can see all the keywords that we specified are bundled into “Extended Properties” since they are not recognized as property names. What is worse is that now we apparently have two keywords corresponding to MARS Connectivity and one is “yes”, whether the other is “False”.
Which one wins? It turns out that the generic value wins. So the setting “MarsConn=yes” as a part of OLE DB connection string has no effect. It would work only if the provider was initialized directly and provider specific property was not used, but “Extended Properties” was used.
If you have the following:
“Server=server1; Data Source=server2;”
The data Source will win.
Generic Initialization properties (DBPROPSET_DBINIT)
The following list shows property IDs and their associated description/keywords:
|
Property ID |
Description / keyword |
|
DBPROP_AUTH_INTEGRATED |
Integrated Security |
|
DBPROP_AUTH_PASSWORD |
Password |
|
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO |
Persist Security Info |
|
DBPROP_AUTH_USERID |
User ID |
|
DBPROP_INIT_ASYNCH |
Asynchronous Processing |
|
DBPROP_INIT_CATALOG |
Initial Catalog |
|
DBPROP_INIT_DATASOURCE |
Data Source |
|
DBPROP_INIT_HWND |
Window Handle |
|
DBPROP_INIT_LCID |
Locale Identifier |
|
DBPROP_INIT_PROPMT |
Prompt |
|
DBPROP_INIT_PROVIDERSTRING |
Extended Properties |
|
DBPROP_INIT_TIMEOUT |
Connect Timeout |
|
DBPROP_INIT_GENERALTIMEOUT |
General Timeout |
If default value is specified the property cannot be overridden with an “extended property” when used in an OLE DB connection string.
Provider Specific Properties (DBPROPSET_SQLSERVERDBINIT)
The following list shows the associated property IDs, default values (if any) and description/keywords for provider specific properties.
SSPROP_INIT_CURRENTLANGUAGE
(no default value)
Current Language
SSPROP_INIT_NETWORKADDRESS
(no default value)
Network Address
SSPROP_INIT_NETWORKLIBRARY
(no default value)
Network Library
SSPROP_INIT_USEPROCFORPREP
1
Use Procedure for Prepare
SSPROP_INIT_AUTOTRANSLATE
VARIANT_TRUE
Auto Translate
SSPROP_INIT_PACKETSIZE
4096
Packet Size
SSPROP_INIT_APPNAME
(no default value)
Application Name
SSPROP_INIT_WSID
(no default value)
Workstation ID
SSPROP_INIT_FILENAME
(no default value)
Initial File Name
SSPROP_INIT_ENCRYPT
VARIANT_FALSE
Use Encryption for Data
SSPROP_AUTH_REPL_SERVER_NAME
(no default value)
Replication server name connect option
SSPROP_INIT_TAGCOLUMNCOLLATION
VARIANT_FALSE
Tag with column collation when possible
SSPROP_INIT_MARSCONNECTION
VARIANT_FALSE
MARS Connection
SSPROP_INIT_FAILOVERPARTNER
(no default value)
Failover Partner
SSPROP_AUTH_OLD_PASSWORD
(no default value)
Old Password
SSPROP_INIT_DATATYPECOMPATIBILITY
0
DataTypeCompatibility
SSPROP_INIT_TRUST_SERVER_CERTIFICATE
VARIANT_FALSE
Trust Server Certificate
SSPROP_INIT_SERVERSPN
(no default value)
Server SPN
SSPROP_INIT_FAILOVERPARTNERSPN
(no default value)
Failover Partner SPN
Note that the corresponding properties are not going to be set, and as mentioned above, some of the settings might be ignored.
The following list shows keywords recognized in the provider string and its (approximate) corresponding property.
“Server"
DBPROP_INIT_DATASOURCE
“UID"
DBPROP_AUTH_USERID
“PWD"
DBPROP_AUTH_PASSWORD
“APP"
SSPROP_INIT_APPNAME
“WSID"
DBPROP_INIT_CATALOG
“Database"
DBPROP_INIT_CATALOG
“Language"
SSPROP_INIT_CURRENTLANGUAGE
“Network" or “Net” or “Network Library”
SSPROP_INIT_NETWORKLIBRARY
“Address" or “Addr”
SSPROP_INIT_NETWORKADDRESS
“Trusted_Connection"
DBPROP_AUTH_INTEGRATED
“UseProcForPrepare"
SSPROP_INIT_USEPROCFORPREP
“LCID"
Not used
“Prompt"
DBPROP_INIT_PROPMT
“PersistSensitive"
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO
“AutoTranslate" or “Auto Translate”
SSPROP_INIT_AUTOTRANSLATE
“Timeout"
DBPROP_INIT_TIMEOUT
“PacketSize"
SSPROP_INIT_PACKETSIZE
“HWND"
DBPROP_INIT_HWND
“AttachDBFileName"
SSPROP_INIT_FILENAME
“Encrypt"
SSPROP_INIT_ENCRYPT
“MarsConn"
SSPROP_INIT_MARSCONNECTION
“FailoverPartner"
SSPROP_INIT_FAILOVERPARTNER
“DataTypeCompatibility"
SSPROP_INIT_DATATYPECOMPATIBILITY
“TrustServerCertificate"
SSPROP_INIT_TRUST_SERVER_CERTIFICATE
“ServerSPN"
SSPROP_INIT_SERVERSPN
“FailoverPartnerSPN"
SSPROP_INIT_FAILOVERPARTNERSPN
Linked server configuration note:
In the "Linked Server Properties -- New Linked Server" dialog box“ (SQL Server Management Studio go to Server Objects -> Linked Servers -> New Linked Server), the Provider string” really corresponds to “Extended Properties”.
Some users try putting their generic OLE DB Connection string, which leads to the server discarding all or the part of the settings and it could end up connecting to the local machine instead of the machine that the user thinks is specified. For instance if “Failover Partner” is specified in that dialog and it contains a space, the setting will have no effect since in the provider string no space is expected.
Hi, My name is David Schwartz and I own the documentation for SQL Server Native Client and Windows (formerly Microsoft) Data Access Components.
Are there sections in the documentation that don't meet your needs? I'm interested to know your thoughts on the documentation.
My email address is dschwart@microsoft.com and I'll read and reply to your documentation comments. I look forward to hearing from you.
ODBC 3.8 is in beta 1 of the Windows 7 SDK...
ODBC 3.8 includes new features such as
· Executing connection operations asynchronously.
· Streamed output parameters.
· ODBC C data type extensibility.
The SQL Server Native Client team at Microsoft is accepting applications for the position of program manager.
For more information about this position, see:
http://members.microsoft.com/careers/search/details.aspx?JobID=F9C9E835-AB77-449C-9E6B-732AFD1A7EAB
If you are passionate about ODBC and native data providers, please contact Raghu Ram (raghu.ram@microsoft.com) or Zlatko Michailov (zlatko.michailov@microsoft.com).
Earlier this week Microsoft release a PHP driver for SQL Server. You can download it from http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-466B-B09A-6597C21A2E2A&displaylang=en Documentation is available online at http://msdn.microsoft.com/en-us/library/cc296221.aspx. There is a team blog at http://blogs.msdn.com/sqlphp/ and a community forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=2108&SiteID=1
The SQL Server Driver for PHP represents a new way for Microsoft to help developers access SQL Server from non-Microsoft development environments. The driver is a thin C++ wrapper on top of the Microsoft SQL Server Native Client ODBC driver, translating the PHP calls into ODBC API calls. This approach simplifies development and reduces the surface area from a security standpoint. In the future, we could build drivers for other languages (Ruby, Python, etc.) using this same approach without having to duplicate the low-level client interfaces. By making the source code for the PHP driver available, we are providing a blueprint to communities that do not yet have Microsoft-built SQL Server connectivity to show how they can access SQL Server via our ODBC driver.
The source code for the PHP driver is available from the codeplex site http://www.codeplex.com/SQL2K5PHP
In general we recommend against configuring a deployed application to use a new version of SQL Native Client (SNAC) unless the application developer has certified that the application has been tested with the version of SNAC in question. This is because we don't know how well applications will behave when they encounter differences between a new version of SNAC and earlier versions of SNAC or MDAC/WDAC. In this post we'll look at the types of change we make between releases and how to design an application to anticipate then. Briefly, the types of change that could cause problems can be categorized as:
-
New functionality
-
New data types
-
Error handling
Let's look at each of these in turn.
New functionality
This is an area where there should be no impact unless an application opts-in to use specific new features, so the application developer has complete control and the scope for existing code mis-behaving on upgrade is very small.
New data types
This is the area where most problems occur. With new releases of SQL Server we often add new data types. New types should have no impact on deployed applications when the server is upgraded but the client application isn't. Firstly, for application to be exposed to the new type at all schema changes would need to be made. Secondly, if a schema change is made, applications already deployed will 'see' new types as an existing type, often nvarchar. This is because SQL Server knows what types the client understands from the TDS protocol version the client API uses and will convert new types to pre-existing types when it sends data to clients.
We generally make it possible for an application using an older version of SNAC to detect the actual type (via a 'local type name' property) in case it needs to be aware of the new type but is not able to upgrade to the newer version of SNAC that exposes the type. This means that applications should base their logic on API type codes (eg SQL_VARCHAR in ODBC) rather than local type names (eg 'xml' or 'datetimeoffset') wherever possible. In practice this is the simplest way to code applications, so is no burden.
Problems can arise when a deployed application is configured to use a later version of SNAC than the one it was developed and tested with and when it encounters types introduced since its deployment. The way to avoid this type of problem is to treat types that an application doesn't recognise as nvarchar. ODBC and OLE DB provide conversions to and from nvarchar for all data types. Applications should avoid code such as
switch (dataType) {
case SQL_INTEGER://deal with integer
break;
case SQL_WVARCHAR://deal with nvachar
break;
}
where a new type can introduce undefined behavior. Instead, add code to deal with unrecognised types:
switch (dataType) {
case SQL_INTEGER://deal with integer
break;
case SQL_WVARCHAR://deal with nvachar
break;
default://either (a) add clean error handling code or
// (b) process the type as SQL_WVARCHAR. SNAC
// convert parameter and result values to SQL_C_WVARCHAR
}
One other pitfall is handling types with unspecified size, such as xml and varchar(max). SNAC returns the size of these types as SQL_SS_LENGTH_UNLIMITED in ODBC. This has the value 0 and can cause unpredictable behavior if an application uses it in arithmetic expressions for buffer allocation, for example. ODBC applications should treat a column or parameter size of 0 as 'huge' rather than 'small'. OLE DB returns the size of these types as ~0 (ie all bits set). Both ODBC and OLE DB in SNAC conform to the API specifications in using these values, so this isn't behavior we invented for SNAC alone, though their use isn't common in other products.
Finally, sometimes we strengthen our conformance to API specifications, and this can catch non-conforming applications. For example, in SNAC10 validation of the scale of SQL_TYPE_TIMESTAMP/DBTYPE_DBTIMESTAMP is more strict than in earlier versions. This is because in SQL Server 2008 datetime2 can have a scale (fractional seconds digits) of 0 to 7, where in earlier versions of SQL Server it could only be 0 or 3. It turns out that OLE DB didn't validate the bScale parameter of ICommandWithParameters::SetParameterInfo in MDAC and SNAC9, though it should have. Applications should always adhere to API specifications, even when a particular driver or provider's validation falls short.
Error handling
Newer releases of SNAC will often include changes in error messages. This is one way in which try to make it easier for you to diagnose and resolve issues without having to make a support call. Sometimes we improve the clarity of a message, sometimes we will add hints in the message to suggest the most common causes or cures for particular errors. Therefore, applications should avoid dependencies on error message text and should use error codes (such as SQLSTATE in ODBC) or numbers instead. If you do this you're also making it easier for your application to be used with localized versions of SNAC.
Occasionally when we optimize SNAC in a new release we change the order in which we validate parameters or split or merge code paths. This can mean that when an application tries to do something invalid the error reported changes between releases or an error is reported on a different API call. Generally we aim for earlier error detection so an error that is caught at execute time in one version might be caught at bind time in a leter version. It's also possible that if an API call has multiple errors the error we detect first may change. In almost all cases changes in this area should have no impact on code already debugged and tested with an earlier version of SNAC since the changes invariably mean there is an error in the application logic. Note that errors associated with runtime conditions such as connection failures or duplicate key errors are much less likely to change.
Microsoft SQL Server 2008 Feature Pack RC0, June 2008 is now available for download from http://go.microsoft.com/fwlink/?LinkId=110393
This includes the standalone installer for SQL Server 2008 Native Client (SNAC10).
The Feature Pack also includes a Command Line Utilities installer which enables installation of sqlcmd and bcp on client systems.
In SQL Server 2008, the x64 and Itanium versions of sqlncli.msi will install the 64-bit and 32-bit versions of SQL Server Native Client. The x86 version of sqlncli.msi will only work on 32-bit Windows operating systems and will install the 32-bit version of SQL Server Native Client. You will not be able to run the 32-bit version of sqlncli.msi on a 64-bit Windows operating system.
In prerelease versions of SQL Server 2008, you may find that the 32-bit version of sqlncli.msi does run on a 64-bit operating system. However, this may cause unexpected results. Use the appropriate 64-bit version of sqlncli.msi to install both the 64-bit and 32-bit version of SQL Server Native Client on your 64-bit Windows operating system.
(The content of this entry was developed by Anton Klimov, a software engineer on the SQL Server Native Client team.)
Table-valued, user-defined functions (TVF) can be used as an alternative to views or stored procedures. (
http://technet.microsoft.com/en-us/library/ms191165.aspx)
There is a problem, however, with the existing support for obtaining parameter information from classic (VB script) ADO code -- if you call a TVF as if it were a stored procedure, it is possible to derive parameter information, but execution will fail.
If one tries to prepare TVF as a select statement it is not possible to obtain the parameter information from a SQL OLE DB provider because providers do not support obtaining parameter information for parameters in the ‘FROM’ clause.
The example below obtains the parameter information and then uses it for the parameterized select statement.
Sub ADO_example()
Dim cn As New ADODB.connection
Dim param1 As ADODB.Parameter
cn.ConnectionString = "provider=sqloledb;integrated security=sspi;Data Source=akl3vm1; initial catalog=tempdb"
cn.Open
cn.Execute "if OBJECT_ID('f_distance', 'IF') is not null drop function f_distance"
cn.Execute "create function f_distance (@x int, @y int) returns table " _
& "as return select @x as x, @y as y, sqrt(@x*@x+@y*@y) as distance"
Set cmd1 = CreateObject("ADODB.Command")
cmd1.CommandText = "f_distance"
cmd1.CommandType = adCmdStoredProc
cmd1.ActiveConnection = cn
cmd1.Parameters.Refresh
Rem At this point you will get the parameters collection starting with the retun value
Rem which we will not use.
Rem The command text will be like: "{?=call f_distance(?,?)}"
Rem The 3 lines below would produce "The parameter is incorrect"
'cmd1.Parameters("@x") = 1
'cmd1.Parameters("@y") = 2
'Set rs = cmd1.Execute
Rem We will use a second command
Set cmd2 = CreateObject("ADODB.Command")
cmd2.CommandText = "select * from f_distance(?,?)"
cmd2.CommandType = adCmdText
cmd2.ActiveConnection = cn
Rem The line below would produce "Syntax error or access violation"
'cmd2.Parameters.Refresh
For i = 1 To cmd1.Parameters.Count - 1
Set param1 = cmd1.Parameters(i)
cmd2.Parameters.Append param1
Debug.Print param1.Name, param1.Type
Next i
cmd2.Parameters("@x") = 1
cmd2.Parameters("@y") = 2
Set rs = cmd2.Execute
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Debug.Print rs(i).Name, rs(i).Value
Next i
rs.MoveNext
Loop
End Sub
The SQL Server Customer Service and Support (CSS) group developed a new a more efficient way for you to get SQL Server 2005 hot fixes. For more information on hot fixes and cumulative updates, see http://blogs.msdn.com/psssql/archive/2008/04/25/sql-server-2005-sp3-and-self-service-hotfixes.aspx.
The setup for CTP6 of SQL Server 2008 does not install sqlncli.h in the SDK\Include directory. This is a bug in the CTP6 setup. To get sqlncli.h in the SDK\Include directory, you need to invoke Setup\sqlncli.msi from the directory where you ran setup.exe and when prompted, select Modify. Then, specify that you want the sqlncli.h and .lib files to be installed.
Microsoft SQL Server Native Client (referred to hereafter as SNAC) was introduced in SQL Server 2005 and combines an ODBC driver and OLE DB provider in a single DLL. The SQL Server ODBC driver and SQL Server OLE DB provider for earlier versions of SQL Server was delivered via Microsoft Data Access Components (MDAC), recently renamed Windows Data Access Components (WDAC) in Windows Vista and later. SNAC and WDAC coexist and serve different needs. SNAC provides support for the latest SQL Server 2005 features. If you are developing a generic application to operate with any ODBC driver or OLE DB provider (namely, an application which is not taking advantage of provider specific types), then using the Windows (WDAC) integrated SQL Server 2000 versions of the ODBC driver and OLE DB provider is a better choice since they map new SQL Server 2005 types to standard ODBC types that are compatible with SQL Server 2000 and other ODBC drivers. The differences in type behavior are discussed more in the “Differences between driver/provider versions” section below in the document.
With SQL Server 2008, we again wanted to expose new features and data types to applications in the most natural way, and we knew that some of the new features (Table-Valued Parameters, for example) would force fairly major code reorganization that would inevitably introduce behavior differences. Most applications won't be bothered at all by this, but when you take into account the huge number of applications written to use SQL Server, it's inevitable that some might be impacted. In order to minimize disruption when SQL Server is upgraded, we decided that SNAC in SQL Server 2005 (now refered to as SNAC9) and SNAC in SQL Server 2008 (now refered to as SNAC10) should be able to coexist side by side. This means that when SQL Server 2008 (or SNAC10 on a client-only system) is installed, applications already deployed using SNAC9 will see no change at all. They will simply continue to run the way they did before using the version of SNAC they originally were built with, which in this case would be SNAC9.. Switching an application to use SNAC10 requires some intervention and we strongly recommend that the application is thoroughly tested with the new version of SNAC before it is deployed in production environments.
Side by side co-existence is achieved by renaming the ODBC driver name and OLE DB progid (and allocating new classids too). The differences between SNAC9, SNAC10 and WDAC (which contains the SQL Server 2000 versions of the SQL ODBC driver and SQL OLE DB provider) are summarized in the following table:
|
Property |
WDAC |
SQL Server Native Client (SNAC9) |
SQL Server 2008 Native Client (SNAC10) |
|
ODBC Driver Name |
SQL Server |
SQL Native Client |
SQL Server Native Client 10.0 |
|
ODBC Header file name |
odbcss.h |
sqlncli.h |
sqlncli.h |
|
ODBC Driver DLL |
sqlsrv32.dll |
sqlncli.dll |
sqlncl10.dll |
|
ODBC lib file for BCP APIs |
odbcbcp.lib |
sqlncli.lib |
sqlncli10.lib |
|
ODBC dll for BCP APIs |
odbcbcp.dll |
sqlncli.dll |
sqlncli10.dll |
|
OLE DB PROGID |
SQLOLEDB |
SQLNCLI |
SQLNCLI10 |
|
OLE DB Header file name |
sqloledb.h |
sqlncli.h |
sqlncli.h |
|
OLE DB Provider DLL |
sqloledb.dll |
sqlncli.dll |
sqlncli10.dll |
Notice that we didn't change the header file name. Since the SNAC9 and SNAC10 SDK files are installed in different paths we can avoid the need to change #include directives in source code, only the include path need be changed. To minimize future code change when SNAC11 comes along, we added macros to the header file to enable applications to automatically pick up the correct version names when they are built for a specific version of SNAC (with the default being the latest version). If you look in sqlncli.h for SNAC10 you'll see things like this:
#if !defined(SQLNCLI_VER)
#define SQLNCLI_VER 1000
#endif
#if SQLNCLI_VER >= 1000
#define SQLNCLI_PRODUCT_NAME_FULL_VER_ANSI "Microsoft SQL Server Native Client 10.0"
#define SQLNCLI_PRODUCT_NAME_FULL_ANSI "Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_VER_ANSI "SQL Server Native Client 10.0"
#define SQLNCLI_PRODUCT_NAME_SHORT_ANSI "SQL Server Native Client"
...
#else // SQLNCLI_VER >= 1000
#define SQLNCLI_PRODUCT_NAME_FULL_VER_ANSI "Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_FULL_ANSI "Microsoft SQL Server Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_VER_ANSI "SQL Native Client"
#define SQLNCLI_PRODUCT_NAME_SHORT_ANSI "SQL Native Client"
...
Each version of SNAC will support the version of SQL Server that it ships with, two earlier versions and two later versions, so SQL Server support for the two existing SNAC versions is as follows:
|
SQL Server Native Client Version |
Supported SQL Server Versions |
|
SQL Server Native Client (SNAC9) |
Microsoft SQL Server 7.0
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
Next SQL Server version |
|
SQL Server 2008 Native Client (SNAC10) |
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
Next two SQL Server versions |
The server version support structure means that there is no rush to change the driver/provider an application uses. Developers can make the changeover on a timescale that makes business sense for them, such as the next major version releases of their applications.
In a similar way, supported Windows versions may change with each SNAC version to allow for older versions of Windows dropping out of support. For SNAC9 and SNAC10, operating system support is as follows:
|
SQL Server Native Client Version |
Supported Operating Systems |
|
SQL Server Native Client (SNAC9) |
Microsoft Windows 2000 Service Pack 4 or later
Microsoft Windows Server 2003 or later
Microsoft Windows XP Service Pack 1 or later
Microsoft Windows Vista
Microsoft Windows 2008 Server |
|
SQL Server 2008 Native Client (SNAC10) |
Microsoft Windows Server 2003 SP1 or later
Microsoft Windows XP Service Pack 2 or later
Microsoft Windows Vista
Microsoft Windows 2008 Server |
Differences between driver/provider versions
The different driver/provider versions see SQL Server data types differently because SQL Server converts values that it sends to clients to types that are understood by the client version, as summarized in the following table:
|
Data type |
MDAC and
SQL Server Native Client OLE DB
applications (SNAC9 or SNAC10) with DataTypeCompatibility=80 |
SQL Server Native Client
(SNAC9) |
SQL Server 2008 Native Client
(SNAC10) |
|
Types introduced in SQL Server 2005 |
|
CLR UDT (<= 8000) |
varbinary |
udt |
udt |
|
varbinary(max) |
image |
varbinary |
varbinary |
|
varchar(max) |
text |
varchar |
varchar |
|
nvarchar(max) |
ntext |
nvarchar |
nvarchar |
|
xml |
ntext |
xml |
xml |
|
Types introduced in SQL Server 2008 |
|
CLR UDT (> 8000) |
image |
varbinary |
udt |
|
date |
varchar |
varchar |
date |
|
datetime2 |
varchar |
varchar |
datetime2 |
|
datetimeoffset |
varchar |
varchar |
datetimeoffset |
|
time |
varchar |
varchar |
time |
|
geometry |
image |
varbinary |
udt |
|
geography |
image |
varbinary |
udt |
|
hierarchyid |
image |
varbinary |
udt |
For new types with unlimited size the maximum size is represented as SQL_SS_LENGTH_UNLIMITED (value 0) in ODBC and ~0 in OLE DB. The APIs uses different values because each API has its own convention for representing values of unlimited size (and these conventions pre-date SNAC). Applications which do arithmetic based on the size returned in parameter and result metadata and which don't take account of this special case may misbehave. This is one reason why we recommend that applications moving to new driver/provider versions should not be deployed without thorough testing. Other version differences are described in SQL Server Books Online.
Deciding which driver/provider to use with an application is straighforward once the above is understood.
- For 3rd party applications designed to work with a wide range of drivers/providers that do not specifically state that they support SNAC (such as Excel or Access, for example), or applications built using ATL or MFC use WDAC
- For 3rd party applications that specify a particular version of SNAC, use only that version of SNAC, even when a later version is available. If the application is following best practice guidelines SNAC will be redistributed with the application and its installation procedures should configure the correct version of SNAC for its use, so you shouldn't have to worry about this case
- For your own applications, stay with the driver/provider you are currently using until you reach a convenient point to upgrade and take advantage of new datatypes and features in the lastest version of SNAC
- If you cannot upgrade the application to use the latest version of SNAC but must adapt to schema changes use the table above to determine how new types will appear to your application
Steve Hale
SQL Server Native Client Development Team
Microsoft Corporation
Recently there have been several requests for samples showing how to use IRowsetFastLoad to send varying BLOB data per row or how to stream data to SQL Server via SQLOLEDB or SQLNCLI. Most of the available samples are too simplistic and don’t demonstrate varying data length per row or using ISequentialStream.
In this sample, you'll see both techniques in one source file. By default, the sample shows how to use IRowsetFastLoad to send variable length BLOB data per row using in-line bindings. In this case, the in-line BLOB data must fit in available memory. This method has better performance when the BLOB data is a few bytes to a few MB, because there is no additional stream overhead. For larger data, especially where the data is not necessarily all available at once in a block, streaming is a better choice.
When you uncomment #define USE_ISEQSTREAM, the sample will use ISequentialStream. The stream implementation is defined in the sample, and can send any size BLOB data simply by changing the MAX_BLOB size. In this case, the stream data does not have to fit in available memory or be available in one block. The provider is called using IRowsetFastLoad::InsertRow, passing a pointer to the stream implementation in the data buffer (rgBinding.obValue offset) along with the amount of data available to read from the stream. Some providers may not require the length of the data to be known when binding occurs, and in that case the length may be omitted from the binding.
Note that the sample does not write data to the provider using the provider’s stream interface. Rather, the sample passes a pointer to the stream object that the provider will consume to read the data. Typically, SQLOLEDB and SQLNCLI will read data in 1024 byte chunks from our object until all the data has been processed. Neither SQLOLEDB nor SQLNCLI have full implementations for allowing the consumer to write data to the provider’s stream object. Only zero length data can be sent via the provider’s stream object.
The consumer-implemented ISequentialStream object can be used with rowset data (IRowsetChange::InsertRow, IRowsetChange::SetData) and with parameters by binding a parameter as DBTYPE_IUNKNOWN.
Since DBTYPE_IUNKNOWN is specified as the data type in the binding, it has to match the type of the column or parameter being targeted. There are no conversions possible when sending data via ISequentialStream from rowset interfaces. For parameters, you should avoid using ICommandWithParameters::SetParameterInfo and specify a different type to force a conversion. This is because it would require the provider to cache all the BLOB data locally in order to convert it prior to sending it to SQL Server. Caching a large BLOB and converting it locally does not give good performance.
Recommended reading:
http://msdn2.microsoft.com/en-us/library/ms131277.aspx (SQL Server 2007)
http://technet.microsoft.com/en-us/library/aa198331(SQL.80).aspx (SQL Server 2000)
Code Sample
You can compile this sample at the command line by including the following libraries:
You will also need to change MyServer in the connection string to the name of a server at your location.
You'll also need to run the following Transact SQL before you execute the program:
create table fltest(col1 int, col2 int, col3 image)
You can see the results of the sample by running this Transact SQL:
select top 50 * from fltest
//
// PROGRAM: FastLoadTest
// (C) Microsoft Corporation. All rights reserved
//
// PURPOSE: Insert variable size BLOB data into different rows with IRowsetFastLoad.
// Demonstrate sending BLOB data with stream binding (ISequentialStream)
//
// REQUIREMENTS: create table fltest(col1 int, col2 int, col3 image)
// VERIFICATION: select top 50 * from fltest
//
// #define USE_ISEQSTREAM
#include <windows.h>
#define DBINITCONSTANTS // Must be defined to initialize constants in oledb.h
#define INITGUID
#include <sqloledb.h>
#include <oledb.h>
#include <msdasc.h>
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>
#define MAX_BLOB 200 // For stream binding this can be any size, but for inline it must fit in memory
#define MAX_ROWS 100
#define SAFE_RELEASE(p) if (p) {(p)->Release();(p)=NULL;}
#define DROP_TABLE L"drop table fltest"
#define CREATE_TABLE L"create table fltest(col1 int, col2 int, col3 image)"
void usage(void);
#ifdef USE_ISEQSTREAM
// ISequentialStream implementation for streaming data
class MySequentialStream : public ISequentialStream {
private:
ULONG m_ulRefCount;
ULONG m_ulBufSize;
ULONG m_ulReadSize;
ULONG m_ulBytesLeft;
ULONG m_ulReadPos;
BYTE * m_pSrcData;
BYTE * m_pReadPtr;
BOOL m_fWasRead;
public:
MySequentialStream(void)
{
m_ulRefCount = 1;
m_ulBufSize = 0;
m_ulReadSize = 0;
m_ulBytesLeft = 0;
m_ulReadPos = 0;
m_pSrcData = NULL;
m_pReadPtr = NULL;
m_fWasRead = FALSE;
}
~MySequentialStream(void)
{
}
virtual ULONG STDMETHODCALLTYPE AddRef(void)
{
return ++m_ulRefCount;
}
virtual ULONG STDMETHODCALLTYPE Release(void)
{
--m_ulRefCount;
if (m_ulRefCount == 0)
{
delete this;
return 0;
}
return m_ulRefCount;
}
virtual HRESULT STDMETHODCALLTYPE QueryInterface(REFIID riid, void ** ppvObj)
{
// HRESULT hr = E_FAIL;
if (!ppvObj)
return E_INVALIDARG;
else
*ppvObj = NULL;
if (riid != IID_ISequentialStream && riid != IID_IUnknown)
return E_NOINTERFACE;
AddRef();
*ppvObj = this;
return S_OK;
}
HRESULT Init(const void * pSrcData, const ULONG ulBufSize, const ULONG ulReadSize)
{
// Must have a source
if (NULL == pSrcData)
return E_INVALIDARG;
// Data length must be non-zero
if (0 == ulBufSize)
return E_INVALIDARG;
m_ulBufSize = ulBufSize;
m_ulReadSize = ulReadSize;
m_pSrcData = (BYTE *)pSrcData;
m_pReadPtr = m_pSrcData;
m_ulBytesLeft = m_ulReadSize;
m_ulReadPos = 0;
m_fWasRead = FALSE;
return S_OK;
}
// SQL Server providers (SQLOLEDB/SQLNCLI) don't allow us to write our data to them. Instead,
// they read from our object.
virtual HRESULT STDMETHODCALLTYPE Write(const void *, ULONG, ULONG * )
{
return E_NOTIMPL;
}
// This implementation simply copies data from the source buffer in whatever size requested.
// But you can do anything here such as reading from a file, reading from a different rowset, stream, etc.
virtual HRESULT STDMETHODCALLTYPE Read(void * pv, ULONG cb, ULONG * pcbRead)
{
ULONG ulBytesWritten = 0;
ULONG ulCBToWrite = cb;
ULONG ulCBToCopy;
BYTE * pvb = (BYTE *)pv;
m_fWasRead = TRUE;
if (NULL == m_pSrcData)
return E_FAIL;
if (NULL == pv)
return STG_E_INVALIDPOINTER;
while (ulBytesWritten < ulCBToWrite && m_ulBytesLeft)
{
// Make sure we don't write more than our max read size or the size they asked for
ulCBToCopy = min(m_ulBytesLeft, cb);
// Make sure we don't read past the end of the internal buffer
ulCBToCopy = min(m_ulBufSize - m_ulReadPos, ulCBToCopy);
memcpy(pvb, m_pReadPtr + m_ulReadPos, ulCBToCopy);
pvb += ulCBToCopy;
ulBytesWritten += ulCBToCopy;
m_ulBytesLeft -= ulCBToCopy;
cb -= ulCBToCopy;
// Wrap reads around the src buffer
m_ulReadPos += ulCBToCopy;
if (m_ulReadPos >= m_ulBufSize)
m_ulReadPos = 0;
}
if (pcbRead)
*pcbRead = ulBytesWritten;
return S_OK;
}
};
#endif // USE_ISEQSTREAM
HRESULT SetFastLoadProperty(IDBInitialize * pIDBInitialize)
{
HRESULT hr = S_OK;
IDBProperties * pIDBProps = NULL;
DBPROP rgProps[1];
DBPROPSET PropSet;
VariantInit(&rgProps[0].vValue);
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].dwPropertyID = SSPROP_ENABLEFASTLOAD;
rgProps[0].vValue.boolVal = VARIANT_TRUE;
PropSet.rgProperties = rgProps;
PropSet.cProperties = 1;
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDATASOURCE;
if(SUCCEEDED(hr = pIDBInitialize->QueryInterface(
IID_IDBProperties,
(LPVOID *)&pIDBProps)))
{
hr = pIDBProps->SetProperties(1, &PropSet);
}
VariantClear(&rgProps[0].vValue);
if(pIDBProps)
pIDBProps->Release();
return hr;
}
void wmain()
{
// Setup the initialization options
ULONG cProperties = 0;
DBPROP rgProperties[10];
ULONG cPropSets = 0;
DBPROPSET rgPropSets[1];
LPWSTR pwszProgID = L"SQLOLEDB";
LPWSTR pwszDataSource = NULL;
LPWSTR pwszUserID = NULL;
LPWSTR pwszPassword = NULL;
LPWSTR pwszProviderString = L"server=MyServer;trusted_connection=yes;";
IDBInitialize * pIDBInitialize = NULL;
IDBCreateSession * pIDBCrtSess = NULL;
IOpenRowset * pIOpenRowset = NULL;
IDBCreateCommand * pIDBCrtCmd = NULL;
ICommandText * pICmdText = NULL;
IAccessor * pIAccessor = NULL;
IRowsetFastLoad * pIRowsetFastLoad = NULL;
IDBProperties * pIDBProperties = NULL;
DBBINDING rgBinding[3];
DBBINDSTATUS rgStatus[3];
ULONG ulOffset = 0;
HACCESSOR hAcc = DB_NULL_HACCESSOR;
BYTE * pData = NULL;
ULONG iRow = 0;
LPWSTR pwszTableName = L"fltest";
DBID TableID;
HRESULT hr;
#ifdef USE_ISEQSTREAM
BYTE bSrcBuf[1024]; // A buffer to hold our data for streaming
memset((void *)&bSrcBuf, 0xAB, sizeof(bSrcBuf)); // Stream data value 0xAB
MySequentialStream * pMySeqStream = new MySequentialStream();
DBOBJECT MyObject = {STGM_READ, IID_ISequentialStream}; // NULL pObject implies STGM_READ and IID_IUnknown, but not recommended
#endif
memset(rgBinding, 0, ( sizeof(rgBinding) / sizeof(rgBinding[0])) * sizeof(DBBINDING) );
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = pwszTableName;
// Col1
rgBinding[0].iOrdinal = 1;
rgBinding[0].wType = DBTYPE_I4;
rgBinding[0].obStatus = ulOffset;
ulOffset+=sizeof(DBSTATUS);
rgBinding[0].obLength = ulOffset;
ulOffset+=sizeof(DBLENGTH);
rgBinding[0].obValue = ulOffset;
ulOffset += sizeof(LONG);
rgBinding[0].cbMaxLen = sizeof(LONG);
rgBinding[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBinding[0].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
//Col2
rgBinding[1].iOrdinal = 2;
rgBinding[1].wType = DBTYPE_I4;
rgBinding[1].obStatus = ulOffset;
ulOffset+=sizeof(DBSTATUS);
rgBinding[1].obLength = ulOffset;
ulOffset+=sizeof(DBLENGTH);
rgBinding[1].obValue = ulOffset;
ulOffset += sizeof(LONG);
rgBinding[1].cbMaxLen = sizeof(LONG);
rgBinding[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBinding[1].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
//Col3
rgBinding[2].iOrdinal = 3;
rgBinding[2].obStatus = ulOffset;
ulOffset+=sizeof(DBSTATUS);
rgBinding[2].obLength = ulOffset;
ulOffset+=sizeof(DBLENGTH);
rgBinding[2].obValue = ulOffset;
rgBinding[2].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH; // DBPART_LENGTH not needed for providers that don't require length
rgBinding[2].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[2].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
#ifdef USE_ISEQSTREAM
rgBinding[2].wType = DBTYPE_IUNKNOWN;
ulOffset += sizeof(ISequentialStream *); // Technically should be sizeof(MySequentialStream *), but who's counting?
rgBinding[2].cbMaxLen = sizeof(ISequentialStream *);
rgBinding[2].pObject = &MyObject;
#else
rgBinding[2].wType = DBTYPE_BYTES;
ulOffset += MAX_BLOB;
rgBinding[2].cbMaxLen = MAX_BLOB;
#endif
// Set init props
for ( ULONG i = 0 ; i < sizeof(rgProperties) / sizeof(rgProperties[0]) ; i++ )
VariantInit(&rgProperties[i].vValue);
// Obtain the provider's clsid
CLSID clsidProv;
hr = CLSIDFromProgID(pwszProgID, &clsidProv);
// Get our initial connection
CoInitialize(NULL);
if (SUCCEEDED(hr))
hr = CoCreateInstance(clsidProv, NULL, CLSCTX_ALL, IID_IDBInitialize,(void **)&pIDBInitialize);
if (SUCCEEDED(hr))
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
// DBPROP_INIT_DATASOURCE
if(pwszDataSource)
{
rgProperties[cProperties].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties[cProperties].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[cProperties].dwStatus = DBPROPSTATUS_OK;
rgProperties[cProperties].colid = DB_NULLID;
rgProperties[cProperties].vValue.vt = VT_BSTR;
V_BSTR(&rgProperties[cProperties].vValue) = SysAllocString(pwszDataSource);
cProperties++;
}
// DBPROP_AUTH_USERID
if(pwszUserID)
{
rgProperties[cProperties].dwPropertyID = DBPROP_AUTH_USERID;
rgProperties[cProperties].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[cProperties].dwStatus = DBPROPSTATUS_OK;
rgProperties[cProperties].colid = DB_NULLID;
rgProperties[cProperties].vValue.vt = VT_BSTR;
V_BSTR(&rgProperties[cProperties].vValue) = SysAllocString(pwszUserID);
cProperties++;
}
// DBPROP_AUTH_PASSWORD
if(pwszPassword)
{
rgProperties[cProperties].dwPropertyID = DBPROP_AUTH_PASSWORD;
rgProperties[cProperties].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[cProperties].dwStatus = DBPROPSTATUS_OK;
rgProperties[cProperties].colid = DB_NULLID;
rgProperties[cProperties].vValue.vt = VT_BSTR;
V_BSTR(&rgProperties[cProperties].vValue) = SysAllocString(pwszPassword);
cProperties++;
}
// DBPROP_INIT_PROVIDERSTRING
if(pwszProviderString)
{
rgProperties[cProperties].dwPropertyID = DBPROP_INIT_PROVIDERSTRING;
rgProperties[cProperties].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[cProperties].dwStatus = DBPROPSTATUS_OK;
rgProperties[cProperties].colid = DB_NULLID;
rgProperties[cProperties].vValue.vt = VT_BSTR;
V_BSTR(&rgProperties[cProperties].vValue) = SysAllocString(pwszProviderString);
cProperties++;
}
if(cProperties)
{
rgPropSets[cPropSets].cProperties = cProperties;
rgPropSets[cPropSets].rgProperties = rgProperties;
rgPropSets[cPropSets].guidPropertySet = DBPROPSET_DBINIT;
cPropSets++;
}
// Initialize
if (SUCCEEDED(hr))
hr = pIDBProperties->SetProperties(cPropSets, rgPropSets);
if (SUCCEEDED(hr))
hr = pIDBInitialize->Initialize();
if (SUCCEEDED(hr))
{
printf("\tConnected!\r\n");
}
else
printf("Unable to connect\r\n");
// Set fastload prop
if (SUCCEEDED(hr))
hr = SetFastLoadProperty(pIDBInitialize);
if (SUCCEEDED(hr))
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&pIDBCrtSess);
if (SUCCEEDED(hr))
hr = pIDBCrtSess->CreateSession(NULL, IID_IOpenRowset, (IUnknown **)&pIOpenRowset);
if (SUCCEEDED(hr))
hr = pIOpenRowset->OpenRowset(NULL, &TableID, NULL, IID_IRowsetFastLoad, 0, NULL, (IUnknown **)&pIRowsetFastLoad);
if (SUCCEEDED(hr))
hr = pIRowsetFastLoad->QueryInterface(IID_IAccessor, (void **)&pIAccessor);
if (SUCCEEDED(hr))
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 3, rgBinding, ulOffset, &hAcc, (DBBINDSTATUS *)&rgStatus);
if (SUCCEEDED(hr))
{
pData = (BYTE *)malloc(ulOffset);
for (iRow = 0 ; iRow < MAX_ROWS ; iRow++)
{
// Column 1 data
*(DBSTATUS *)(pData + rgBinding[0].obStatus) = DBSTATUS_S_OK;
*(DBLENGTH *)(pData + rgBinding[0].obLength) = 1234567; // Ignored for I4 data
*(LONG *)(pData + rgBinding[0].obValue) = iRow;
// Column 2 data
*(DBSTATUS *)(pData + rgBinding[1].obStatus) = DBSTATUS_S_OK;
*(DBLENGTH *)(pData + rgBinding[1].obLength) = 1234567; // Ignored for I4 data
*(LONG *)(pData + rgBinding[1].obValue) = iRow + 1;
// Column 3 data
*(DBSTATUS *)(pData + rgBinding[2].obStatus) = DBSTATUS_S_OK;
*(DBLENGTH *)(pData + rgBinding[2].obLength) = MAX_BLOB/(iRow + 1); // Not needed for providers that don't require length
#ifdef USE_ISEQSTREAM
// DBLENGTH is used to tell the provider how much BLOB data to expect from the stream, not required
// if provider supports sending data without length
*(ISequentialStream **)(pData+rgBinding[2].obValue) = (ISequentialStream *)pMySeqStream;
pMySeqStream->Init((void *)&bSrcBuf, sizeof(bSrcBuf), MAX_BLOB / (iRow + 1)); // Here we set the size we will let the provider read
pMySeqStream->AddRef(); // The provider releases the object, so we addref it so it doesn't get destructed
#else
memset(pData+rgBinding[2].obValue, 0, MAX_BLOB); // Not strictly necessary
memset(pData+rgBinding[2].obValue, 0x23, MAX_BLOB/(iRow + 1));
#endif
if (SUCCEEDED(hr))
hr = pIRowsetFastLoad->InsertRow(hAcc, pData);
}
}
if (SUCCEEDED(hr))
hr = pIRowsetFastLoad->Commit(TRUE);
if (hAcc)
pIAccessor->ReleaseAccessor(hAcc, NULL);
SAFE_RELEASE(pIDBInitialize);
SAFE_RELEASE(pIDBCrtSess);
SAFE_RELEASE(pIOpenRowset);
SAFE_RELEASE(pIDBCrtCmd);
SAFE_RELEASE(pICmdText);
SAFE_RELEASE(pIAccessor);
SAFE_RELEASE(pIRowsetFastLoad);
SAFE_RELEASE(pIDBProperties);
#ifdef USE_ISEQSTREAM
SAFE_RELEASE(pMySeqStream);
#endif
if (pData)
free(pData);
CoUninitialize();
return;
}
CoDe Magazine recently published a special issue that features the work of the Data Programmability product unit at Microsoft.
Links to all the articles are here http://www.code-magazine.com/focus/index.aspx
Of special interest to developers who use SQL Server Native Client are the articles by Vaughn Washinton "Programming SQL Server 2008" at http://www.code-magazine.com/Article.aspx?quickid=990712122 and my article "ODBC Rocks!" at http://www.code-magazine.com/Article.aspx?quickid=990712172
Chris Lee
Program Manager, Microsoft SQL Server Native Client
