Xbase and ADT tables NULL value

[skiman]

Hi,

We were planning to convert our tables to Advantage ADT files. We thought we could convert and use the same source with some minor modifications, it's compatible with ADSDBE, and to move to a complete SQL syntax at the end.

Compatible seems to be something different in Belgium.

The following is working:
customer->(dbappend())
customer->number := 123
customer->name := cName
....
So good so far, but now the problem starts for each field which isn't replaced after the append.

Suppose there is a numeric field customer->totalsale, this remains a NULL field. So if you afterwards have a report where you want to print customer->totalsale you can't! You have to check if this field is a numeric or NULL field.

Suppose there is a character field customer->remark which isn't replaced with space(xx) after the append, it remains a NULL field. If you have valtype(customer->remark) it won't give Character afterwards.

In our software we have hundreds of fields which aren't filled at the moment of the record creation. They are filled when there is data for it. With these NULL fields, our ADT table doesn't give the same result as DBF tables.

Anyone who have tried this? Is this a problem with SQL-Express?

I know you can define 'default value' in your table, but this should be done for every field? And what happens if a new field is added to an existing table. The existing records have a NUL value, only new records are getting the default value.

Also the ACE function to convert a DBF to ADT file is creating the ADT table with NULL fields for all the empty character fields. Strange enough, it doesn't create NULL fields for numeric fields. On the other hand, if you append(), a numeric is a NULL field.

[Tom]

Hi, Chris.

a) Use the data dictionary to set default values for those fields OR:
b) use SET NULLVALUE OFF if you have Xbase++ 1.9 or later

[Tom]

Add: If you use SQLexpress and nothing else to manage the tables, there is at least no DBE active while dealing with the data. SET NULLVALUE OFF will have no effect.

[rdonnay]

I use the function IsNull() in my replacement for FieldWBlock().

Code: Select all

FUNCTION DC_FieldWBlock( cFieldName, xDataSource, bFormat, aStru )

LOCAL cAlias, bGetSet, oCol, nLen, nDec, cDataType := Valtype(xDataSource), ;
      cType, xValue, nPos

IF xDataSource == NIL .OR. cDataType == 'C' // alias

  IF xDataSource == NIL
    cAlias := Alias()
  ELSE
    cAlias := xDataSource
  ENDIF

  IF Empty(aStru)
    aStru := (cAlias)->(dbStruct())
  ENDIF

  nPos := (cAlias)->(FieldPos( cFieldName ))
  cType := aStru[nPos,2]
  nLen := aStru[nPos,3]
  IF cType $ 'CMT'
    xValue := "Space(" + Alltrim(Str(nLen)) + ")"
  ELSEIF cType $ 'NI'
    xValue := "0"
  ELSEIF cType == 'D'
    xValue := "Ctod('')"
  ELSEIF cType == 'L'
    xValue := ".f."
  ELSE
    xValue := "''"
  ENDIF

  IF '->' $ cFieldName

    bGetSet :=  &( '{|x|IIF(x==NIL .OR. x==' + cFieldName + ',' + ;
                'IsNull(' + cFieldName +',' + xValue + '),' + cFieldName + ':=x)}' )
  ELSE

    bGetSet := &( '{|x|IIF(x==NIL .OR. x==' + cAlias + '->' + cFieldName + ',' + ;
           'IsNull(' + cAlias + '->' + cFieldName + ',' + xValue + '),' + cAlias + '->' + cFieldName + ':=x)}' )

  ENDIF

ELSEIF cDataType == 'N' // ADS SQL cursor

   bGetSet := DC_AdsFieldBlock( xDataSource, cFieldName, bFormat )

ELSEIF cDataType == 'O' // SQLexpress cursor

  oCol := xDataSource:getSQLColumn(cFieldName)
  nLen := oCol:length

  IF oCol:ValType == "C"

    bGetSet := {|x|if(PCount()==0,PadR(xDataSource:fieldGet(cFieldName),nLen),;
                  xDataSource:fieldPut(cFieldName,Trim(x)))}

  ELSEIF oCol:ValType == "N"

    nDec := oCol:decimals

    bGetSet := {|x|if(PCount()==0,Str(xDataSource:fieldGet(cFieldName),nLen,nDec),;
                  xDataSource:fieldPut(cFieldName,val(x)))}

  ELSE

    bGetSet := {|x|if(PCount()==0,xDataSource:fieldGet(cFieldName),;
               xDataSource:fieldPut(cFieldName,x))}

  ENDIF

ENDIF

RETURN bGetSet