Sunday, October 18, 2009

SWFox Final Day

The final day of Southwest Fox began at included repeats of sessions from the previous days. The conference schedules each session twice to give all the attendees plenty of opportunity to get to the sessions they want to hear. I really like this strategy, I have been to some conferences where there are two sessions that I want to see scheduled at the same time. Just another one of those nice touches that make this a really fun conference to attend.

SWFox_GiveawaysI haven't mentioned our giveaways for this year's conference yet. This year our excellent marketing department was able to get us several items to distribute from our booth. We had some great mini cars that include a spring so you can pull them back and have them zoom over any flat surface. We also had a bunch of nerf-like missiles/grenades which came in very handy for the ambush of Doug Hennig on Saturday. Finally we gave away 1GB flash drives which included a bunch of information about Advantage for FoxPro developers.

Kindle 2In addition to our free giveaways we also had an Amazon Kindle 2 to give away. We collected names during the conference and had Rick Schummer draw for the Kindle during the closing session. The lucky winner , was excited to take away his bran new Kindle 2 from the conference. Note that Advantage Database Server a Developer's guide is not available on the Kindle, however, it is included as a part of the 9.x help file.

I would like to thank the Southwest Fox staff for once again putting on an excellent conference. I always enjoy talking with people from the FoxPro community and this is the biggest FoxPro event in North America. I am already thinking about what we can do for next year's conference.

Saturday, October 17, 2009

Southwest Fox Day Two

Day two of Southwest Fox had a much lighter schedule than day one. There were only twenty nine sessions scheduled. As you can imagine attendance was down just a bit for the 8:30 am sessions but by 10 everyone was back into learning mode. The afternoon sessions were very well attended.

I presented a session on Advantage Database Server right before lunch. My presentation was similar to the presentations that I have been giving at various FoxPro user groups over the past year. My presentation highlights the capabilities of the server along with demonstrations of using CloneDBC and concurrent access to the data. There were many good questions during the session and I got a lot of positive feedback about the product.

One of the really fun parts of the conference is the family like atmosphere. On the final session of the day an attack was planned by Craig Boyd on Doug Hennig. All of the people attending Craig's session were issued Nerf guns and Sybase iAnywhere Nerf grenades. Craig then lead the charge into Doug's session. The fight was over quickly given that the only armed parties were from Craig's session. Although the scene was brutal, hundreds of Nerf darts were scattered around, order was quickly restored and the sessions continued. See the video here.

Although there is no dinner provided on the second night of the conference many of the attendees look forward to the annual trip to F1 Race Factory here in Phoenix. There is a very intense rivalry between many of the regular speakers at the conference. It is the source of a lot of trash talk on Twitter prior to the conference and many hallway challenges during the conference.

Friday, October 16, 2009

SWFox Day One

Day one of Southwest Fox consisted of forty sessions that started at 8:30 am and ended at 9:30 pm. The organizers do an incredible job creating a schedule and providing lots of great content. Each session is presented twice so all the attendees have an opportunity to see all the sessions that they want to. I am impressed each year at how well everything works.

Booth_Setup I spent most of my day talking to the attendees at our booth in the exhibit area. Since this is our third year I have been seeing a lot of familiar faces. The FoxPro community is a very tight knit group and many of the attendees have been coming to this conference for years. It has been great chatting with people that we have met at various FoxPro User Group Meetings over the past year.

I was able to break away from the booth to attend the session on Alaska Xbase++. The presentation was done by  , the chief architect and president of the company. Xbase++ has big plans to support the FoxPro community by adding support for the VFP language to the Xbase++ compiler. This could become another way for FoxPro developers to work with Advantage since Alaska provides a native Advantage Data Driver.

Ken Levy presented a session on Advantage where he discussed the benefits of using Advantage with your FoxPro applications. Ken discussed the benefits of moving to client/server specifically using Advantage. He demonstrated the use of the bran new CloneDBC utility to create an Advantage data dictionary. He then showed accessing the data concurrently from a simple Visual FoxPro application and a Visual Studio application.

Ken also discussed some code samples which were produced by VFP Conversions. There are two samples one is a VFP9 application and the other is a C# application. The C# application uses the Advantage .NET Data provider to access the data concurrently with the native VFP application. You can download these samples using this link.

After the sessions we had a very nice dinner provided by the conference. During the dinner many giveaways were handed out to attendees. After dinner there was one last group of sessions for the hard core conference goers. Still one and a half days left in this year's conference.

Thursday, October 15, 2009

SWFox Conference Begins

