Friday, November 13, 2009

Madison PC Users Group

We visited the Madison PC Users Group meeting on Wednesday night. There was a good turnout at the meeting and we were able to discuss Advantage Database Server with them. I would like to thank Eric Selje of Salty Dog Solutions for getting us on the schedule. You can get his summary of the meeting here.

This meeting had a diverse mix of PC users and developers. We trimmed down our presentation to highlight the Advantage features and we did a lot more discussion of general database topics than usual. However, the group was very friendly and had many good questions during the presentation.

If your interested in the information that we presented to the Madison user group check out the Getting Started with Advantage screen cast we did earlier this month.

Wednesday, November 11, 2009

Chicago FoxPro User Group Meeting

Last night we presented at the Chicago FoxPro Users and Developers Group. We got a great turnout and the meeting lasted nearly three hours. There were many questions about replication and encryption during the meeting. It was great to have such an interactive group to present to.

We did take a short break during the meeting to have some authentic Chicago style pizza. Which made for a great break during the meeting.

With everyone energized after the pizza we finished up the meeting by showing some demonstrations of our CloneDBC utility and using Advantage from FoxPro. Again we had great interaction and good questions during the demos and we were able to discuss several use cases and scenarios.

All of the attendees received a flash drive which included all of the screen casts we have produced for FoxPro developers. You can view these screencasts all together using this link. Many other screen casts about Advantage are available on the Devzone.

We will be in Madison, WI tonight for another user group meeting.

Monday, November 9, 2009

Chicago FoxPro User Group

I will be presenting at the Chicago FoxPro Users and Developers Group on Tuesday night the 10th of November. We will be discussing using Advantage Database Server with FoxPro. If you are in the Chicago area feel free to drop in. THe meetings are held at Tech Nexus on the 15th floor of 200 S. Wacker Drive. The meeting runs from 5:30PM until 8:00PM.


View Larger Map

Here is the description from the Chicago FoxPro User Group Site

We have a couple of representatives of Sybase coming to Chicago to demonstrate their Fox-friendly data server -- Advantage; An alternative to pricey backends with an easy conversion route. It should be a good time -- another milestone in a long history of tech twists and turns. From their promotional material at http://www.sybase.com/products/databasemanagement/advantagedatabaseserver: * Flexible data access via either native relational SQL or direct navigational database commands. * Optimized data access for all development environments, including Delphi, Visual Studio, Visual Objects, Visual Basic and more. * Zero administration, easy to install and manage — does not require a database administrator, eliminating high administration costs. * Complete referential integrity support including primary/foreign key definition and cascaded updates and deletes. * Complete server-based transaction processing eliminates database corruption, drastically reducing support costs. * Database security and encryption support. * Fully scalable from local to peer-to-peer to client/server environments — with one set of source code If you have been wondering about a Fox friendly data backend that breaks the 2 GB barrier, come check out the Sybase presentation of Advantage Database Server. First meeting is free. From then on, $10 per meeting or $75 per year. The usual time and place: Tech Nexus 200 S. Wacker Drive, Suite 1500. Tuesday Nov. 10th at 5:30pm (chip in for Pizza and brown bottle beverage.) Here’s the RSVP link: http://bit.ly/3yOzIG As we have worked to keep the FoxPro Users and Developers group a vibrant and interesting colloqium over the years, we notice that new blood comes into the group based on one meeting or subject. I remember making contact with a future employer because he came to see what was up with Crystal Reports in 2002. See you there?

Monday, November 2, 2009

FAQs October 2009

DSN less connection

There are two ways to connect to an ODBC driver, using a Data Source Name (DSN) or by providing a connection string. The latter is generally referred to as a DSN Less connection. A DSN Less connection can be useful because there is no need to create a DSN on every system your application is running on.

A DSN connection string needs at least a Driver and a DataDirectory. When connecting to a data dictionary you will also have to specify a UID (Username) and PWD (Password). When working with free tables we recommend specifying the table type using the DefaultType keyword. See the example DSN connection strings below.

