PDA

View Full Version : Are Your Customers still on the embedded DataFlex database? Go SQL!



Peter van Mil
16-Oct-2011, 05:45 AM
Are Your Customers still on the embedded DataFlex database? Go SQL! is the introduction of a presentation of the Corroborree 2011.

DAW encourages developers to use a SQL database a SQL database as a backend server. At the same time DAW advises developers to use the native tools of the backend server to do modifications to the tables. I agree with that, but sometimes it feels uncomfortable.

When you are using SQL 2008 and get used to it, you tend to use newer datatypes. E.g. VARCHAR datatypes in stead of CHAR or TEXT, new DATE, TIME and DATETIME2 in stead of DATETIME. Database Builder respects the VARCHAR datatype at a restructure when it exists. When you hate DATETIME datatypes and want to use DATE and TIME, you get in real trouble. Database Builder changes DATE columns into DATETIME after a restructure. In that case the advise to use the native tools is more than just an advise.

Database Builder could do a much better job, but it seems stuck to old possibities. Why can't Database Builder generate VARCHAR columns? Why can't Database Builder generate DATE columns if a developer just wants dates? Why can't we use 0001/01/01 as a dummy zero date in MSSQL? What to do with TIME columns?

When DAW encourages developers to use a SQL, I want to encourage DAW to offer more dedicated support of SQL databases. If developers have to take it seriously, DAW has to take it seriously too!

Garret Mott
16-Oct-2011, 06:25 AM
Good points Peter.
<Begin Soapbox>
One thing I'd add is that the SQL focus seems to be on converting existing embedded apps to SQL. While this is good, it strikes me as too limited. For VDF to grow, we need to be able to use existing SQL db's - from apps written in other languages. I recently converted a VB app to VDF & one of my biggest headaches was utilizing the db, as it used more datatypes than VDF seems to be comfortable with: Money, DateTime, & text for example. I realize VDF supports datetime, but they seem to have issues with indexes (which may be a MS issue, but VB, for example, seems to have worked it out). The system is live & the customer is very happy with it, but I had to make many db changes to get it there.

Focusing only on converting embedded to SQL seems cannibalistic - as it doesn't bring new customers to the VDF fold - just keeps existing ones. Yes, this is important - but IMO, shouldn't be the only focus.

To fully utilize SQL, VDF may need extended or new controls. While I love VDF's backwards compatibility, adding new SQL only functionality may be what's needed to head down this road.
<End Soapbox>

To add to your point, the piece that has impressed me the most is how fast the SQL backend is. In particular how little the # of users seems to affect speed.

Bob Worsley
16-Oct-2011, 03:09 PM
I heartily agree Peter. In my "day" job I work all day long with SQL Server and use all of those strange data types but have stayed away from them in my VDF applications, which do use SQL Server, because of compatibility concerns. One of these days I must dig into using the SQL Server datetime in a VDF application to see how well it works.

Knut Sparhell
16-Oct-2011, 03:32 PM
+1 for this. Good points!

Michael Mullan
16-Oct-2011, 11:22 PM
So if I do this, and port over to SQL how hard is it going to be to convert Crystal reports to the new db?
And what about VRW? is that going to gracefully accept being pointed at a new database?

Nils G. Svedmyr
17-Oct-2011, 01:06 AM
I fully agree with everything that is said here. Well put, Peter and Garret!

Cheers.

seanyboy
17-Oct-2011, 03:58 AM
So, whilst current cutting edge chatter is focusing on noSQL, we should continue to make the plunge and go to SQL?
Not that the embedded database is noSQL, but it's closer than MSSQL is.

Embedded could have been brilliant, if DA had spent some time on it. Bang a JSON embedded db server on the top of embedded, and update the client so it can read from this server in the same way as from embedded files. Open up the Database API so that more than Mertech and DA can provide database drivers.

Concentrating on MSSQL when the language doesn't really support it feels like DA are giving away a core component. Also, with the emphasis on moving more stuff into HTML and Javascript at the other end of the stack, Dataflex is rapidly turning itself into an unnecessary middleware component.

/rant over

Bob Worsley
17-Oct-2011, 07:50 AM
So if I do this, and port over to SQL how hard is it going to be to convert Crystal reports to the new db?
And what about VRW? is that going to gracefully accept being pointed at a new database?
Isn't it simply a matter of the driver? I've used VRW on SQL Server and it's quite fast and easy to set up. If I remember correctly I've used the same version of Crystal 11 with both DF and SQL Server so I don't think there's a problem there.

