Dragon Lake Books

The home of great stories

SQL

Avoiding Transaction

by Leave a Comment

There are times in my job that I wished I had photographic memory. I primarily code in Microsoft Visual Basic for Applications (VBA) and in MS Access SQL (or other flavours depending on the client.)

As with all programming langauges, there are a lot of words to memorize. I suspect that I only use a small fraction on a daily basis.

For comparison, did you know that according to Oxford Dictionary there are 171,476 English words in current use.

Robert Charles Lee writes on Quora that the average adult native speaker of English knows 20,000 to 35,000 words. Also, that “3,000 words will cover 95% of everyday writing.”

Of course, some of those words we use are ones that are not always appropriate in certain situations…

There I was, trying to run a Stored Procedure (a SQL command I’d saved in MS Access) from VBA and all I got was this error:

“The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.”

Okay, what does that mean, apart from the obvious? The SQL was working fine when I ran it directly within the Access database. I only received the error when running through VBA.

I went and poured myself a coffee. Decaf…

Reserved word, misspelt or missing argument name, incorrect punctuation… One of those must be true, but why was the SQL query running on the database?

I took a closer look and finally realised what I’d done wrong. The query was returning a list of Transactions and so I’d called the name of the Transactions: Transaction. I searched online and found this handy list of Reserved Words.

Transaction, of course, is reserved. If you know more than basic SQL that is probably pretty obvious. A feature of SQL is that it allows you to combine commands within a “Transaction” which can then be rolled back if something goes wrong.

So, using Transaction as a column name is not allowed. What I’m left wondering is why MS Access allowed it… Maybe one for another day.

A final analysis

by Leave a Comment

While I’ve been working as a software developer, in one form or another, for the past thirteen years, I know I’m still learning.

At the end of the project I’ve been referring to for the last couple of days, I looked back to see what I could learn.

Estimating. Originally I quoted 15 days to deliver the application being asked for. A week into the project I was advised the system being asked for was more complex than originally envisaged.

Estimating is an area I’ve had no training in. At college a lecturer once told us: when estimating, think of a number and times by three… I think there was a fair bit of wisdom in that statement…

I think 15 days was a fair assessment of how long the original project would have taken. With the clock ticking though, I felt pressure to give a quick response to how long the extra work would take. I underestimated it…

If a similar situation happens again, I should add the time it takes to carry out a new estimate to the length of the original project and make it clear that the original project will now be delayed by however long it takes me to analyse the new requirements.

I should recommend that the original project is completed as agreed (if a short term project.) Longer projects have more scope for flexibility and change. Short projects have very little opportunity for massive changes.

I should allow as much time as needed to work out both how much new work is required AND how much time will be required to undo previous work and integrate the new design.

While it is scary to propose an estimate that vastly increases the cost and time, it is better for everyone to be upfront and honest about this. The client can always decide to drop the new requirements. They may have delayed the original deadline and paid for analysis that is now no longer needed, but they would have had to pay someone to do that analysis and it might as well be me (or you if in a similar situation.)

Or they may decide to accept the revised estimate and in that case, as the developer, completing the project will be less stressful if sufficient time has been allocated.

Do you provide estimates for your work? How do you ensure your estimates work for both you and your clients?

Analysis of a Project

by Leave a Comment

Back in 2013 I completed one of the largest projects I’d tackled to date and decided to work out what I’d actually managed to produce during four months developing a complex business application. I realised then that I’d actually produced the equivalent of a book with all the code, queries and user documentation I’d written. That realisation was pivotal in helping me believe I could finish my first novel.

Last week I completed a much smaller project, one that only took 17 days and decided to carry out the same analysis.

The final application had:

  • 12 user forms, each providing a different type of functionality to the users.
  • 3635 lines of code.
  • 13 database tables
  • 57 SQL queries.
  • 28 user guide.