Right before SWFox Keynote The 2009 Southwest Fox conference began with a great keynote presentation. The conference is organized by Geek Events which is a group of three Visual FoxPro MVPs; Tamar Granor, Doug Hennig and Rick Schummer. Unlike most keynotes this one includes a bunch of personal touches. The conference statistics are discussed right up front. This year there are 88 attendees from 7 different countries, 19 speakers, 9 sponsors and only 2 additional staff people.

Several awards are also presented to the community during the introductions. First off the VFPX Outstanding Service award went to Cesar Chalom who contributes to the GDI Plus X and FoxCharts projects. Next up was the Ceil Silver Ambassador award which goes to an international member of the community and pays for them to come to the conference. This is the inaugural award and it went to Emerson Santon Reed from Brazil. Finally the award for most popular speaker for the 2008 conference, as determined by the attendee evaluations, was Cathy Pountney.

After all the conference business the keynote speaker Sara Ford from Microsoft was introduced. Sara is the project manager for CodePlex which is Microsoft's open source community. The title of the keynote "Towards a Stronger Open Source Ecosystem on Codeplex.com". Sara is the self-titled "Queen of Open Source at Microsoft" and she has made a lot of effort into understanding how the Open Source community works.

Sara is a high energy and dynamic speaker and she covered many topics using many stories. She discussed the process of bringing more open source awareness to Microsoft which she discussed in detail in this video. Overall I found her discussion of open source very interesting and it is great to see that Microsoft is supporting open source development. Just as interesting though was her discussion of how the CodePlex site is developed. They use agile development processes so a release is done every three weeks. This was a big change for her since she came from the Visual Studio team which has an 18 – 24 month release cycle. The entire keynote was broadcast live over UStream and you can view the recording here.

Sessions begin at 8:30 on Friday and I will be attending a few of the sessions.

Wednesday, October 14, 2009

Ready for SWFox

SWFox Southwest Fox 2009 begins Thursday night, this is my third year at the conference. I am particularly excited to see the keynote by Sara Ford from Microsoft. I am sure she will put on a good show as well as provide some good information.

This year there will be two presentations on Advantage during the conference. Ken Levy will be doing a session on Friday at 3:30 PM and I will be doing a session at 11:30 on Saturday. The session descriptions are below.

Using Advantage Database Server with FoxPro – Ken Levy

This session is an overview with useful scenario samples of using VFP with Sybase's Advantage Database Server (Advantage), as well as the free Advantage Local Server, OLE DB Provider, ODBC Driver and .NET Data Provider. Advantage can be used with Visual FoxPro as a database system, but its unique functionality is its data dictionary wrapping and using existing DBF data. This allows .NET and other technologies to use DBF based data of VFP applications while not requiring VFP applications to be modified. Advantage will play a key role in modernizing VFP applications with DBF data into the next decade. Ken will present a series of useful scenarios of using VFP DBF data with .NET, Office, and other products and technologies by leveraging Advantage tools and products, many of which are free for both development and deployment.

Advantage Database Server Have We Met? – Chris Franz

As companies store more and more data the need to organize and retrieve this data becomes critical. Use of a client/server database system provides secure storage of large amounts of data while providing high performance. Advantage Database Server provides a unique client/server solution for Visual FoxPro development while making data conversion unnecessary.
This session will provide an overview of Advantage Database Server's key features and demonstrate those features that are unique to FoxPro. You will learn:

  • How to connect to Advantage from FoxPro using remote views, cursor adapters and SQL pass-through.
  • Concurrent access to data from a FoxPro application and an ASP.NET website
  • Data security through restricted access and encryption
  • Accessing very large DBF tables ( >2gb )

I'll be doing daily posts from the conference and I may even post to Twitter as well. The FoxPro community are very avid users of twitter so you can get a real great perspective on the conference. Just take a look at what they are saying.

What's being said about SWFox

Southwest Fox 2009

kevincully Later folks. Shutting down to head to #SWFox. 4 hours ago reply

mroof Heading to the airport... See ya'll at #swfox 3 hours ago reply

mikefeltman Uninstalling VFP from my notebook on the day I leave for #SWFOX - keep your fingers crossed for me! 3 hours ago reply

frontier2000 @MikeFeltman /uninstalling VFP/ Check your email! #SWFOX 3 hours ago reply

psherwood Trying to finish up several projects before I head out to #swfox this evening. 3 hours ago reply

visutrac #SWFox Happy Trails to all heading off to AZ for the conference ! Enjoy , it is going to be way nicer than the nearly winter weather here. 2 hours ago reply