// Connection to an Advantage Data Dictionary
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\Demo\DemoDictionary.add; Uid=adsuser;Pwd=password;

// Connecting to a directory of Visual FoxPro tables
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\VFP ; DefaultType=FoxPro;

All of the properties for an ODBC connection are listed in the  help file 

Permissions Required for Modifying RI Rules

You cannot assign rights to Referential Integrity objects within the database. RI rules can only be created in the database by an administrator. Users with ALTER permissions on the tables involved in the RI rule can modify the rule. RI Rules are not visible in the dictionary without the proper permissions. RI rules are enforced for every database user there is no way to exclude users from an RI rule.

Using the AdsCommandBuilder

The Advantage .NET Data Provider includes a AdsCommandBuilder class which is responsible for creating SQL commands to update, insert and delete data for an AdsDataAdapter. Any statements that are not set directly on the AdsDataAdapter will be generated by the AdsCommandBuilder.

By default the command builder requires the table to have a primary key in order to generate the additional commands. Ideally you will have primary keys on all of the tables that you use within your application. In practice this is not always the case. If the table does not have a primary key assigned you must set the RequirePrimaryKey property to false.

The following code demonstrates using the command builder to generate INSERT, UPDATE and DELETE commands for a given AdsDataAdapter.

oCommand.CommandText = "SELECT * FROM Customer"
daTemp = New AdsDataAdapter(oCommand)
dtTemp = New DataTable()
daTemp.Fill(dtTemp)
 
' build the INSERT, UPDATE and DELETE commands
cbAds = New AdsCommandBuilder(daTemp)
cbADS.RequirePrimaryKey = False    ' Primary key not available on the table
daTemp.InsertCommand = cbAds.GetInsertCommand()
daTemp.UpdateCommand = cbAds.GetUpdateCommand()
daTemp.DeleteCommand = cbAds.GetDeleteCommand()

Error 7156

The 7156 "Auto Index Rebuild Active" error appears in the error log it most likely occurred because the server was stopped abnormally. For performance reasons indexes are stored in cache and written to disk as necessary. If the Advantage service is stopped abnormally it is possible that all of the index updates have not been written to the index file.

To correct this situation the Advantage server will automatically rebuild any indexes that it had in cache prior to the cache. This will ensure that the indexes are proper and up to date. It will lock the tables while the index is being rebuilt so you may need to wait a few minutes before connecting clients after restarting a server that has crashed.

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.

Wednesday, September 30, 2009

Delphi 2010 Support

Embarcadero released Delphi 2010 in late August. In keeping with our commitment to supporting the latest version of Delphi we have updated the Advantage Delphi Components to support Delphi 2010.

Both version 8.1 and 9.1 have been updated. You can download the updated components on the Advantage Developer Zone. The latest versions are 8.1.0.46 and 9.1.0.15.

For the latest Advantage updates subscribe to the Advantage Announcements feed.

Monday, September 28, 2009

Getting into Web 2.0

I admit I am a bit behind on this new fangled web 2.0 revolution. However, I have been convinced that there are some good things about many of the social networks that are available. I started with LinkedIn about a year ago adding many of my colleagues and found that it was a good way to stay in contact with people I have worked with in the past.

LinkedIn

If you are on LinkedIn and you have attended one of our Technical Summits in the past join the Advantage Technical Summit Alumni group.

Starting at Southwest Fox last year I was introduced to Twitter. The FoxPro community is very active on Twitter and I started an account as well. I don't do many tweets (twits?) but I send out updates periodically. You can also find JD Mullin, Advantage R&D manager, and Mike Hagman, Advantage product manager on Twitter.

Twitter

I have also created a page on Facebook where I will be posting some events and discussions. I'll do my best to keep this page updated with information about the various events I attend. I have a few ideas on some discussions so feel free to join in.

facebook

