LiveCode Forums.

Still working on this project involving an ODBC connector, and now that I am able to connect to the database I have run into another issue.

One of the crtical columns that I need to query on has a period in it's name - "parent.change". Obviously I can't just use this raw, since periods in sql indicate schema. I still tried it though, and it returns nothing, even though I know there are matching records.

I have tried putting the column name in double quotes, in square brackets, and using the "_" wildcard in place of the period with no luck. The first two give no results, while the third (_) returns the first record in the database regardless of the value in my query.

Has anyone run into a problem like this befopre, and if so, how did you manage to solve it? It kind of amazes me that HP (the vendor of the software I am trying to work with) would use periods in the "out of the box" column names since it's obviously going to be a problem if anyone tries to use the ODBC connector.

Hi,

You probably need to use single normal quotes or curly quotes ‘ and ’. Sometimes it is possible to escape a character with either a backslash or a single quote in front of that character. Wildcards inside strings usually don't work for column names and I don't think the underscore is a wildcard.

Do you mean that HP is the creator of the database software, which isn't standard SQL? Then you should probably ask HP for help if this doesn't answer your question.

Kind regards,

Mark

Thanks for the ideas Mark - I gave them a try, but no luck.

The problem here is that what I am trying to access is the backend database for HP Service Manager - a tool for managing changes and service incidents. The backend is a proprietary, application specific database. However HP provides an ODBC connector that is SQL compliant.

The problem is that the default column names in the application's database use periods as their standard for separating words, so you get column names like parent.change, instead of the more usual parent_change. Since their native backend is not an SQL database and doesn't interpret "." as schema this causes their application no problem. However, for anyone attempting to use their ODBC connector with anything other that Crystal Reports (which apparently somehow works) this, of course causes huge problems.

I have been working with the support people at HP for over two weeks trying to find a solution to this, but they don't really seem to have one other than "Migrate the backend database to SQL Server", which I would love to do, but it's not my database and the people that do own it do not have any interest in doing so.

Very frustrating, since without being able to access the columns with periods I can't finish this app that would save my change reviewers hours of work per day...

I was hoping there was someway I could get around this, but it's looking pretty hopeless, and all because HP didn't stop to think "Hey! We provide an SQL compliant ODBC connector! Maybe we should use SQL compliant column names!"