dfapam Something must be wrong. All packed but the airport shuttle isn't due to pick me up for another hour. #SWFox 2 hours ago reply

Monday, October 12, 2009

Book Review – A Guide to the SQL Standard

This will be a different type of review than I normally do, because this book is more of a reference guide rather than a book you would read cover to cover. A Guide to the SQL Standard Fourth Edition by C.J. Date with Hugh Darwen is an in-depth examination of the SQL standard. It breaks down nearly every aspect of the standard with good examples.

I have used this book frequently as a reference or when I need more information about a particular aspect of SQL. The book is divided into five sections; Introduction, Some Preliminaries, Data Definition and Manipulation, Data Control and Advanced Topics.

The introduction is a very good summary of SQL and it covers many of the features of the SQL language. For anyone new to SQL chapter 2 is a must read, it is also a good review for any SQL veteran. This chapter paints the entire picture of the overall functionality of the SQL standard.

The preliminaries section is a pure reference section in my opinion. It provides lists of key words, operators and basic SQL concepts like schemas and transactions. This is a good section to refer to when you need to verify a keyword or look up a basic operator.

The data definition and manipulation section is the real heart of the book. This section contains seven chapters which cover many aspects of working with data using SQL statements. In particular chapter 10, Data Manipulation: Cursor Operations includes several examples on working with a set of data in an SQL script. Chapter 11, Table Expressions, provides an in depth discussion of table joins. This section is very useful in demonstrating techniques for retrieving data from a highly normalized relational database. Chapter 12, Conditional Expressions, provides a tremendous amount of useful information about filtering data.

The data control section provides discussions on data integrity and data security. The integrity section provides useful information about using constraints as well as referential integrity. The data security chapter covers information about the different types of operations which can be defined for database objects. This chapter includes information on the GRANT and REVOKE statements.

The advanced topics section contains detailed information about various topics. I found chapter 16, Missing Information and Nulls, to be very interesting. It is very valuable to understand what a NULL really means and the authors do a very good job demonstrating the impact of NULLs. Other chapters go more in depth about data types such as date and time values as well as character data.

The bottom line: this is a very good reference book. It provides a detailed look at the SQL standard which will give you a broader understanding of how SQL should be implemented. Keep in mind that not all database servers implement all aspects of the standard. If you are looking for a book that teaches you how to write SQL statements to retrieve your data there are better books. However, if you are looking for a better understanding of the SQL language this provides the in-depth view of how it works.

Friday, October 9, 2009

CloneDBC Screencast

Using the CloneDBC utility A new utility named CloneDBC is included with the latest Advantage OLE DB Provider (version 9.1.0.16) which replaces the DBCConvert.prg which was included in previous versions. The CloneDBC utility creates an Advantage Data Dictionary which contains references to the same tables as a FoxPro Database Container. This makes it easier for developers working with FoxPro data to access it through Advantage.

This short screencast, about 7.5 minutes, highlights the features and limitations of the utility. It also walks through the conversion of a sample DBC. You can get more information about CloneDBC in this announcement.

Wednesday, October 7, 2009

Translating DBC Views to Advantage

You can use the CloneDBC to create an Advantage Data Dictionary (ADD) from an existing FoxPro Database Container (DBC). This tool adds the tables, views and referential integrity rules to the ADD based on the information in the DBC. However, FoxPro allows several things within their views which are not allowed by Advantage.

One of the most notable is the ability to order a view. Using an ORDER BY clause in a view is prohibited by the SQL standard and could result in poor performance. For example if you are using a view in another SQL statement that is ordered the server would have to order the data twice. The view would be ordered then the resulting join would be ordered.

The CloneDBC utility has an option to remove the ORDER BY clause from all the views that it copies from the DBC. I highly recommend using this option and then ordering your views within your application.

Another issue I ran into when cloning the Northwind sample database was the join order. The FoxPro view designer uses <prev join> when listing some of the joins that are used by the view. I found that this syntax sometimes does not translate well into Advantage. You may get errors stating that certain tables were not found. You can resolve this issue by removing and adding the joins again using your primary table as the first join. Below are two versions of the PRODUCT_SALES_FOR_1997 view. The top image shows the original view definition which gave me an "Table or alias not found : OrderDetails…" error.

FoxPro_ViewProblem1

In this case I deleted all of the joins and re-created them starting with the Orders to OrderDetails join. This did not change the results of the view but it did allow the view to be added to the ADD file correctly.

 FoxPro_ViewSolution1

