PDA

View Full Version : Vdf & ms sql



Garret Mott
9-Jun-2010, 06:33 PM
Hi -

This may ramble a bit, but I wanted to get some thoughts down while all this is fresh in my (alleged) mind. Some of this has probably been posted by others as well.

I've heard DAW say for quite some time that an SQL DB of some sort is the way to go for multi-user apps. While I certainly won't argue that, I do think that VDF needs to change quite a bit to really play well with SQL Server.

I realize most developers will be converting a DF DB to some flavor of SQL Server & that process is probably pretty smooth. I, however, managed to (as usual) do things the hard way & chose my first VDF/SQL project as one where the data existed outside of DF or VDF. In fact, I had to convert it from Access to SQL Server & that was a whole adventure in itself - on which I may post in the Connectivity forum at some point. Short of it: do not use the "SQL Server Migration Assistant for Access" tool that MS recommends! What a mess....

I should interject some info here. I am a (very rusty) MCDBA - so I do have a clue when it comes to MS SQL Server. In this instance I am working with MS SQl Express 2008. My past experience with MS SQL Server as a back end is with VB.

What it boils down to for me is that I don't get how VDF developers that cannot change the SQL DB can make it work with VDF. Being normal SQL data, what I have is full of Float, Real & Money fields. From what I've seen, VDF does not play nicely with them at all. After 12+ hours of converting them all to Numeric (x,y) - out of 4+ days of fighting with the conversion & figuring out that I had to convert fields - I have some views that will actually find records.

This was the main issue for me compatibility-wise - though I know I'll be dealing with date/datetime issues. I realize VDF now has date-time capabilities, but yet one can't utilize a datetime field in VDF. Text/memo fields? VDF cannot come close to what MS SQL Server can hold. Image fields? Nope (AFAIK).

Then there's how SQL data is accessed. Think how powerful using SQL statements instead of Constraints would be..... Yah, I know I've mentioned this before. ;)

I'm know there's more, but wanted to toss this out for consideration. I realize that backwards compatibility is important (& appreciated!) & that the current work on new grids is a huge step, but, along with more user interface objects (without relying on 3rd party controls - meaning 3rd party to us, the developers), utilizing all the capabilities of modern DB's needs (IMO) to be addressed.

Have at me!

Peter van Mil
10-Jun-2010, 01:46 AM
In the past 8 years I have been dealing with an external SQL Server database of Exact financial software. From time to time this database has been acting wierd and DAE has several times modified the CK for MSSQL to support it.

My first shock was the fact, that Exact was using approximate database to store financial data :rolleyes:. From the VDF perspective an amount of 17.50 was looking like 17.4999999999999999 (or something like that). This has been solved with masks.

One issue, that isn't solved, is supporting the (hummm) € sign. In the items table some items contained the price in euro's. We have been discussing about possibilities to support it, but after all we have asked the client to change the data.

At the last EDUC we have been talking about the next big thing. When the new grids are there, the VDF product is some kind of finifshed. The aren't very obvious things to solve anymore. So it will be time for more fundamental issue. The first thing is Unicode or at least ANSI support. A better support for "foreign" SQL datatypes and other SQL features is another issue.

Support for another character set than OEM has consequeses for the backwards compatibity of source code. Until now DAW has been protecting us against these issues, but developers that are struggling with this would be relieved when DAW set them free.

Support for other datatypes than the DataFlex database know, might have consequences for compatibily between backends. Using new datatypes in MS SQL 2008 (date, time) might be a bad idea when you have to switch to another database in the future. But it is a must when you have to connect to existing SQL databases or it could be a good choice for applications in vertical markets.

So, it is becoming time for a change and I hope to hear "Yes, we can". (I am only talking about the VDF product here :D).

Garret Mott
10-Jun-2010, 08:40 AM
Good points Peter. We in the US don't have as much of an issue with ANSI or Unicode (at least I don't) - but I can see that it'd be really important for the rest of the world!

Yes, there could be backwards compatibility issues with db changes, but I'd think that adding data types would be additions - not replacements, so they could be disabled for the embedded db or db's that can't handle them.

Of course we can! We just need the will.... And money & time.....:eek:

Bob Worsley
10-Jun-2010, 08:51 AM
Garret, you aren't (for once :D) off base on this.

I would view VDF as being far from "finished" with the current SQL support. I converted one of my customers to SQL Server a year or so ago and while I didn't have problems with large numeric fields, I did encounter other problems, mostly with the $%&^#$%^@ "U" case fields which still haunt me even with the later CK. One I removed a month ago somehow came back the other day and would not go away with the normal restructuring. Modifying a table with dbBldr (on the customer's server) resulting in loss of all indexes for example - has happened several times. I'm starting to use SQL Manager to do table changes instead of using the VDF tools.

DateTime fields are great for display but completely useless otherwise because you can't index them and would need a separate date field for that purpose. Given that my application - freight dispatching - relies heavily on statuses, indexing by dates is critical.

I work with SQL Server in my "day job" so am quite familiar with much of it's operation, probably more so than other developers so maybe have a leg up. We VDF developers have to contend not only with the oddities of VDF/SQL (and some of that is really a no-man's land) but also SQL in a record based product. In the oddities dept, INT files for example, how do they get populated and why do they get populated with incorrect values? i.e. field lengths... When to remove the CCH files so you don't get strange results in data and file structures, etc. etc.

Look at the speed difference in VRW between a VDF and ODBC connection, it's huge!

So, I for one would vote for vastly improved SQL support as an upcoming DAW project. Better compatibility with the rest of the world - think of the possibilities! :eek:

DaveR
27-Jun-2010, 06:52 AM
...............
So, I for one would vote for vastly improved SQL support as an upcoming DAW project. Better compatibility with the rest of the world - think of the possibilities! :eek:

you'll be going metric next...... :D

Bob Worsley
27-Jun-2010, 10:49 AM
you'll be going metric next...... :D
Just mentioning that the other day when talking to one of my customers, a freight delivery company... If only everyone was on the same system! :cool:

DaveR
27-Jun-2010, 07:28 PM
Just mentioning that the other day when talking to one of my customers, a freight delivery company... If only everyone was on the same system! :cool:

I now have a supermarket within my remit. I didn't know what confusion was before I saw how US wholesalers 're-code' European barcodes into their 10 or 11-digit UPC based systems.

Javier
15-Sep-2010, 09:15 PM
Probably we should pass directly to programming in excel.

There is the one who does not understand that "the others" work with sql because they have not had own database in his language and have not had another option.

The same thing was said of Access years ago, and there were many people who fell down. I do not enter any more commercial experiments, and the free offers AT THE MOMENT.

Have you seen the price of the Windows 7 ultimate ?