I assume you are using ADO for MySql.
In the recent versions TDataRow() validates field type while assigning a new value. Because this check was not implemented in earlier versions you were not getting this runtime error.
One way to disable the type check is to set oRec:lTypeCheck := .f., but this is not safe. This is provided for arrays and sqlite3.
oRec := TDataRow():New( oRs )
oRec:lTypeCheck := .f.
EditDlg( oRec )
But I do not advise this unsafe approach.
Normal practice is to use "BIT" field type to create a BOOLEAN (logical) datatype in most sql databases, eg. MS Access, MS Sql, MySql, Sqlite3, etc., but not TINYINT. ADO recognizes BIT datatype as ADO Type adBoolean. FWH libraries also recognize adBoolen type as Logical field. TINYINT field is recognized as numeric field by ADO and consequently by FWH.
If we use FWAdoCreateTable(...) function to create tables, this function takes care of choosing the DataType most appropriate to the DBF Type for the target SQL database, be it Access, MSSql, MySql, Oracle, etc. It is for this reason we recommend using this function for creating tables in sql databases.
Now, let us see why you were not (apparently) having a problem with using TinyInt fields for logical values. When we assign .T. or .F. to a field, ADO converts the values is -1 or 0 and assigns to the underlying field. So we do not get an error when we assign a logical value to a numeric field.
Interestingly CHECKBOX also does not complain when we start with a numeric variable. So it seems ok using an INT (tinyint or other) field to hold logical values. I would say it is okay if the programmer handles numeric/logical conversions with good care.
You mentioned that in earlier versions it was working allright. You were not getting runtime errors but I doubt if everything was "allright". Please check the following using the old version.
Let us assume that the VOLUME field is having value 0 to start with.
Now the CHECKBOX shows false in the dialog and the user checks the checkbox. When saved the value of VOLUME field is -1.
Try reading the data again and look at the checkbox. ( Does not matter whether you use TDataRow or directly use the recordset). CHECKBOX again shows FALSE, though the value of VOLUME field is -1. The user sees it as FALSE even though VOLUME field is not empty.
You can even test this with
REDEFINE CHECKBOX oGet VAR oRs:Fields( "VOLUME" ):Value ID 4002 OF oDlg
So, we thought everything was alright but in fact was not alright. Revised DataRow brought out the problem.
Best and the safest way is to covert all logical fields to BIT type instead of TINYINT or INT type. I know there are programmers who use INT to hold logical values but in their application code they take care of logical/numeric conversions, which I consider unnecessary and prone to errors. (Oracle programmers mostly use 'Y', 'N' )
If for some reasons we need to retain TINYINT (or any other) field type, I suggest this code.
- Code: Select all Expand view
local lTmp := .t.
REDEFINE CHECKBOX oGet VAR lTmp ID 4002 OF oDlg
oGet:bSetGet := { |x| If( x == nil, !Empty( oRec:Volume ), oRec:Volume := If( x, -1, 0 ) ) }
So,
1) It is better to use BIT fieldtype than TINYINT for logical values. In the longrun, it is desirable to convert TINYINT as BIT fields.
2) It is safer to use FWAdoCreateTable(...) function to create tables.
3) DataRow is not big obstacle. We can get over with setting lTypeCheck to .f. but we still have the problems of using Int for logical fields
4) If you still want to use Int (of any size) as logical fields, you should be prepared to put in more programming efforts to handle numeric/logical conversions while accessing.
