I have a stored procedure which runs fine in mysql 5.0.
When I call it is servoy using:
var ds = databaseManager.executeStoredProcedure(‘sprocket’,strQuery,arrParams,iArray,1000);
I get a dataset with data fine, but if I try to get the column names using:
ds.getColumnName(c)
all I get is col1, col2, col3 etc.
Can I do anything about this?
In this case I believe it is because there is no information to return. In running a stored procedure, there is no guarantee that a column within that returned dataset necessarily comes from a database/table column. The stored procedure could be setting up a variable that it fills from different sources depending on the criteria set up within the stored procedure. I’d just look at the stored procedure itself to figure out what, if any, table.columnname is returning the particular dataset.column.
For the most part, I believe the dataset.getColumnName(number) is designed to give you the columnnames if you run a ‘select *’ on a table as per the example in the docs.
The problem is, the stored procedure dynamically creates different columns based on the incoming parameters, so I can’t just hard-code the names of the columns. Does anyone know how to get the column names of a stored procedure from within servoy?
I imagine I could write a separate peice of code that would create the row names as they are created in the stored procedure and then could write even more code to merge the two together in servoy – but I’d like to avoid doing that if possible…
Does anyone know how to get the column names of a stored procedure from within servoy?
I don’t think I expressed it very well but the point is that the output of a stored procedure itself won’t have the column names so there is no way for Servoy to be able to extract them. As you mention, in your stored procedure the columns are variable depending on what criteria are met within the stored procedure. Plus there is no guarantee that the contents of a stored procedure output column is a database column at all. I would look at the documentation of your particular database. I seem to remember being able to dynamically ‘name’ the column for stored procedure but a) I think that would be database specific and b) I don’t think you could use the .getColumnName function to retrieve that. You’d have to use the array functions for that.
John,
I don’t know why you are saying “the output of a stored procedure itself won’t have the column name”.
If I call the stored procedure from a sql tool I see the column names. If I run the stored procedure with Microsoft Access I see the column names. Why do you say that a stored procedure output doesn’t have column names?
<You’d have to use the array functions for that>
The problem is, there are no column names in the dataset array that the executeStoredProcedure method returns.
Sorry, Glenn, about that. I guess I have it wrong. I wanted to check a number of my stored procedures on my main server setup before getting back on this. I think I just always use SPs in a way where there never is a column name per se because it is some variable assigned within the stored procedure and/or I am working directly with the returned data and know what the columns are. But looking at one of my SPs I see that in fact that one at least does return the column name. So someone with better knowledge than me will have to answer that question as to whether there is any possibility of extracting the column name out of a stored procedure dataset within Servoy.