If you are a user of StackOverflow and you have questions about Advantage use the "advantage-database-server" tag. You can get more information from our July newsletter.

Remember you can still interact with the Advantage community through our newsgroups.

Wednesday, September 9, 2009

Generating a Server Dump File

Beginning with version 8 of Advantage Database Server the server will generate a dump file when an internal error or exception occurs. This dump file will be written to the error log directory, c:\ by default. The dump file uses the following naming convention

adsdump-CCYYMMDD-HHMMSS.dmp.gz

If your server generates a dump file you can use the ADS Support Capture utility to retrieve the file along with other pertinent information. You should then open a support issue with Advantage support so we can investigate the cause of the error.

ARC_SnapDumpYou can manually create a server dump using Advantage Data Architect. Simply connect to the server using the management utility in ARC and press CTRL-F9. This will cause the server to generate a dump file which can then be sent into Advantage Support. Keep in mind this may have an effect on the performance of any application that is currently connected to the server. When the dump file has been generated a message will be displayed.

For more information about the dump file refer to the help file.

Friday, September 4, 2009

After Action Review

We just finished our latest Advantage Technical Summit. We always have the team sit down together after these events and review how it went. We review the feedback we get from the attendees and review everything that we did looking for; the good, the bad and the ugly. Fortunately there is rarely any ugly.

This has always been a good practice and has provided us with a long running history of our technical summits which gives us something to look at when we begin planning for the next one.

The Army has a similar system called the After Action Review (AAR) which is done after most operations. Whether the operation was big or small soldiers are encouraged to spend a few minutes and evaluate what happened. The AAR consists of a leader asking a three simple questions.

  • What went right?
  • What went wrong?
  • What can we improve for the next time?

Egos are checked at the door and rank does not matter during the AAR. This is to promote honest feedback and constructive criticism about the event. This shouldn't open the door to allow people to insult members of their team, although I have seen this happen. When used correctly the information gathered during the AAR can yield great improvements and serve as a "knowledge base" for new members and leaders in the unit.

So how does this type of thinking fall into the programming world. Just ask Joel Spolsky who used the five whys to help improve the uptime of their servers.

The five whys method involves asking why until the root cause of the problem is discovered. This can be a very useful tool when solving complex programming issues. By asking the right questions your investigations can be focused allowing the root problem to be revealed quicker. This process has been extensively used by Toyota motor company as a methodology to resolve problems. Wikipedia has a simple example of the process

My car will not start. (the problem)

  1. Why? - The battery is dead. (first why)
  2. Why? - The alternator is not functioning. (second why)
  3. Why? - The alternator belt has broken. (third why)
  4. Why? - The alternator belt was well beyond its useful service life and has never been replaced. (fourth why)
  5. Why? - I have not been maintaining my car according to the recommended service schedule. (fifth why, a root cause)

Regardless of what method you prefer taking some time to look back after a particular milestone will pay great dividends in the future. Having an historical reference to review before beginning a process could provide the valuable insight you need to make it the most successful process ever.

Wednesday, September 2, 2009

FAQs – August 2009

Signaling an Event

It has been about a year since I wrote a demo and an article on using Advantage Notifications. You can read the technical overview in this tech tip. There is a Visual Studio example application available on Code Central.

One potential pitfall when using events is how the data path is defined. Events are registered with the connection path and name therefore all clients that wish to listen for a particular event need to be using the same connection path. For best results I recommend using a UNC path with the IP address, Port number and share/alias name (i.e. \\192.168.0.10:6262\MyData ). Using the same connection path and event name from all clients will ensure that all clients are notified when the event is triggered.

Using the Advantage Delphi Components on Vista

By default the Advantage Delphi Components are installed to the Program Files directory (C:\Program Files\Advantage 9.10\TDataSet). Normal users do not have rights to create new files in the Program Files directory. Therefore when you compile your application you may receive a F2039 compiler error in Delphi.

