Tell Me: Griffo Info

I have a program I am trying to get ready for potential users to download and test. This is a program developed in Clarion For Windows (6.3). The testing database is a MS-SQL2005 system which is hosted on a shared server at discountasp.net and the server is located in Los Angeles. My users will most likely be located in Australia.

The problem I saw was the initial time it takes to make a connection to the remote database, and then a further delay with each Table I was opening. These turned out to be two separate issues.

Initial Connection:

The time taken to establish the initial connection was in the order of 20+ seconds. The connection was being attempted before the main APPFrame screen was being displayed. So expecting someone to start a program and wait 20+ seconds before the first screen response was not going to work. This lead me to build a separate “Starter” program with just one screen that received staged updates from the main program, and then once the AppFrame opened, the “Starter” program simply closed, or displayed any error applicable. The Starter program also had a Seconds Elapsed display and a notice that they may need to wait 20 seconds to connect to the database.

But why was this initial connection taking so long? I could fire up MS-Studio Express and connect in about 4 seconds. It was only the Clarion program that was very slow to connect. This I would research further and eventually solve. See below.

Slow File Opening:

The time taken to open a Browse/List screen was also slow. It was taking about 5 seconds per table to open. Again, this is just too slow. I solved this by adding the Driver switches /FASTCOLUMNFETCH = TRUE to most tables. That has helped. I also set the program to Keep Files Open and I opened a few of the larger tables (those with more columns, not rows).

Testing:

After spending several days fiddling with the various Driver switches, I was resigned to the fact that it would take 20+ seconds to connect. I started looking for another closed SQL server that would be hosted in Australia. Then, along came Christmas and I took some time off from the development computer. I was relaxing out-doors and just for kicks, decided to try running the program from a 10″ netbook with an Atom processor and on a local wireless network. I started up the program, which worked just fine, and got connected to my Los Angeles database server in about 4 seconds. Could not believe my eyes! Looked at the data and yes, this was indeed coming from the LA database. This little netbook had no database stuff loaded, no MS-SQL and had very few programs installed. Basically it had IE and Firefox for travelling.

So, back to the development PC which could not be 20 seconds slower than the netbook.

I tried several things… Stop all running MS-Servers, Stop a bunch of system services that were running (pretty much anything that looked like a network service). Still I could not get any faster connection from the PC.

Next I remembered someone on the Clarion Database forum had mentioned SQL Native Client. This certainly was not installed on the netbook. But it was on my PC. I had another notebook PC laying around that did have some SQL stuff installed. It was running MSSQL2005 database. I tried running my program on that notebook and it also would connect in 4 seconds. The laptop also had MS SQL Native Client installed, albeit an older version of SQLNCLI.dll. OK, after backing up my newer SQLNCLI.dll I copied the older one from the Laptop to my Development PC. Testing the program again I was still taking 20+ seconds to connect. Then I renamed the file SQLNCLI.dll (located in \windows\system32 ) and tried again. This time I finally managed to connect in about 4 seconds. I put the original SQLNCLI.dll back and connection time again went out to 20+ seconds.

So there was a problem with the SQLNCLI DLL that was causing the problem. I researched this in several places on the internet and found one other report of this problem. But there was no resolution there.

