how to get recordset from database

[Softman]

Hi all New to NI forums and only been Labviewing for a month. I wanted to get a record set from a database and spent ages searching for a way to do it with examples but could not find what I wanted, so after a couple of hours created my own and thought for the good of the community I would share it in the hope it will help someone else. It will return a Record set with the option to include the column names in the first row of a multidimensional string array created on the fly from the tables you wish to read.

[Christian_Hartshorne_NIUK]

Hi Softman,

I would recommend that you post this code on the NI Developer Community as this may be useful for other developers.

Regards,

Christian Hartshorne

NI|UK

[Softman]

Hi Christian

Thank you.

Now I know where to post, just posted it there.

[Yamaeda]

If you use a Access database the Get property wont work if memory serves, and the double loop is overly complex, you only need the variant to string in the middle. 🙂

/Y

[Softman]

Hi

Thanks for your comments

I use MSSQL only but am interested in what you say, need examples to understand more.

[Yamaeda]

Then i can tip you about Column named which includes spaces ... the default kit doesn't work and 'fixing' it to add '[' and ']' (as i did) isn't quite recommended as there's no SQL standard for such. 🙂

/Y

[Softman]

Er don't know what you mean, just tried my routine with spaces in column names and it worked fine

Top row is column names from the SQL database.

[Yamaeda]

Does it also work if you do a Select with those column names? It might have been changed in later versions, but it caused me alot of head ache in 8.2 when it happened. 🙂

/Y

[Softman]

Well here is an interesting thing

The Select

Causes an error here

 Error being

As you can see it can be trapped out, but still runs to produce the list

But as you can see there is now a fault with the column names, as it has returned all the column names but I only wanted what was in the select

'SELECT [Primary Version],[Minor Version],TPname FROM TestPrograms'

I need to do a bit more work on how it returns only the columns you select, but it is working for the project I am doing so working on it again to fix the issue of selecting columns over return all ('Select * FROM TestProgram') will have to wait 😉