Converting those lines of code into pages, that’s 95 pages, plus effectively 57 pages for the SQL queries and the 28 page user guide. A total of 180 pages, not including the forms or database design. 180 pages equivalent, after editing and testing… A proof read, formatted, final draft…

Divided by number of days on the project, that works out to just over ten pages a day, full time. So, in theory, if I could match that level of output for my fiction writing, I could potentially write a 300 page novel in 30 working days, or in six weeks.

Writing that last sentence, I still find it hard to believe that it’s possible. But, it’s worth noting that I’ve been working as a software developer for 13 years now. Enough time to have honed my skills, to have learned the methods and routines that allow me to tackle complex projects.

If we keep working towards developing our skills and experience in areas we want to improve, we will see progress. Often, there is no substitute for perseverance. For taking the long road, building discipline and habits. This is part of the reason I’ve set myself the challenge of writing at least 100 words a day for 100 days.

What goals do you want to achieve? What skills will you need to achieve them? Will you commit to developing those skills, day in and out? If you do, one day you’ll look back and be amazed at how far you’ve come.

When the SQL twists and squirms

by Leave a Comment

What do you do when something just stops working for no apparent reason?

I’ve been developing an application for a client for the past few weeks, one that records answers to a set of questions. A couple of weeks ago I was asked to add a process to automatically set the answers to a default value if the question was not asked. It’s not standard practice, but sometimes the rule that the customer is always right has to be followed.

It was working fine. Tested okay. I moved on.

Today that process stopped working.

Just stopped. Dead. No error messages, it appeared to work okay as far as the code was concerned, but no answers were written to the database.

I should say I’m developing this using Microsoft Access and Excel. My favourite rapid application development suite.

Here’s an example of the original SQL code (dynamically generated when the process runs):
INSERT INTO tbl_Test_Answers ( FK_Test, FK_Answer, Test_Run, FK_EnteredBy, Date_Entered )
SELECT 4, tbl_Answers.PK_Answer, 1, 1, #25/05/2017 14:32:56#
FROM tbl_Answers INNER JOIN qry_Questions_Live ON tbl_Answers.FK_Question = qry_Questions_Live.PK_Questions
WHERE tbl_Answers.Answer='Test did not ask me this question' AND qry_Questions_Live.Question_Order>5

Now, I have a confession to make… The code didn’t just stop dead. No, as usually happens in situations like this, I’d made a change. Here’s the SQL code after I made those changes:
INSERT INTO tbl_Test_Answers ( FK_Test, FK_Answer, Test_Run, FK_EnteredBy, Date_Entered ) SELECT 10, tbl_Answers.PK_Answer, 1, 1, #29/05/2017 20:44:30# FROM tbl_Answers INNER JOIN qry_Questions_Live ON tbl_Answers.FK_Question = qry_Questions_Live.PK_Questions WHERE tbl_Answers.Answer Like '*Test did not ask me this question' AND qry_Questions_Live.Question_Order>5

Can you spot the difference?

Hopefully fairly obvious even if you’re not a SQL developer. Instead of =’Test did not ask me this question’ I’d changed the SQL to Like ‘*Test did not ask me this question’

This allows the code to look for variations like ‘1. Test did not ask me this question’ or ‘5. Test did not ask me this question’

Originally I’d not been told the answers could be numbered. Then I was, but it was inconsistent numbering so had to cope with variations. Fair enough. The Like command solves that and the * wildcard allows for variable length of text beforehand.

At least when running SQL directly on the database.

Not, apparently, when running via an ADODB connection from VBA…

Too many technical terms? Sorry… This isn’t supposed to be a technical blog.

Anyway. There are two wildcards you can use in SQL: * and %, but my understanding is that only * works in Access. I tried the % wildcard and it worked fine over VBA.

There’s a very useful explanation on Stackoverflow:
https://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign/720896#720896. Thanks to David-W-Fenton for pointing me at that.

Lessons learned?

When something was working and then breaks, it’s usually because something changed.

Just because something works in one environment doesn’t mean it will work in another. Always better to test.