Another issue with cloning is support for various FoxPro functions. If you are using functions in your View I recommend checking the Supported Scalar Functions before cloning the DBC. Some of the functions I had to replace were ALLTRIM and NVL. The Advantage SQL Engine does not support ALLTRIM, however, it does support TRIM which provides the same functionality. The NVL command can be replaced with ISNULL which is supported in both FoxPro and Advantage.

Finally be aware of reserved keywords that may be used as column names or alias names. Use of these words will produce SQL syntax errors. The following example is the SQL Code for the SUMMARY_OF_SALES_BY_YEAR view.

-- Original view statement year cannot be used as an alias without a delimiter
SELECT YEAR(Orders.shippeddate) AS year, SUM(Order_subtotals.subtotal) AS total 
 FROM  Orders  INNER JOIN Order_Subtotals 
 ON  Orders.orderid = Order_subtotals.orderid 
WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2
 
-- Corrected view statement with year delimited with double quotes
SELECT YEAR(Orders.shippeddate) AS "year", SUM(Order_subtotals.subtotal) AS total 
 FROM  Orders  INNER JOIN Order_Subtotals 
 ON  Orders.orderid = Order_subtotals.orderid 
WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2

When cloning your DBC pay close attention to any reported errors after the conversion. Many times you can make a few simple changes to get the same functionality with Advantage.

Monday, October 5, 2009

CloneDBC Available with Advantage OLEDB Update

With the release of Advantage 9 we added native support for the VFP 9 data types. Unfortunately we could not add support for the FoxPro database container (dbc). However, our Advantage data dictionary (ADD) provides the same functionality. With the initial release we included a program called DBCConvert which read the DBC and created an ADD which contained all the compatible features. The ADD could then be used to access the DBF data with an Advantage enabled application.

With the latest update of the Advantage OLEDB provider we have replaced the DBCConvert program with a new utility called CloneDBC, read the announcement here. This is a VFP application which simplifies the process of creating an ADD that points to the same data as an existing DBC. The utility includes a graphical interface which allows users to browse for a DBC to convert. The new utility has also been enhanced enabling it to copy more objects from the DBC than before.

CloneDBC

The chart below highlights the differences between the original utility and the new utility.

DBCConvert.prg CloneDBC.app
FoxPro program code, reads the DBC currently open in FoxPro GUI based application allows for selection of DBC
Does not convert views with "ORDER BY" clause Option to strip ORDER BY from views
Cannot create referential integrity rules if NOT DELETED() is not specified keys in the relation Option to add NOT DELETED() to keys in relations

It is important to note that neither of these utilities make any changes to the DBC file or the tables. The utility simply reads the DBC and creates an ADD that contains all of the compatible objects. The new ADD will contain references to all the tables, views and referential integrity rules. We can not currently convert triggers or stored procedures.

You can download the latest version of the Advantage OLE DB provider from the Advantage Developer Zone.

Friday, October 2, 2009

FAQs – September 2009

Local Server on a 64-bit OS

Although we introduced a 64-bit version of Advantage with version 9.0 it is only the Client-Server (Remote) Server version. The Advantage Local Server (ALS) is only available as a 32-bit version. Any application that will use ALS must be compiled as a 32-bit application. When using Visual Studio make sure you set the target platform to x86 to ensure that your application will be compiled as 32-bit.

Working with Backup Sets

Advantage online backup allows users to create a backup of their database or free tables while the tables are in use. You can then further protect the data by making a backup of this backup with your normal backup software or procedures.

Any backup created using online backup must be restored prior to using it. The restore process copies the tables back to the specified location and verifies their integrity. It also rebuilds all of the indexes to ensure they are up to date. Attempting to open the backed up tables directly will result in errors.

You can restore a backup using sp_RestoreDatbase, sp_RestoreFreeTables or the adsbackup utility with the –r option.

Error 75 on Entity Framework Model

You may receive an error when generating an ADO.NET Data Model if your primary key fields are allowed to be NULL. The error may look like the following.

Error 75: Key Part: 'Employee_Number' for the type EMPLOYEE is not valid. All parts of the key must be non nullable.

All of the fields used in primary keys or as foreign keys must be set to be non nullable. The Null Valid property for all of these fields must be set to NO.

 Null Valid Option

Using the Entity Framework with Prism

Delphi Prism does not currently support the entity framework model wizard. There is no code generation for the oxygene language available for the wizard to utilize. You can manually map your tables, views and stored procedures into a model and write the code yourself. You could also generate the model using C# and access it from your Prism project.

There is some additional information about using Prism with the entity framework on Stack Overflow.