David Martinko
17-Oct-2011, 08:39 AM
Redeemed Software has a tool to convert your CRXI reports from Native DF to MSSQL. It will change the data source on all your tables and correct the date fields to be date and not datetime.

Javier
24-Oct-2011, 11:03 AM
I think that this is not serious. Now here and tomorrow there. This discussion about SQL ¿ is not the same the same that we had in 2010 ? With this is not possible to preview the way that you have to be preparing for the next years.

Here and I suppose that too there, must be slogans like " Master of all = master of nothing " or " Who much includes, little is to tight" or something similar ( sorry... the translation )

I think that DAW has not to fall well to the whole world, nor to follow all the modes. Must to have his own character as ever... but I miss it more and more.

From time to time we are always speaking about the complexity of the language, the puzzle of complements, and the new directions. We are lost in details, and we arrives latter to the important lines.

Javier

Larry R Pint
24-Oct-2011, 03:27 PM
Two additional items that I think are necessary for SQL support are:
1) support for null values and
2) support for conditional relationships.

I'm working with a database designed by another company and I'm constantly being bitten by lack of support for these two items.

Garret Mott
24-Oct-2011, 06:05 PM
I hate nulls & have always appreciated how I never had to deal with them in VDF. However, for full support I 'spose you're right.

Vincent Oorsprong
25-Oct-2011, 12:52 AM
Larry,

What you mean with "support for null values"? AFAIK it is supported but not advised for indexed columns.

Larry R Pint
25-Oct-2011, 10:17 AM
I am working with data created by a web portal written in Java.

VDF does not have a value of "null". Null data columns from an SQL database come in to my application as "0" if a numeric column, the default date for datetime and empty strings for string columns. When I try to save those values back to the database, the VDF values are saved rather than "null". The application I am working with expects null values for columns that have not been filled by the users, so once I save a record (row) the main application, the data is seen as "filled in" and is treated differently.

When saving this data, I have to "guess" if that "0" means the user entered a value of zero or if it is what VDF sees for a null value. Same with the other data types. We are an insurance company so, for example, does "0" for a liability deductible value mean that user selected "no deductible" (the most common condition) or that the user did not select any deductible value?

This database is also full of conditional relationships that VDF also cannot handle. Because of this I am unable to define relationships for VDF to use and must handle that all manually. (I know, I should take the time to look at the conditional relationship package available on the forum, but I am reluctant to use things not supported by DAW.) An extreme example of this is the coverage table. The coverage could "belong" to an insured, a policy, a policy line item, a vehicle or cargo. There are foreign key fields in this table for each of those "parent" tables. Up to two of them will be used in each row (policy and maybe one of the others). SQL has no problem with this, but VDF, being a hierarchical relational database system, can't handle it.

Vincent Oorsprong
25-Oct-2011, 11:15 AM
Larry,

You are right that the language does not have the NULL concept. But if your database supports NULL values and you want to use them you can make use of the attribute DF_FIELD_IS_NULL. It can be retrieved and it can be set.

Richard Hogg
25-Oct-2011, 04:51 PM
Hi Larry,

I have a similar situation with NULL values and have made use of the DF_FIELD_IS_NULL attribute as Vincent suggested.

I do agree with the points about conditional relationships being supported. I use Peter Crooks conditional relationships packages which are fantastic but it would be good to be able to model databases in the same way SQL (MSSQL in my case) does being built into VDF.

One other thing I was bitten by recently was relating a field in a child file to more than one other file. I have been dealing with an SQL database (used in a third party .NET program) which has multi-segment relationships where a field can relate to more than one table. When I started I just assumed VDF would handle this but then discovered while it handles multi-segment relations once you use a field in a relationship you can use it again. This resulted in having to create a whole lot of unique id fields to work around it. So yes it could be handled but it would have been good if the VDF could have modelled this structure especially given that the main strength of VDF is supposed to be it's data handling abilities.

Cheers
Richard

Larry R Pint
26-Oct-2011, 08:48 AM
Larry,

You are right that the language does not have the NULL concept. But if your database supports NULL values and you want to use them you can make use of the attribute DF_FIELD_IS_NULL. It can be retrieved and it can be set.

Thanks, Vincent.

I do use the DF_FIELD_IS_NULL to set the field to NULL when saving. The problem is deciding _when_ to do so. For example, in the case where the value to be saved is zero, does that mean the operator entered a zero or is it the value VDF set for a NULL database value? (i.e. do I move "0" to the ddo buffer or set DF_FIELD_IS_NULL to True?)