CRecordSet

Problem – a row is failing to be fetch with a "data truncated" error. The column that’s giving the trouble is a varchar field with a possible length greater than 255 in the database and a CString in the CRecordSet derived class. RFX_TEXT defines a default size limitation of 255. To get around the error, simply assign the size required by the column. For example, assume that [comment] is a varchar 256. Most of the time this wouldn’t be an issue but every once in a while someone is bound to fill the entire column to the max. That’s when the unhealthy Bang! Comes tumbling out of the computer. The fix would be to over ride the default size parameter in the RFX_Text file. In the field exchange section

RFX_Text(pFX, _T("[comment]"), m_comment );

RFX_Text(pFX, _T("[comment]"), m_comment, 256);

Getting Identity Field Value On Record Insert

Here’s the problem in a nutshell: You have a database column defined as an identity column. It gets loaded with a unique value at the time that the row is inserted. The normal CRecordset Add/Update pair doesn’t return this value. How do you get it? One way would be to construct a GUID or some other guaranteed unique value and explicitly include it in the new row’s data. After the insert, you can search for the row that contains this unique value and that will give you the record set that contains the database added identity column. Another way is to construct a stored procedure that will insert the row for you and return the identity value back to the stored procedure wrapper class.

As an example, let’s assume that you create a stored procedure that looks something like this:

CREATE PROCEDURE sp_InsertRec @dtWhen datetime, @strName varchar, @iID int OUTPUT
AS
INSERT INTO MyTable( colWhen, colName ) VALUES(@dtWhen, @strName )
IF @@ERROR = 0
BEGIN
SELECT @iID = @@IDENTITY
RETURN 0
END
ELSE
BEGIN
SELECT @iID = 0
RETURN -100
END

Handling exceptions and errors

Using 'LIKE'

(Supply a list of the LIKE syntax rules and some examples of how to use them)

There are times when the special pattern characters need to be treated as literals.The following example is looking for a match to a string that starts with a single "_".

Using native SQL

Using Stored Procedures

Returning Values From Stored Procedures

Passing values in to a stored procedure is blindingly easy. Getting string values back is just pretty easy, too, although you will have to crack at least one of your eyes open a little bit to do it. The key on the stored procedure side is to mark the argument as an output. The key on the C++ side is to keep the argument order in the DoFieldExchange in the same order in which the arguments are specified in the stored procedure and to mark the variables that are to receive values as an ‘outputParam’.

Here’s an example. Let’s assume that we have a table with two columns, ‘Name’ and ‘Value’, both of which are defined as varchar(80). Let’s further assume that we want a stored procedure to extract a particular value based upon a specified name. That stored procedure will look something like:

Create procedure sp_GetValue @arg_name varchar(80), @arg_value varchar(80) OUTPUT as
select @arg_value=value from tblNameValues where name = @arg_name

By the way, if you want to test your stored procedure from SQL Query Analyzer before getting into the additional complications imposed by MFC, you can very easily do so with this sort of format:

Declare @iReturn as integer
Set @iReturn = -1
declare @arg_value as varchar(80)
Set @arg_value = 'Before call'
exec @iRet = sp_GetValue ‘some name’, @arg_value output
select @iRet iRet, @arg_value arg_value

Back to MFC. We now expect that @arg_value is going to get loaded with whatever matches up with the passed in name. Back in the C++ world, the caller will use

CString sp_GetValue::GetDefaultSQL()
    {
    return _T("{? = CALL sp_GetValue(?,?)}");
    }

void sp_GetValue::DoFieldExchange(CFieldExchange* pFX)
    {
    // RFX output param mapping
    pFX->SetFieldType(CFieldExchange::outputParam);
    RFX_Long(pFX, _T("[@RETURN_VALUE]"), m_RETURN_VALUE);

    pFX->SetFieldType(CFieldExchange::inputParam);
    RFX_Text(pFX, _T("[@arg_name]"), m_csArgName );

    pFX->SetFieldType(CFieldExchange::outputParam);
    RFX_Text(pFX, _T("[@arg_value]"), m_csArgValue );
    }