Usually, it is symbolic of an omitted profanity when you see several special characters together in print, as, for example, $#@_. IBM i developers, however, probably recognize those characters as allowable characters in DDS and RPG field names. Because the IBM i has so readily accommodated the use of those special characters in both its data description language and its programming languages, there has generally been little thought given to the advisability of using or not using those characters.

A recent email caught me off-guard:

I cannot create an SQL statement where the field name has a $. I get the following message:

Error in SELECT clause: expression near '$'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Unable to parse query text.

A number of simple resolutions immediately came to mind, none of which worked. I'll describe those attempts a bit later in this article. For now, I'll show you the test environment that I created and the results of working with those $#@_ characters.

Create the test file

To get started, I created a test file using the DDS shown in Figure 1. This is a simple physical file, there is nothing tricky about it. I created FIELD1 as a "control" field, it has no special characters. FIELD2 - FIELD7 are variations of field names that begin with one of the allowable special characters ($, @, #) or have one of those characters embedded. FIELD8 shows the use of the other allowable special character (_), which can be embedded within a field name but not used as a starting character. The conventional Create Physical File (CRTPF) command was used to create the file.

To see how the file would work with SQL, I used the OS/400 Start SQL (STRSQL) command in a 5250 session. I then used the INSERT statement shown in Figure 2 to put some test values into each of the fields. This worked without error. A subsequent SELECT statement, also in the STRSQL environment, is shown in Figure 3. This worked as expected, displaying the contents of the fields.

At this point, I have a valid IBM i physical file that contains data. It can apparently be accessed using SQL Statements.

Moving over to Visual Studio

I now went over to Visual Studio 2008. For my first test, I created a new Data Connection using the IBM DB2 Provider for .NET (the V6R1 version; I have no reason to suspect that the V5Rx version would work any differently, in regards to the tests I show in this article). Using the Visual Studio Server Explorer, I started a new query. In the query SQL statement panel, I entered the SELECT * SQL statement shown in Figure 4. When I executed the query, all of the data was retrieved from all of the fields.

One of the first things you learn when working with SQL is that it is generally inadvisable to use SELECT * queries. Instead, it is better to list the columns that are needed by your application, the idea being that you usually need fewer columns for your application than are available in the table. So my next SELECT statement included all of the column names. When I tried to execute the query, the Query Definitions Differ message shown in Figure 5 was displayed, followed by the SQL Execution Error message shown in Figure 6.

At first glance, this seems to not make any sense: why could I successfully run the same SELECT statement in STRSQL, but I cannot run it against the same database file in Visual Studio?

The answer is, the parser used in STRSQL is not the same as that used in the .NET provider.

The phantom parameter marker

Now it turns out that the Query Definitions Differ message shown in Figure 5, although alarming, is innocuous. The real issue is identified in the SQL Execution Error message, in Figure 6. The SQL0418 error in Figure 6 states that the "use of [the] parameter marker [is] not valid". The IBM .NET provider treats names that begin with the @ (at-sign) character as being parameter markers, that is, substitution values within an SQL statement. The problem is, parameter markers are only valid in the WHERE clause. As much as we might like to create parameter-driven lists of columns, or tables to query, or ORDER BY clauses, we can't. So when the .NET provider sees the @FIELD3 column name in the SELECT column list, it simply gives up.

Alternatives, there must be alternatives!

Usually, when you come upon something that can't be done in a programming environment, there is some easy work-around. So the first thing I tried, since it always worked before, was renaming the column: SELECT @FIELD3 as asFIELD3...

With the exact same parameter marker error message. The .NET provider is not willing to "read ahead", if it sees that you are trying to sneak a parameter marker into the SELECT list, it bails out on you.

Another technique that works with SQL Server queries is to surround the column name with square bracket characters:

SELECT [@FIELD3]...

This time, a different SQL error message, informing me that the [ character is not a valid delimiter (according to the IBM .NET provider).

At this point, I was starting to get a little concerned, as the obvious work-arounds were not working. Digging into the MSDN Help file for information about parameter markers, Help suggested that Visual Studio provides a way to specify the character to use for a parameter marker and an escape character. Figure 7 shows the Database Tools, Query and View Designers options (you access the Options dialog by using the Visual Studio Tools, Options menu item). The parameter prefix and parameter escape character looked promising. I first tried setting the parameter prefix to the ~ (tilde) character, thinking that I could tell the provider to use that as the parameter marker character. The .NET provider simply ignored my attempt to mislead it, and stubbornly told me I couldn't use a parameter marker in a SELECT list.

I then tried to use the escape character, like this:

SELECT \@FIELD3...

And got the invalid delimiter message.

No way, no how.

Can I run my own program?

I wanted to see what this looked like when I ran my own code. So I created the test programs (Module1.vb, Test1.cs) to work with the SQL statement using the .NET provider, controlled by my own code. (You can access these test programs in SQLFieldNameTesterVB.zip and SQLFieldNameTesterCS.zip).

Figure 8 shows that I get the same result (SQL0418 error) when running my code. I also tried the variations here (@FIELD3 as asFIELD3, [@FIELD3]), with the same ill results.

Finally, in desperation, I abandoned the .NET provider and created Module2.vb and Test2.cs, working with the IBM OLE DB provider (in this case, the IBMDASQL provider). Success at last, as Figure 9 shows! Using the OLE DB provider, I can simply use my column names as they are defined in my IBM i physical file.

The reason why this works is not difficult to understand. The OLE DB provider uses the ? (question mark) as a parameter marker. That character is not allowed in an IBM i file field name, so there is no danger that it will ever be encountered in an SQL statement except as a parameter marker.

As you can imagine, it is more convenient when developing an application to use "named" parameter markers, rather than single-character ? parameter markers. It is especially nicer when you are working with several parameter markers in a statement; rather than just see a list of ? parameters, you see the field name, albeit the names start with the dreaded @ character.

How to work with those $@#_ characters

So you now have some examples of what does not work and some examples of what does work. In a nutshell, here are some of the options you have for developing SQL statements in .NET applications, when working with those $@#_ characters (in order from least preferred to most preferred as a solution):

1. Define a logical file on your IBM i that uses the DDS RENAME option to rename fields that include the @ character to something else. In addition to being an aberrant solution (that is, the solution is implemented far from the platform where the problem occurs), this is also bad news for your queries. My understanding is that SELECT queries over a logical file are routed to the "classic" SQL environment, not the enhanced SQL environment that has been present on the IBM i for several releases. Since the whole point of SQL is to get a result, quickly, from the database to the application, it is a major step backwards to use an ancient technique (logical files) when you have viable alternatives.
2. Use a SELECT * query if using the .NET provider. Since the provider does not have to parse the column names on the SELECT, it simply forwards the SELECT request to the IBM i database manager, which happily supplies all of the columns back. If you will really be working with all or most of the columns, this is workable. If you only want 2 or 3 columns out of 50, this is not a good solution.
3. Use the OLE DB provider to access tables that contain columns using @ in the column name. If you can develop your application using OLE DB, this is an OK solution. But if you wanted to take advantage of the many features of the .NET provider, you may end up using both the .NET and the OLE DB provider in the same application (do-able, but it sure makes a mess of your code).
4. Create a stored procedure on your IBM i and call it. There are many, many advantages to working with stored procedures. As you saw in the STRSQL tests, when the SQL statement executes directly on the IBM i, it does not matter if the special characters are used in the column names. With a stored procedure, you can specify the exact query you need and call it from your application, using the .NET provider. And that's how you deal with those $#@_ characters.