MySQL - get Alias names from a SELECT ?

[hoburne]

Hi all,

Can anyone offer some advice above how to get the column names or more specifically, alias names from a query?

e.g..

Code: Select all

SELECT x, y, concat(x,"_",y) as z FROM MyTable

I am looking the get the names of the columns used and the alias returned to LC so that the column names are accurate when the results are put out to a data grid.
The query's are not hard coded or fixed in the application,they are entered manually by the user so the columns cannot be fixed to the data grid before running the query.

Thanks!

[SparkOut]

It looks ok to me - the only thing I could think of is to use single quotes rather than double quotes in case MySQL has ANSI_QUOTES set oddly.
Otherwise it may be the problem lies elsewhere. Have you a bit more script, and in particular, what error/symptoms are you getting?

[hoburne]

Hi SparkOut - Thanks for your reply.

The issue is the query works fine, returns the results expected but I need the query results to include the name of the alias.

In the example query above the result would be three columns of data [x, y, z]. When the result comes back from MySQL I simply output it into a data grid. This again works fine but the columns are called "Col 1, Col 2, Col 3" etc.. I need the column names to be accurate the table column name or the alias.

This part of the application simply allows the user to enter a SQL query into a text box, which is processed and the results are displayed in a data grid. So the code is really simple (which is probably part of the problem):

Code: Select all

--conRes = the connectionID made a few lines previous
--fldSQLin = text box to type query

put fld "fldSQLin" into gSQL
if gSQL is not empty then
  put revDataFromQuery( , , conRes, gSQL) into tData
  set the dgText of group "dgOutput" to tData
end if

This process works, the question is how can the record set include the alias and column names?

I could, I guess, scan through the query the user enters and collect the alias names before sending the query to MySQL. This would give me the opportunity to the adjust the query to include the names, but, given the simplicity of the code above, the fact that I simply want to parse the query to MySQL to handle; this approach seem Inefficient. The adjusted query would look something like:...

Code: Select all

select 'x', 'y', 'z'
union all
SELECT x, y, concat(x,"_",y) as z FROM MyTable

Confused!
Thanks.

[SparkOut]

Don't you already have that when you construct the query? Oh wait, you're letting the user type the whole SQL statement in field "fldSQLin" and then running that?
Wow, that's dangerous, I hope none of your users have a name like Bobby Tables. http://xkcd.com/327/
If you have any method for constructing the SQL in the fldSQLin field that is more controlled, you may be able to check the chosen column names at that point.
Otherwise I guess you would have to parse the query along the lines of:

Code: Select all

put field "fldSQLin" into tSQLToParse
   get matchText(tSQLToParse,"(?i)SELECT.(.*).FROM",tSelection)
   put tSelection into tColumnNames
   -- you would then have to parse the tColumnNames variable to split by comma delimited items to 
   -- get the individual column names
   -- you would also have to parse the string to extract the concatenated alias name for each column
   -- this would probably involve lots of checks for special characters and applying some regex

I think your energies would be best served by controlling the user input beforehand so that you can a) know what columns they chose in the query construction and b) know that mistakes or "playing" won't break your database.

[hoburne]

hahaha - great example. In a way this is kind of what I want to achieve. Part of this application is designed to allow users to break the db in a controlled manor. MySQL users are configured not to allow some commands and always resets after 60 minutes aways, returning the content to normal. Its kind of a practice/training system.
Thanks for your reply and I think you're correct, I'm going to have to do this the long way round. sigh!
Thanks and I appreciate your help

[phaworth]

Check out revDatabaseColumnNames in the dictionary. You'll have to use revQueryDatabase to create a db cursor to use it.
Pete