This error can be avoided by turning off the Vista User Access Control (UAC), which is not recommended. You can run Delphi as an Administrator which has the proper rights to create files under the Program Files directory or you can change the output path for your project.

You can get detailed instructions about these options from this knowledge base item. Windows 7 has the same security restrictions as Vista.

Passing Data with Temp Tables

Temporary tables can be created with SQL statements by preceding the table name with a pound ( # ) symbol. These tables are only available on the connection and are destroyed when the connection is closed. These tables can be very useful for passing information to stored procedures and views.

Stored procedures accept input parameters through the __input table. However, this table can only contain a single row so you must call the stored procedure over and over if you need to run it multiple times with different parameters. By using a temporary table you can populate the temporary table with multiple records. The stored procedure could then loop through the table processing multiple inputs with a single call.

This technique can also be used to pass a parameter to a view. This tech-tip demonstrates the technique.

Using LINQ to SQL with Prism

Delphi Prism allows object Pascal code, called oxygene, to be compiled by Visual Studio. It comes with the Visual Studio 2008 shell and can be used as a stand alone product. It contains many of the features of Visual Studio including the use of Language Integrated Query (LINQ).

Cary Jensen wrote two blog posts about using LINQ to SQL on his blog. Part I introduces you to the LINQ syntax and part II demonstrates using LINQ to SQL in Prism.

Friday, August 28, 2009

Advantage Technical Summit Day 3

Although our technical summits normally last two days we provided some additional content for this event. We started the morning with a presentation from Tenderfoot Software. They use Advantage Replication to track sales from stores around the country. It was a great demonstration of replication at work to provide a consolidated look at data from several remote sites in real-time.

After the demonstration we broke out into two groups to discuss web development with Advantage. We did a session on using ASP.NET and PHP with Advantage. I presented a class on database design principals which attempts to demonstrate practical uses of various database objects (i.e. stored procedures, triggers, replication etc…)

I want to thank everyone who took the time out of their busy schedules to spend a few days with us in Washington D.C. As always we got some great feedback from everyone. If you missed this technical summit we would love to have you at our next summit in Boise. Our next technical summit will be in the Spring of 2010, I hope to see you there.

Thursday, August 27, 2009

Advantage Technical Summit Day 2

Day two of the Advantage Technical Summit included in-depth training on SQL, Data Dictionaries, Stored Procedures, Replication, Triggers, File Formats, Delphi, Visual Studio and Visual FoxPro. Our SQL training is broken up into four sessions which go from the basics through SQL scripting and information on SQL optimization.

It is a very full day of training and we received a lot of great feedback from the attendees. This group has been very interactive with lots of questions and input during the training. We had two tracks running providing a total of twelve classes during our second day.

The final session of the day was presented by Anders Olsen from Embarcadero Technologies. Anders spent about 90 minutes discussing the latest version of Delphi which is included in RAD Studio 2010. We will be supporting Delphi 2010 with the next service release of Advantage.

UPDATE: You can get some additional information from JD's blog.

After the training we hosted the Potomac Area FoxPro User Group meeting. There was a great turnout and like all the other FoxPro groups we have visited this year they were a great group of developers. We discussed Advantage and our unique offering for the Visual FoxPro community. Many thanks to all the members that showed up for the meeting.

Wednesday, August 26, 2009

Advantage Technical Summit Day 1

The first day of the Advantage Technical Summit began with the Techwave keynote presentation. After lunch the Advantage track began with an introduction from Mike Hagman the Advantage Product Manager. From there the training went much like it does when we hold it in Boise.

I did an overview of the server with about half the attendees. You can view a quick overview of Advantage here. JD Mullin conducted a round table with the rest of the attendees. The roundtable is an excellent opportunity to interact directly with the Advantage R&D team and provide feedback on how Advantage is performing with your application. You also get a peek at some of the upcoming features.

After the first two sessions the next presentation was on the Advantage Full Text Search engine. An online seminar discussing Full Text Search can be download here.

The last session of the day was our product direction session. For this session J.D. Mullin discusses the plans for the next version of Advantage. You have to attend this session to get this information. However, you can see a list of the upcoming features on the DevZone. You can also see which features have been completed by going to the Advantage UserVoice page.

You can follow all the happenings at the conference on Twitter.

Monday, August 24, 2009

Advantage Technical Summit

SummitLogoWebMed The next Advantage Technical Summit begins on Wednesday in Washington DC. The summit will be in conjunction with the Sybase TechWave Symposium. I am really looking forward to seeing all of the Advantage Partners that have signed up for the event.

Although I have been a bit lax on updating this blog over the past few weeks, I will be adding several posts during the Technical summit. Also look for the rest of the tips from last month to be up soon. I'll be back to my regular schedule in September.

Wednesday, August 5, 2009

Tips Round Three Complete

The third round of tips is now complete. All of the tips ( 41 – 60 ) are listed below. The first round of tips were posted in November 2008 and the second round were posted in February 2009. All of the tips are tagged with Tips making them easy to find.

  1. Creating Tables with SQL
  2. Adding Tables to a Dictionary
  3. Removing Tables from a Dictionary
  4. Altering Table Structure
  5. Modifying Table Properties
  6. Getting Server Information from AdsConnection Object
  7. Getting a List of Tables on a Connection
  8. Get Database Info from AdsConnection Object
  9. Retrieving Database Objects
  10. Working with Connection Events
  11. Using the Advantage Crystal Reports Driver
  12. Opening a Crystal Report with Visual Studio
  13. Dynamically Assigning a DataSet to a Report
  14. Design a Table within the Delphi IDE
  15. Pausing Replication
  16. Using Advantage Online Backup
  17. Creating a Differential Backup
  18. Restoring Data
  19. Backup and Restore Options
  20. AdsBackup Utility Options

My plan is to get another round of tips published before the end of the year. Hopefully you found something useful in this round of tips. If you have any suggestions or ideas for future tips feel free to add your comments and suggestions.

Monday, August 3, 2009

FAQs – July 2009

Which Advantage Clients are Available in 64-bit

With version 9 of Advantage a 64-bit version of the Server was released. The 64-bit server can be accessed by any of the current Advantage clients. You do not need to make any changes to your client application, although using the same client and server version is recommended, to connect to the 64-bit server.

If you want to create a 64-bit client application you will need to recompile your client application. We currently have 64-bit versions of the ACE API and .NET DataProvider. We have plans to release 64-bit versions of our OLEDB provider, ODBC driver and PHP driver.

Using the Advantage .NET Data Provider on Vista 64-bit

64-bit operating systems support both 32-bit and 64-bit applications. In many cases the operating system will run either of these application types seamlessly. However, when developing on a 64-bit system you need to make sure that you are loading the appropriate DLLs and components for your application.

When developing a .NET application the key is setting the proper target platform. For a 32-bit application your target should be x86 and use the x64 target for 64 bit applications. The Advantage 64-bit .NET data provider will be installed on a 64-bit system by default.

One additional pitfall is in redistribution of the DLLs. You must distribute the proper version of the .NET data provider and Advantage DLLs with your application. Make sure you include the 64-bit versions of Advantage.Data.Provider.dll, Ace32.dll and axcws32.dll. You can verify the version (32-bit vs 64-bit) of the DLLs by ----.

Scott Hanselman has a great overview of 32-bit vs 64-bit with .NET on his blog.

9107 Errors

When the Advantage server is unable to close a specific handle a 9107 error is generated. This can occur when the server tries to close a static cursor or temporary table. Both static cursors and temp tables can be stored in temporary files (*.tmp) which will show up in the data directory or in the temp path as defined in the data dictionary. When the static cursor or temp table is closed the temporary file should be deleted.

If the temporary file cannot be deleted the server will get an error and a 9107 will be logged. This can occur if the file has been locked by another process such as a virus scanner. We recommend excluding the data directory from virus scanning since a virus scanner may scan a file on open and close. This should eliminate any 9107 errors and potentially improve performance since the files are no longer being scanned.

Using Mapped Drives

One way of ensuring that all clients have access to the same data is to map the same drive letter to a data share on all of the client machines. This can be easily done with a batch file that is run at startup. However, if you are using a server-side alias you cannot map a drive since there is no network share to map to. In this case you can create a virtual mapped drive using the ADS.INI file.

Adding a drive letter and a path to the [DRIVES] section of the ads.ini file allows any Advantage client the ability to use that mapped drive. This entry can point to an actual network share or a server-side alias. Make sure that you designate a letter that has not already been assigned to a local device or other mapping. If there is a conflict with a particular drive letter a 7078 error will occur.

Friday, July 31, 2009

Tip #60 – AdsBackup Utility Options

The AdsBackup utility is a command line tool that ships with the Advantage Sever and is located in the server directory, C:\Program Files\Advantage 9.10\Server by default. This utility has several options which can be specified to customize how your backup or restore is performed. The basic command line is below.

adsbackup [options] password> <source> [file mask] <destination>

Below is a list of the most commonly used options.

  • -a  Prepare data for a differential backup
  • -d  Don't overwrite existing tables
  • -f  Perform a differential backup
  • -i  Include file list
  • -e  Exclude file list
  • -m  Backup metadata only
  • -p  Password
  • -r  Restore the data
  • -w  Table type map
  • -y  Username for dictionary connection (adssys if not specified)

All of the details about the adsbackup command line tool, along with information about a java version, is available in the help file.

Thursday, July 30, 2009

Tip #59 – Backup and Restore Options

There are several options that can be used when backing up or restoring a database. Some of these were shown in other tips, however, I thought I would highlight them in a single spot.

  • PrepareDiff – Prepares ADT tables for a differential backup
  • Diff – Performs a differential backup
  • Include – Includes specified tables
  • Exclude – Excludes specified tables
  • DontOverwrite – Will not overwrite existing tables
  • MetaOnly – Only backs up the table structures no data
  • TableTypeMap – Defines the types of tables ( i.e. ADT, CDX ) when backing up or restoring free tables

These options are specified in the options parameter of the system procedures. Multiple options are delimited by using a semi-colon ( ; ). Lists of items ( i.e. Include, Table Type Map ) are delimited by commas ( , )

Exclude=_Remote1;PrepareDiff

For a full description of all of the options refer to the help file.

Wednesday, July 29, 2009

Tip #58 – Restoring Data

If you have some kind of failure that requires you to use a backup you must restore it. Restoring a data dictionary or set of free tables ensures all the data is ready for use. The restore process rebuilds all of the indexes and copies the data to the destination. You can restore data using Advantage Data Architect by right-clicking on an active connection and choosing Restore…

Like backing up data the restore requires a source and destination path. If you are restoring a data dictionary you must provide the adssys password for the dictionary. Choosing Restore… in ARC will bring up the Restore dialog.

ARC_Restore

You can select specific tables to restore from the list displayed on the Advanced tab. Like the Backup dialog the Preview SQL and Command Line tabs display syntax for using the system procedures or command line utility respectively. An example of using sp_RestoreDatabase, sp_RestoreFreeTables and the adsbackup utility are below. The examples are shown without any options specified

EXECUTE PROCEDURE sp_RestoreDatabase( 'C:\Data\Backup\SampleDB.add', 'password', 'C:\Data\SampleDB\SampleDB.add', NULL )

EXECUTE PROCEDURE sp_RestoreFreeTables ( 'C:\Data\Backup', 'C:\Data\FreeTables', NULL, NULL )

adsbackup –r –ppassword "C:\Data\Backup\SampleDB.add" "C:\Data\SampleDB\SampleDB.add"

Tuesday, July 28, 2009

Tip #57 – Creating a Differential Backup

A differential backup identifies the records that have changed since the last backup and updates an existing backup. This is different than an incremental backup, in that each differential backup is not stored as a separate file. Since the differences are applied to an existing backup you only have to restore once. Configuring a differential backup is a three step process.

  1. Create a full backup of your database/free tables
  2. Prepare the database/tables for a differential backup
  3. Perform a differential backup

You must have a full backup ( see tip #56 ) of the data prior to performing a differential backup. Once the full backup has been performed the database/tables must be prepared for a differential backup. This only needs to be performed once unless the table structures change or new tables are added. You prepare the data for a differential backup by specifying the PrepareDiff option.

EXECUTE PROCEDURE sp_BackupDatabase ( 'C:\Data\Backup', 'PrepareDiff' )

EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.ADT', 'C:\Data\Backup', 'PrepareDiff', NULL )

Now that a full backup has been performed and the data has been prepared subsequent backups can be performed with the Diff option. This will only backup the records that have changed since the last backup.

EXECUTE PROCEDURE sp_BackupDatabase ( 'C:\Data\Backup', 'Diff' )

EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.ADT', 'C:\Data\Backup', 'Diff', NULL )

Note: When running the sp_BackupDatabase system procedure you must be connected to the data dictionary as adssys or a user who is a member of either the DB:Admin or DB:Backup group.

Monday, July 27, 2009

Tip #56 – Using Advantage Online Backup

Advantage online backup is a powerful tool for ensuring that you have a copy of your database if your primary database gets lost. Online backup allows administrative and backup users the ability to create a backup of a database or set of free tables while the tables are open. It does not interfere with any current operations which may be occurring on the data.

You can start an online backup in ARC by right-clicking on a open connection in the connection repository and choosing the Backup… menu item. This will open the Backup dialog. First enter a destination for the backup, this can be any location that the server can get to. Remember that by default the Advantage service runs as the SYSTEM user account which may not have rights to network shares. After you have specified the backup location click the Advanced tab to select any additional options.

ARC_BackupAdvanced

If your database contains a lot of data and you are using ADT tables, it may be beneficial to do differential backups ( see tip #57 ). If you only want to backup specific tables simply select the tables you wish to backup. The Preview SQL tab shows the syntax for either the sp_BackupDatabase or sp_BackupFreeTables system procedures. The Command Line tab shows the syntax for calling the adsbackup utility with the specified options.

Bonus Tip: If you want to backup all but a few of your tables, for instance all but your replication queue tables, use the exclude option when calling sp_BackupDatabase or the adsbackup utility. The backup dialog creates the code using the include syntax.

EXECUTE PROCEDURE sp_BackupDatabase( 'C:\Data\Backup', 'exclude=__Remote1' )

EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.ADT', 'C:\Data\Backup', 'exclude=Demo1', NULL )

adsbackup –ppassword –e__Remote1 "C:\Data\SampleDB\SampleDB.add" "C:\Data\Backup"

Friday, July 24, 2009

Tip #55 – Pausing Replication

With the release of Advantage 9 the ability to pause replication was added. When replication has been paused all changes to the tables are still added to the queue table, but the changes are not sent to the subscriber(s). You can also disable replication which will no longer track changes.

You can pause replication in several ways. The easiest way is to open the data dictionary in ARC, right-click on the subscription you wish to pause and choose Pause from the context menu. You can also open the subscription properties page. From here you can disable or pause the subscription.

Subscription Properties

You can also pause or disable a subscription using a system procedure. Since these options are properties of the subscription object you need to use the sp_ModifySubscriptionProperty system procedure. Examples of pausing and disabling a subscription are below.

EXECUTE PROCEDURE sp_ModifySubscriptionProperty( 'Remote1', 'PAUSE', 'TRUE' )
 
EXECUTE PROCEDURE sp_ModifySubscriptionProperty( 'Remote1', 'ENABLED', 'FALSE' )