Date fields incompatible on Paradox and Sql server

[Karen99]

I have a program that uses Paradox as the db. I must make it compatible so that it also runs on Sql Server. Everything looks fine except I have a problem with my date and time fields. Paradox only has Tdate and Ttime fields and Sql Sever only have Tdatetime fields. I use persistent fields on my table component. Delphi gives me an error 'Incompatible field types'. I understand why, but I don't know how to fix this. I tried using setfieldtype, but it only works for tblob fields.

Does anybody have any idea how I can get around this problem?

Thanx

 

[towerbase]

What is the line of code that gives the error message?

Andrew

 

[Karen99]

I it when I open my table. Ttable.open; If I added my persistend field in design time connecting to paradox that field's datatype would be ftdate and if I added it in Sql Sever it is ftdatetime.

This I understand and it makes sense. But I would like to know if I can change my persistend field's datatype or update it at runtime, so that it won't give me an error. Because I switch between the databases the whole time.

 

[DeerBear]

Hi Karen99,

I would suggest you to use dynamic fields.
As you probably know, Paradox and SqlServer use
different date formats.

Specifically, in SqlServer there are lots of
settings that influence the date format.

I would suggest you use dynamic fields and
ANSI standard format to be sure about compatibility.

Cheers,

Andrew

 

[Karen99]

I can not use dynamic fields, because the application is already written. I am talking here about 408294 lines of code and 700 table components. The application was originally written for paradox and I must convert it so that it can also run on sql server.

So can anybody come up with another idea ?

But thanks anyway DeerBear.

 

[DeerBear]

Hi Karen99,

You're welcome

Well, what you said made me have an idea.

You are using persistent fields, right?

Each TField descendant has an ASString property.
Now, you can use AsString with an ANSI date to
overcome the problem instead of using an ASDate
value.

Cheers,

Andrew

 

[Karen99]

Andrew,

I am getting the incompatible error when I open my table. Thus the table component verify the datatypes against the db when it is opened. I thought about adding 2 persistent fields one Tdate and one Tdatetime. And when I'm connected to paradox, I will set the Tdatetime field .dataset = nil and naturally the other way around - if I'm connected to Sql Server I will set the TDate field .dataset = nil. And then I can open my table. Don't know yet if this will work. Must still try it .....

Regards
Karen

 

[MozNZ]

Does the program have to work with both, or are you converting it? If it's a one-time conversion you could do a global replace on the field definition in the dfm files.

Otherwise you'll need to iterate the components on the datamodule(s) and force the change at runtime. I'd do that in a pair of procedures, because it won't be trivial.

I'm not near Delphi, and haven't done this for a while, but the iteration is fairly straightforward:

for i := 0 to Form.ComponentCount do
if Form.Components is TDateField then begin
DateField := Form.Components as TDateField;
// Convert to DateTime

The actual conversion will be a bit ugly - create a TDateTimeField, copy the database etc values over from the TDateField, then add it to the relevant TQuery/TTable, delete the old one, whatever. You've presumably got the component source, so it'd be worth finding out how Delphi changes the field type and copy that.

 

[Karen99]

This is my whole problem. I can not get Delphi to change the fieldtype. There are a setfieldtype, but that only works with blob fiels. I went into delphi's source code db.pas. There I can see that setfieldtype for tblobfields have code in it, but setfieldtype for tfield have no code in it. If I add code to this, how do I compile it. I must probably recompile the package. But what package would that be ?

 

[Karen99]

I got a solution !!! I just went into db.pas and added this code :

procedure TField.SetFieldType(Value: TFieldType);
begin
SetDataType(Value);
end;

I just added SetDataType(Value); The procedure was already there, but for some reason there was nothing in the procedure. I guess it can be dangerous to change any field type, but I am using it for some specific reason.

For Delphi to recompile db.pas - I rename db.dcu to db1.dcu (always better to keep a backup). My db.pas was in \\source\vcl so I copied it to my lib file where my db.dcu are. And then I just rebuild my whole project.