Sql column alias

I have the same version of Ignition (7.5) installed on two different machines, both of which are supposed to run the same code. One of the components calls a stored procedure which returns some data. I renamed some of the columns when I returned them. For example: SELECT user.id AS userid, employee.id AS employeeid … The Ignition component on one machine recognizes the column names as “userid” and “employeeid,” but the other one doesn’t. Any ideas on why this would be happening? Is there a preference setting or something in Ignition?

PS The database we’re using is mysql.

I need a few more pieces of information in order to help narrow this down:

  • You said you’re using Ignition 7.5, do you mean 7.5.0? Is that the stable release or one of the beta versions?
  • What type of component is calling the stored procedure, and which component is recognizing the column names incorrectly? Or are they one in the same?
  • Are you renaming the columns after returning data from the stored procedure, or does the stored procedure handle the aliasing? If you are doing the aliasing within Ignition, are you using scripting to run the query? A SQL Query binding?
  • Is this behavior happening in the designer, client, or both?

There isn’t a setting for this, aliasing should work fine.

Hmm, this is very odd. I’m wondering if it’s not something in mySQL.

–I’m using Ignition 7.5.1. I just downloaded it from the main download page two days ago – so that one.
–The component calling the stored procedure is a dropdown box. It’s really not the component doing the reading. In the property change event handler for the dropdown box, we call a stored procedure to retrieve values from the database associated with the user’s selection. We then pass those values to tags.

call = system.db.createSProcCall(“sp_locationdpdwn”)
call.registerInParam(1, system.db.INTEGER, locationid)
system.db.execSProcCall(call)
rs = call.getResultSet()
results = system.dataset.toPyDataSet(rs)

for row in results:
location = locationid
uwi = row[“LSDdwn”]
pumptypeid = row[“pumptypeid”]
system.tag.writeToTag(“Pressure/Pumptype”, row[“Name”])

The procedure returns columns called LSDdwn, pumptypeid, and Name. Name is a rather generic word, so I aliased it in the stored procedure by calling it “ptName”, as in "SELECT pt.Name AS ptName ". If I change that last line to “system.tag.writeToTag(“Pressure/Pumptype”, row[“ptName”])” it throws an error message that says “Column name ‘ptName’ not found.”

– It happens in both the client and the designer.

I tried replicating your issue without success - aliasing within a stored procedure returned a proper dataset to Ignition. Something weird has to be going on within MySQL or the stored procedure, do you mind posting the stored procedure? That error should only happen when referencing a column that doesn’t exist in the dataset that was returned, so it looks like the dataset didn’t return what you were expecting. Also, which version of MySQL are you using?

I doubt the issue is with the stored procedure, because it’s running on three different computers, all with identical copies of the stored procedure and Ignition front-end code. On two of the computers the aliasing works perfectly. It’s just one stray computer that it doesn’t work on. They’re all running the same versions of Ignition and mySQL. Since you said there isn’t an option or anything that could have been turned on or off in the Ignition designer, I’m guessing it’s something to do with mySQL. I’m not sure what else it could be.

The mySQL version on them is 5.5, by the way.

Ok, if you can access the computer that is having issues, let’s take a look at what the dataset is returning. After calling the stored procedure that uses the alias, put this code in after call.getResultSet():

for x in range(rs.columnCount): print rs.getColumnName(x)

Are the column names what you expect? Also, 5.5 should be fine, I found a bug report for someone in 5.1 having issues with MySQL not returning proper column names from a stored procedure.

Running that code you gave me, it prints the non-aliased names, such as “Name,” rather than “ptName.”

Try using the MySQL Command Line Client with the following commands:

use db_name; call sp_locationdpdwn();

Replace db_name with the database that contains the stored procedure. In the call command, you will need to put parameters in the parentheses if your procedure is expecting them.

I get the correct aliases running the query that way.

On the computer that the aliasing isn’t working, which JDBC driver is listed for MySQL? Go to Databases -> Drivers in the Ignition Gateway under the Configure tab, and post which JAR file is listed for MySQL. Also, on that same computer, has this computer been upgraded from previous versions of Ignition? Or is it a clean install? Upgrading from previous versions may not install a new version of the JDBC driver, and it is possible that an outdated version of the JDBC could cause issues.

If you do need to install the new JAR file, follow the following steps:

  • Download the newest JDBC driver from the MySQL website: dev.mysql.com/get/Downloads/Conn … mysql.com/
  • Extract the mysql-connector-java-x.x.x-bin.jar file somewhere on your hard drive
  • Go to Databases -> Drivers under the Configure tab in the Ignition Gateway
  • Click Edit next to the MySQL JDBC driver
  • Under Main Properties, click on the “Choose File” button in the JAR file section
  • Navigate to the folder where you extracted the jar file, select that file
  • Save the changes by clicking the “Save Changes” button on the “Edit JDBC Driver” page
  • Restart the gateway

Hopefully that will solve the issue.

Shoot! I was hoping that would work, but it didn’t. :frowning:

The computer that it isn’t working on was upgraded to 7.5 from Ignition version 7.4, whereas the other two were upgraded to 7.5 from version 7.2. Since you mentioned the way in which they were upgraded, I uninstalled Ignition using the Ignition uninstall program and re-installed it. Both before and after doing that, I tried changing the driver to the latest one on the mySQL web site, which was 5.1.21 and restarting the service. I also tried mySQL connector 5.0.8, just in case. The previous version on there was 5.0.6, so both were an upgrade.

Try adding the following parameter to the Extra Connection Properties section of the Edit Database Connection page on the Gateway:

useOldAliasMetadataBehavior=true

If that doesn’t work, which version of the JDBC driver are the machines that upgraded from 7.2->7.5 using? Try using that same jar with the machine that isn’t working.

I entered the “useOldAliasMetadataBehavior=true” connection property and it worked. Yay! Thank you so much!