Views from an Advantage Evangelist

SWFox Final Day

2009-10-18T18:19:00.001-06:00

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.

I 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.

In 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.

Southwest Fox Day Two

2009-10-17T22:01:00.000-06:00

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.

SWFox Day One

2009-10-16T22:29:00.000-06:00

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.

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.

SWFox Conference Begins

2009-10-15T22:15:00.001-06:00

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.

Ready for SWFox

2009-10-14T07:00:00.000-06:00

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

Join the conversation

CloneDBC Screencast

2009-10-09T16:48:00.001-06:00

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.

Translating DBC Views to Advantage

2009-10-07T07:00:00.000-06:00

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.

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.

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.

CloneDBC Available with Advantage OLEDB Update

2009-10-05T07:00:00.001-06:00

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.

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.

FAQs – September 2009

2009-10-02T07:00:00.000-06:00

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.

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.

Delphi 2010 Support

2009-09-30T07:00:00.000-06:00

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.

Getting into Web 2.0

2009-09-28T07:00:00.001-06:00

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.

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.

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.

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.

Generating a Server Dump File

2009-09-09T07:00:00.000-06:00

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.

You 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.

After Action Review

2009-09-04T07:00:00.000-06:00

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)

Why? - The battery is dead. (first why)

Why? - The alternator is not functioning. (second why)

Why? - The alternator belt has broken. (third why)

Why? - The alternator belt was well beyond its useful service life and has never been replaced. (fourth why)

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.

FAQs – August 2009

2009-09-02T07:00:00.000-06:00

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.

Advantage Technical Summit Day 3

2009-08-28T23:48:00.000-06:00

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.

Advantage Technical Summit Day 2

2009-08-27T22:07:00.000-06:00

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.

Advantage Technical Summit Day 1

2009-08-26T18:05:00.000-06:00

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.

Advantage Technical Summit

2009-08-24T16:02:00.001-06:00

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.

Tips Round Three Complete

2009-08-05T07:00:00.000-06:00

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.

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.

FAQs – July 2009

2009-08-03T07:00:00.000-06:00

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.

Tip #60 – AdsBackup Utility Options

2009-07-31T07:00:00.000-06:00

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.

Tip #59 – Backup and Restore Options

2009-07-30T07:00:00.001-06:00

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.

Tip #58 – Restoring Data

2009-07-29T07:00:00.000-06:00

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.

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"

Tip #57 – Creating a Differential Backup

2009-07-28T07:00:00.000-06:00

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.

Create a full backup of your database/free tables
Prepare the database/tables for a differential backup
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.

Tip #56 – Using Advantage Online Backup

2009-07-27T07:00:00.000-06:00

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.

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"