Sybase: Update TEXT/IMAGE columns using dbwritetext

Similar to the writetext command, you can use the dbwritetext function of the Open Client DB Library to update a text or image column.

Here is a short sample how to do it:

// Defines the entry point for the console application.
//

#include "stdafx.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <sybfront.h>
#include <sybdb.h>

LOGINREC     *login; 
DBPROCESS    *dbproc; 
DBCHAR       mytextcol[512]; 

int _tmain(int argc, _TCHAR* argv[])
{
	if (dbinit() == FAIL) 
		exit(ERREXIT);
	
	// Create a login  record and fill it with username, password and application name.
	login = dblogin(); 
	DBSETLUSER(login, "myusername");
	DBSETLPWD(login, "mypassword"); 
	DBSETLAPP(login, "myapplicationname"); 

	// Open a connection to the server using the login record.
	dbproc = dbopen(login, "myservername"); 

	// You need to first select the text/image field in order to be able to get the pointer.
	dbcmd(dbproc, "select mytextcol from mydb..mytable where myuid = '582289'"); 
	dbsqlexec(dbproc); 
	dbresults(dbproc); 
	dbbind(dbproc, 1, STRINGBIND, (DBINT) 0, (BYTE *)&mytextcol); 
	dbnextrow(dbproc); 

	// This is the new value for the field.
	strcpy(mytextcol, "My update text value."); 

	// Now write it with dbwritetext
	dbwritetext (dbproc, "mydb..mytable.mytextcol", dbtxptr(dbproc, 1), DBTXPLEN, dbtxtimestamp(dbproc, 1), TRUE, (DBINT)strlen(mytextcol), (BYTE *)&mytextcol); 

	// The end...
	dbexit(); 
	return 0;
}

myservername is not the hostname of the server but the name in the Sybase configuration (you can see it using dsedit).
TRUE: it means that the operation should be logged. If you set it to FALSE, the operation will be minimally logged which is suboptimal in case of media recovery (but is faster and doesn’t fill the transaction log).

You need to additionally set the following in your visual C++ project:
Additional Include Directories: “C:\sybase\OCS-15_0\include”
Additional Library Directories: “C:\sybase\OCS-15_0\lib”;”C:\sybase\OCS-15_0\dll”
Additional Dependencies: libsybct.lib libsybdb.lib

The advantage of using dbwritetext are:

  • It’s fast.
  • It can handle large volumes of data (up to 2GB).

The disadvantages are:

  • It does not activate insert or update triggers.
  • If you have a timestamp column in the table it’s value will not be updated when using dbwritetext

If the program crashes on dbbind, check whether your SQL-Select is right (I had a typo in the selected column name and wasted half an hour wondering why it was crashing).

Leave a Reply

Your email address will not be published.