SqlWriteText%

Sends a text or image value to SQL Server.

Syntax

SqlWriteText% ( sqlconn%, objname$, textptr$, textptrlen%, timestamp$,
log%, size&, text$ )

where

sqlconn% ( )

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

objname$

Is the database table name and column name. The table name and the column name are separated by a period.

textptr$ ( )

Is the text or image value to be modified. This identifier can be obtained by calling SqlTxPtr$.

textptrlen%

Is a parameter. This parameter is included for future compatibility. For now, its value must be the constant SQLTXPLEN.

timestamp$ ( )

Is the text timestamp for the text or image value to be modified. This identifier can be obtained by calling SqlTxTimestamp$. The value changes whenever the text or image value itself is changed. This string cannot be empty.

log%

Is a Boolean value that specifies whether this SqlWriteText% operation should be recorded in the transaction log.

size&

Is the total size, in bytes, of the text or image value to be written.

text$

Is a string containing the text or image to be written. If this string is empty, DB-Library for Visual Basic expects the application to call SqlMoreText% one or more times until all size& bytes of data have been sent to SQL Server. No single data block can be larger than 64K.

Returns

SUCCEED (1) or FAIL (0).

Remarks

SqlWriteText% is used to update text and image values, allowing an application to send long values to SQL Server without having to copy them into a Transact-SQL UPDATE statement. In addition, SqlWriteText% gives an application access to the text timestamp mechanism, which can be used to ensure that two competing users do not inadvertently wipe out each other's modifications in the database.

SqlWriteText% succeeds only if its timestamp$ parameter, usually obtained when the column's value is originally retrieved, matches the text column's timestamp in the database. If a match occurs, SqlWriteText% updates the text column and at the same time updates the column's timestamp. This has the effect of governing updates by competing applications ¾ an application's SqlWriteText% call fails if a second application has updated the text column between the time the first application retrieved the column and the time it made its SqlWriteText% call.

SqlWriteText% is similar to a Transact-SQL WRITETEXT statement. However, calling SqlWriteText% is usually more efficient than sending a WRITETEXT statement through the command buffer. (For information about WRITETEXT, see the Microsoft SQL Server Transact-SQL Reference.)

SqlWriteText% can be invoked with or without logging in, according to the value of the log% parameter. To use SqlWriteText% with logging turned off, the SQL Server option select into/bulkcopy must be set to TRUE by executing the following system procedure:

sp_dboption 'mbsql', 'select into/bulkcopy', 'true'

For more information about SQL Server options, see the Microsoft SQL Server Transact-SQL Reference and the Microsoft SQL Server Administrator's Companion.

SqlWriteText%, used in conjunction with SqlMoreText%, also enables an application to send a large text or image value to SQL Server in the form of a number of smaller chunks. This is particularly useful with operating systems that are unable to allocate extremely long data buffers.

When SqlWriteText% is used with SqlMoreText%, it locks the specified database text column, and the lock is not released until the final SqlMoreText% has sent its data. This ensures that a second application does not read or update the text column in the middle of the first application's update.

If the text$ string is not an empty string, SqlWriteText% executes the data transfer from start to finish, including any necessary calls to SqlOk% and SqlResults%. To send a text or image value in chunks rather than sending the whole value at once, set the text$ parameter to an empty string. SqlWriteText% returns control to the application immediately after notifying SQL Server that a text transfer is about to begin. The actual text is sent to SQL Server with SqlMoreText%, which can be called multiple times, once for each chunk.

Examples

The following code fragment uses SqlWriteText%:

A.    'Retrieve a record from the "abstract" text column.
    cmd$ = "SELECT abstract FROM articles"
    cmd$ = cmd$  " WHERE article_id = 1000"
    Result% = SqlCmd%(Sqlconn%, cmd$)
    Result% = SqlExec%(Sqlconn%)
    Result% = SqlResults%(Sqlconn%)
    Result% = SqlNextRow%(Sqlconn%)
    'Update the text column.
    Abstract$ = "A brand new text value."
    Result% = SqlWriteText%(UpdSqlconn%, "articles.abstract",_
       SqlTxPtr$(Sqlconn%, 1), SQLTXPLEN,_ 
       SqlTxTimestamp$(Sqlconn%, 1), 1,_ 
       LEN(Abstract$), Abstract$)

    The following code fragment uses SqlWriteText% with SqlMoreText%.     Notice the required calls to SqlOk% and SqlResults% between the call to     SqlWriteText% and the first call to SqlMoreText% and after the final call to     SqlMoreText%.

B.    'Retrieve a record from the "abstract" text column.
    cmd$ = "SELECT abstract FROM articles"
    cmd$ = cmd$  " WHERE article_id = 1000"
    Result% = SqlCmd%(Sqlconn%, cmd$)
    Result% = SqlExec%(Sqlconn%)
    Result% = SqlResults%(Sqlconn%)

    Result% = SqlNextRow%(Sqlconn%)

    'Update the text column.
    Abstr1$ = "A brand new text value."
    Abstr2$ = " This text value contains two sentences."
    Result% = SqlWriteText%(UpdSqlconn%, "articles.abstract",_
       SqlTxPtr$(Sqlconn%, 1), SQLTXPLEN,_ 
       SqlTxTimestamp$(Sqlconn%, 1), 1,_ 
       LEN(Abstr1$  Abstr2$), "")
    Result% = SqlOk%(UpdSqlconn%)
    Result% = SqlResults%(UpdSqlconn%)

    'Send the update value in chunks.
    Result% = SqlMoreText%(UpdSqlconn%, LEN(Abstr1$), Abstr1$)
    Result% = SqlMoreText%(UpdSqlconn%, LEN(Abstr2$), Abstr2$)
    Result% = SqlOk%(UpdSqlconn%)
    Result% = SqlResults%(UpdSqlconn%)

These examples specify datatypes for Windows.

See Also

SqlMoreText%, SqlTxPtr$, SqlTxTimeStamp$, SqlTxTsNewVal$, SqlTxTsPut%