ADO : Building columns , NULL ... DEFAULT

[Franklin Demont]

Hello ,

Working with ADO i am trying to understand field definitions as :

CREATE TABLE TestTable ( ID COUNTER PRIMARY KEY ,
[FIELD2] CHAR(20) NOT NULL DEFAULT 'Fivewin power' ,
[FIELD3] NUMERIC(10,0) DEFAULT 0 )

Code: Select all  Expand view

   oRS:AddNew()
   a := {1,nil,0}
   FOR i := 1 TO oRs:Fields:count
       WITH OBJECT oRs:Fields(i-1)
            ? :Value == nil   // Give 3 times .T. , value from each field seems to be nil
            try
              :Value = a[ i ]
            catch
              ? " Error on field " + :Name , a[i]
            end  
       END
   NEXT
   oRs:Update()
   FOR i := 1 TO oRs:Fields:count
       WITH OBJECT oRs:Fields(i-1)
            ? :Value , :Value == nil , VALTYPE(:Value)
       END
   NEXT

 

1) Default value has no effect. I expected the value from the second field to be 'fivewin power'
2) Second field has clause NOT NULL , but value nil is accepted.
The field has now a value SPACE(20) , same result with Clause NULL
3) Third field ? :Value --> 0.00 , expected 0 , maybe configuration from the aplication (SET DECIMALS)
Exact value can be retrieved using oField:Precision
It seems that on this way the clauses (NOT) NULL and DEFAULT have no effect.
Can this clauses be retrieved from ADO ? If not they are useless in the fivewin aplication.

Frank

[nageswaragunupudi]

Assigning nil is equivalent to assigning NULL.
When we assign NULL, server does not use Default value. Server thinks that we wanted the value to be NULL overriding the default value.

So it is desirable to assign DEFAULT. This can be done either by omitting the assignment in SQL statement of specifically assigining DEFAULT.

Now, how do we do it in ADO. Instead of using NIL, use AdoDefault().
AdoDefault() and AdoNull() are functions provided by FWH for this purpose.

[Franklin Demont]

Rao ,

I am not sure that you answered the question .

Building the table with
CREATE TABLE TestTable ( ID COUNTER PRIMARY KEY ,
[FIELD2] CHAR(20) NOT NULL DEFAULT 'Fivewin power' , ... )
....

oRs:AddNew()

FIELD2 must become the value "Fivewin power" .

Will Ors:Fields("FIELD2"):Value := AdoDefault() do so ?
or does it the same as AdoNull() ?

This function is not present in FWH1404.
( I don't use FWH for comercial purposes , ony to make a new version from FWHDBU)

I found this two functions in ADORDD.PRG from 08.12.2015 , but adonull gives a nil value , adodefault a error

How can give Adodefault this Defaultvalue ? How can it read this value from the ors or connection class ?

Frank

[Rick Lipkin]

Frank

I am not familiar with "Default" .. I am a bit old fashioned and like to write out my code without taking too many shortcuts .. understand Rao has created some VERY nice Ado wrappers and can simplify the code below .. however, Consider this example:

Code: Select all  Expand view

// Assuming you have a connection to the database oCn

// easier to read if you code the Create Table this way

cSql :=  "CREATE TABLE [TestTable] "
cSql +=  "( "
cSql += "[Id]  COUNTER NOT NULL, "
cSql += "[Field2]        char(20) NULL, "
cSql += "CONSTRAINT PK_TESTTABLE PRIMARY KEY ( ID )"
cSql += " )"

Try
   oCn:Execute( cSQL )
Catch
   MsgInfo( "Create Table TESTTABLE Failed" )
   oCn:CLose()
   Return(.f.)
End try

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT * from [TestTable]"

TRY
  oRS:Open(cSQL,oCn )
CATCH oErr
  MsgInfo( "Error in Opening TESTTABLE table" )
  oCn:Close()
  RETURN(.F.)
END TRY

oRs:AddNew()
oRs:Fields("Field2"):Value := "Fivewin power"
oRs:Update()

oRs:CLose()
oCn:CLose()

Return(.t.)
 

Rick Lipkin

[nageswaragunupudi]

Rick

He is writing a generic DBU.
How does he know what value to be assigned?

oRs:Fields("Field2"):Value := "Fivewin power"

How does he know that the default value is "Fivewin power" ?
That is the question he is asking.

[Franklin Demont]

Rao ,

ADO has not the possibility to retrieve the Default value ? Wright ?

It is not difficult to build DBU , writing the Default values in an ini-file when the table is created with DBU.

But opening an ADO file , created outside DBU , the default value can't be retrieved (i suppose)

Frank

[nageswaragunupudi]

ADO does not retrieve default values.

ADO converts all Save()s into INSERT or UPDATE SQL statements and executes on the server. Assigning a NULL value also over-rides default. Through SQL, the ways to write default values are (1) do not specify a value for the field or (2) specify DEFAULT as value for the field.

Eg:
INSERT INTO TestTable ( [field1], [field2] ) VALUES ( somevalue, DEFAULT )

or

INSERT INTO TestTable( [field1] ) VALUES ( somevalue ) // We do not list ID and field2 here

Now how to do this with ADO?
We have seen that assigning a value of NIL and saving is equivalent to specify NULL in the INSERT sql statement. So default value is not used.

So,
oRs:AddNew()
Assign fields with nil or some value
oRs:Save()

does not work.

Only way is:

oRs:AddNew( aFields, aValues ) // oRs:Save() is not required for this usage
without including autoinc and default value fields in the list (array)

Eg: oRs:AddNew( { "field1" }, { 24 } ) // 24 is some value. We did not include ID and FIELD2 in this list.
This writes correctly to the database assiging "Fivewin power" to FIELD2 and also autoincrementing ID.

Still our client does not know what are the values written,
We need to re-read the values written with
either
oRs:ReSync( 1, 2 )
or
oRs:Requery()

Unfortunately Resync() does not work perfectly with all databases. Works perfectly with MSSQL
and Requery() is slow but works in all cases

Tested just now. Adodefault() is not working.
As to the code we can always share with you wherever necessary. But this is not working here.
May I know are you developing in Harbour or xHarbour?

[nageswaragunupudi]

It is not difficult to build DBU , writing the Default values in an ini-file when the table is created with DBU.

Assigning default values explicitly is not proper. This is the job of the server and we should let the server do it.
The way is to use AdoNew( <fields>, <values> )