cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Visit the PTCooler (the community lounge) to get to know your fellow community members and check out some of Dale's Friday Humor posts! X

Displaying Database Tables & Columns list on a mashup

aseaton
7-Bedrock

Displaying Database Tables & Columns list on a mashup

I have an MSSqlServer based thing and when creating a new service, selected as SQL Query/Command, the tables/columns tab allows me to include these into my script. How can i get this list of tables and columns from the database on a mashup?

database table and columns.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
supandey
19-Tanzanite
(To:aseaton)

Hi Andrew, not sure if you already tried this but I think you can simply write a service querying the schema for all it's tables and the columns. As to the script for getting all of that you could check this S/O link

View solution in original post

8 REPLIES 8
supandey
19-Tanzanite
(To:aseaton)

Hi Andrew, not sure if you already tried this but I think you can simply write a service querying the schema for all it's tables and the columns. As to the script for getting all of that you could check this S/O link

HI Sushant,

Thanks for reply. Yeah this gives me the set of tables that i require. Now i want to bind this data to a list and then make the list display as a dropdown box. When i do so and configure, the Display field and Value field cannot be selected.

I have given the service a datashape to format  the returned data but this has not helped. I know that when using a grid widget and binding data without a datashape that the 'Show All Columns' checkbox can be selected to correctly display the data in the mashup but there is no such checkbox with the list widget? is there any advice on this?

Thanks

supandey
19-Tanzanite
(To:aseaton)

To confirm if the Datashape is leading to right output I would test that service's output in a Grid just to see what is returned and how it's returned. I've not seen the code but it appears as though the services is likely not returning the result as expected.

Actually when the data is bound to the grid the data is shown correctly, but not the dropdown at the moment.

grid displaying database tables.PNG

supandey
19-Tanzanite
(To:aseaton)

Interesting. Would it be possible for you to share the DataShape Definition here may a screenshot of the DataShape. Also it would be helpful if you can elaborate a bit on what the service is returning like how many columns and so on.

Just to confirm when you are binding to the list are you taking All Data or are you binding only a particular column there? For DisplayField and ValueField to be configurable you'll need All Data.

The datashape is just a simple one column: Name.

database table s datashape.PNG

Here is the result from the service. Just a list of the table names:

getdatabasetables result.PNG

Yes, I am binding with All Data. In the Display Field in am selecting Name. I have also tried with the Value field but the results are not displayed in the list on the mashup.

I have corrected it now. The problem was that i had written the query as:

SELECT name FROM sys.tables

And the datashape is defined as having a column: Name

Since i have changed the query 'name' to 'Name' the list is visible.

Thanks for the Help.

Andy

supandey
19-Tanzanite
(To:aseaton)

Yeah it sounded something off with the datashape. Gald it worked out though.

Cheers.

Top Tags