Next I went to Control Panel, and un-installed SQL Native Client. Now I was back to 4 second connect times. OK, Let’s put the Native Client back in. So I downloaded the latest MSSQL2005 version( which turned out to contain the exact same DLL that I was having problems with, and now I was able to connect in about 4 seconds each time the test program started.

MS SQL Native Client (or a related setting) had been the problem all along. By removing and re-installing I had solved the major connection problem. But I had wasted about 4 days getting to that stage! Oh well, that’s computing.

John Griffiths

It looks like we will have Clarion 7.1 released in the next two weeks. A new announcement has been posted here

I am looking forward to all its new features, from a programmers point of view.

And this should flow through to some happier users. There will be quite a lot of conversion and testing to do, but it should all be worthwhile.

John Griffiths

After seeing Carbonite mentioned on a newsgroup, I thought I would try it. They offer a 15 day free trial. And then US$55 per year to store unlimited backup files on their servers.

I selected a few folders and let it rip. Took about 4 hours to upload my selected folders.

Then it was SUPPOSED to upload any altered files “soon” after they were altered. I had several files that were not uploaded again more that 24 hours later. This is not what they said in their spiel. It says the files will be uploaded whenever the running service detects in-activity on the PC. Well, I left it in-active for a couple of hours and still no upload.

Actually, here is what they say on their site:

When your computer is idle, Carbonite automatically backs up new and changed files. You don’t have to do anything! When you’re using your computer, Carbonite goes to sleep so it will never slow down your computer or internet connection.

I would be loathe to try it again. It is just not for me. But it may well suit someone whose files are not altered too often. If you think you may like it, go ahead. But check that your files are actually being uploaded once changed. The initial upload went very well, but not the uploads about a week into the trial.
I give it a rating of 2 out of five

John Griffiths

UPDATE: 20091005

Chasing down the problem where a changed file is not backed up automatically as advertised by Carbonite, I have found this in their FAQ area…

When Carbonite detects that a file has been modified, it checks to make sure that the file has remained unchanged for 10 minutes before the file is made eligible for backup. (By default, a modified file is backed up only once every 24 hours so that you’ll have the opportunity to restore the previous version if necessary.)

That would be why then! Carbonite goes on to say that i can manually right-click the file and select to force it to “Backup as soon as possible”. That sure conflicts with their sales pitch shown above, where they say:-

When your computer is idle, Carbonite automatically backs up new and changed files. You don’t have to do anything!

So it is not very suitable for situations where an important file (or group of files) are NOT automatically backed up!

A few weeks ago I ordered an external USB 1 Tb hard drive. This was to replace a 250Gb drive that I use for one of my backup regimes.

It costs less than US$90 and seems a little quicker than my older 250Gb USB drive. This is probably because it is 7,200 RPM in lieu of 5,400 RPM. And I think it has more internal cache memory.

I did not really need the extra room, but perhaps I will one day… The price was right, so why not have more free space available for backups.

Specs of the new drive are: SimpleDrive 1Tb Hitachi HDD.

Backups

Whilst on the subject of backups – I have recently trialed a few simple backup tools that let me schedule backups to remote FTP sites. Two I like are “Backup4All-Pro” and “GFI Backup 2009″

BackUp4All is about $49 and you can get it here

GFI Backup is free, and you can get it here

If you are looking for off-site backup space, you may look at DriveHQ

John

If you have a Gateway laptop and it ever fails to boot, then you are pretty much on your own.

I recently tried to get help for a family member who has a Gateway Vista laptop. it is about 3 years old. Now it will not boot. It does not even get through the progress bar on the initial “Gateway” screen. The progress bar stops about half-way.

There are on-screen option… F10 or utilities, F2 for BIOS.

Using the F2, the system start showing BIOS and Processor and memory info. Then locks up.

I tried on-line chat support from Gateway, and then 24Hr email support from Gateway. The common responses were just cut-and-paste sentences from some resource the responder has. In neither attempt (chat with their “expert”, or their email tech responder) were any of the concerns I outlined addressed.

Result = Pay $100 for 30 minutes phone support. This would only result in a wasted phone call. The machine is kaput! Their help sucks BIG-TIME.

Stay away from Gateway products as their support is pretty well non-existent.

It is a sad day when tech support has dwindled to such a low level. Bad Gateway!

I was doing some extensive software testing in a program that talks to a MS-SQL2005 database. I detected that a table INSERT/UPDATE Trigger was seemingly not firing.

I scrutinised the trigger code and could see nothing wrong, and know that it has been functioning well for several years in other applications, using both MSSQL2000 and MSSQL2005.

So what was different in this program? I searched the web and did not find an answer.

I added some code to a test program to work around the issue. That is when I started catching this error message:

Here I was trying to CALL a stored procedure to update some balances. Again, the stored procedure has been working great for many years with no change. So, what was this message all about? Back to the web again and still no resolution.

That is when I dug deeper and added some more tests into a couple of other test programs I wrote, each of which tried different approaches to see what was really happening.

Strangely, some tested OK with no errors, and others threw the same error! So, what was the difference?

The Answer

The original error it seems is caught by the SQL Native Client ( or perhaps Microsoft Data Access Components (MDAC) ) and translated for the ODBC file drivers I was using. A translation (or miss-translation) is performed and a meaningless message is captured by the ODBC based drivers and passed back to my program as “Executing SQL directly; no cursor”.

Comparing the test programs I was working with pointed me to the answer. It was all to do with permissions! Nothing about “Executing SQL directly; no cursor”.

I found in some programs where I was using Application Role permissions to access the backend SQL, that the Application Role I was using did not have EXECUTE permission on the stored procedure.

Back into MS SQL Server Management Studio I went, and ran this command:

GRANT EXECUTE ON [dbo].[jg_AllBalances11] TO [LMGaus]

Now all my test programs began behaving as expected.

Also, my TRIGGER began working. The trigger was calling the stored procedure to do some of its work, and so now it too performed as expected.

Next time you see the

Executing SQL directly; no cursor

error, check all your permissions!

Hope this helps someone.

John Griffiths

Recently I was invited to join a group of volunteers working on a software development project. As there were to be more than two in the group, I could see problems working through a swarm of emails, cc: emails and all the replies…

That is when I started researching web based Project Management Software.

As the project was to be a low-cost exercise, I think I have found the perfect answer.

We needed three components:-

A domain name ($10 p.a.)

A Site Host ($72) But prices can range from almost free to whatever you want.

Project Managment Software ( Free )

The Site Host:

=============

If you shop around, you can save on the Site Host account. Or even find a free one, but that is not recommended. You may already have a host that will allow an add-on domain at no extra cost. And, make sure that any host offers CPanel access, and not a  management tool they built themselves!

Project Managment Software:

==========================

The site host we selected provides dotProject and PHProjekt system installs via the Fantastico within cPanel.

We went with dotProject

I am truly amazed at the features and functionality in this free software product. You really need to try it to see what it will do.

It is PHP driven and uses a MySQL database for content storage.

I had a quick look at PHProjekt and it too looks great, but we had to choose one so we went with dotProject.

Review of Acronis Migrate Easy

Link: Acronis

OK, we have been battling for several months now with a 4 year old PC (Windows XP) that my Wife uses. The 60GIG hard drive was close to capacity. It was so close that we could not even perform a Defrag!

I had attached 250GIG pocket USB drive and had directed most internet downloads to go to the external USB drive.

She is heavily involved in her love of movies and so does voluntary work for FilmSpotting and their podcast productions. (plus her Blog stuff at MagneticNorth )
This involves downloading large video and sound clips of films. Hence the large accumulation of data on her hard drive.

I knew that the hard drive would need replacing. I dreaded managing getting all her programs and MS-Win-XP onto a new bigger hard drive. Then the crunch came when downloads and FireFox and many other programs began to complain about the HD free space.

Then I learned about Acronis Migrate Easy. I read on-line what was promised. I read several user comments. I believed none of them because of my long term relationship with MS-Windows and programming. But Hey! It is only a PC where I am not doing real development work. Something needs to be done, and it needs doing now. So why not take a small chance and try it out.

The Process:

Downloaded the 15 day trial software, installed it (only just, given the space limitations on the existing HD). Went out and bought a new 500Gig WD hard drive and set it to “Slave”, Installed it, and then ran the Acronis program. I opted for the ‘Automatic’ process (I think the second on the menu) and reboot, watch, walk-away, come back in 50 minutes, and it reported success and was done.

Then I removed the origingal HD, re-configured the new 500Gig HD as IDE Primary (using the jumper block on the back of the HD unit).

Feeling 100% confident, I put all the panels back on the PC case, plugged all the cables back in, and turned it on.

VOILA! All worked perfectly and now we have a PC with enough spare space for several more years! I had been ready with the original Win XP Install CD and activation key, but neither were needed.

Summary:

If you need or want to substitute a newer larger HD to a PC, then go with Acronis. I give it 10/10 for meeting expectations.

John