dtl

Binding Very Long Strings in DTL

It is very convenient to be able to bind string fields in DTL using std::string rather than having to rely on character arrays. By default, DTL binds std::string to a database by using SQL_VARCHAR for the source ODBC datatype. Unfortunately, doing this means that we are binding to the default character type in the target database which may have length limitations. For example, the maximum length of a VARCHAR field in Access (text field) and MySQL is 255, in Oracle the maximum legth for a VARCHAR is 2000, and SQL Server has a maximum of 4096. Fortunately, most databases have a type for longer length strings (VARCHAR2 in Oracle, memo field in Access, etc.) which corresponds to the SQL_LONGVARCHAR type in ODBC. The reason we don't bind using SQL_LONGVARCHAR by default is if we did that we would get errors when the underlying data field is just a regular VARCHAR or short string.

DTL provides a default string type of SQL_VARCHAR with a maximum column size of 255 for Access and 2000 for Oracle and SQL Server. If these defaults don't satisfy your needs, either or both of these values may be changed in the relevant BCA as follows:

For each std::string field that the defaults won't suffice for:

1. First bind the column as usual, boundIOs[colName] == rowbuf.stringField

2. To change the SQL type DTL uses for binding a particular field call boundIOs[colName].SetSQLType(sqlType) where sqlType is SQL_LONGVARCHAR (or whatever type you wish to use instead of SQL_VARCHAR).

3. To override the maximum size of the SQL column being used, call boundIOs[colName].SetColumnSize(newColumnSize) where newColumnSize is the maximum string size that you wish to use.

For std::wstrings, SQL_WVARCHAR may similarly need to be changed to SQL_LONGWVARCHAR.

The example below shows the use of a very long string:

// object which we'll write long strings with ... will bind Test::str to LONG_EXAMPLE.STRING_VALUE
struct Test {
	string str;
};

// this BCA for Access overrides the sql type for STRING_VALUE to long varchar and the column size to 500

class BCAAccess : public binary_function<BoundIOs &, Test &, void>
{
public:
	void operator()(BoundIOs &boundIOs, Test &rowbuf)
	{
		boundIOs["STRING_VALUE"] == rowbuf.str;
		boundIOs["STRING_VALUE"].SetSQLType(SQL_LONGVARCHAR);
		boundIOs["STRING_VALUE"].SetColumnSize(500);
	}
};

void InsLong() {
	 PrintHeader(cout, "InsLong()");
	 Test rowbuf, rowbuf_read;
	 DBStmt stmt("DELETE FROM LONG_EXAMPLE");
	 stmt.Execute();
	 DBConnection::GetDefaultConnection().CommitAll();
	
	 DBView<Test> view;

    
	 // on Access the defaults won't suffice, co we must use BCAAccess() there to set the SQL type to long varchar and
     // the column size to 500, our maximum string length

	 if (DBConnection::GetDefaultConnection().GetDBMSEnum() !=
		 DBConnection::DB_ACCESS)
	 {
		view = DBView("LONG_EXAMPLE", BCA(rowbuf, COLS["STRING_VALUE"] == rowbuf.str));
	 }
	 else
	 {
		view = DBView("LONG_EXAMPLE", BCAAccess());
	 }

	 DBView<Test>::insert_iterator it = view;

     // generate a very long string
	 for (int i=0 ; i < 500; ++i)
		rowbuf.str += char('0' + i%10 - 1);


	 *it = rowbuf;
	 ++it;

	 DBConnection::GetDefaultConnection().CommitAll();

	 DBView<Test>::select_iterator read_it = view.begin();

	 rowbuf_read = *read_it;

	 if (rowbuf.str != rowbuf_read.str)
	 {
		 cout << "<<<<< Error with long strings! >>>>>" << endl;
		 cout << "String written ..." << endl;
		 cout << rowbuf.str << endl;
		 cout << "String read ... " << endl;
		 cout << rowbuf_read.str << endl;
		 rowbuf_read.str = "error";
	 }
	 else
	 {
		 cout << "Long string test successful!" << endl;
	 }

	 PrintSeparator(cout);
}

Copyright © 2002, Michael Gradman and Corwin Joy.

Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

This site written using the ORB.