sql with multiple strings

[Wildmind]

I have a Microsoft SQL 2008 R2 database which contains a single integer, 3 varchar(max), and 4 floats within a 'Configuration?' table. In LabVIEW 2010SP1, the data from the table is collected with a simple 'SELECT * FROM AssemblyBench' statement which is then sent to a 'Database Variant to Data' object. The integer and floats are returned but only the "StringDefault" string. The other two strings are always blank.

Below is the SQL statement used to create the table within my database. The attached image is the SQL block diagram.

CREATE TABLE [dbo].[Configuration](
    [Index] [int] NOT NULL,
    [Description] [varchar](max) NOT NULL,
    [StringValue] [varchar](max) NULL,
    [StringDefault] [varchar](max) NULL,
    [FloatValue] [float] NULL,
    [FloatMinValue] [float] NULL,
    [FloatMaxValue] [float] NULL,
    [FloatDefault] [float] NULL)

I would appreciate if someone could explain why all the strings are not outputted and if there is either a fix or work around to this problem

Regards

[sletrab]

Hello,

I am still using LV2009. But I found an Example where exact your Code works (see attachment).

When you set a probe on the variant Data. Can you see the String data? Otherwise it is not a problem of conversion.

I can not see the Cluster element names. Element Name of the Cluster and Column names of the table match?.

[Wildmind]

I have tried the 'DB Tools Select Data VI' as per your diagram and the 'DB Tools Execute Query\DB Tools Fetch Recordset Data' VIs and it appears using probes that there is no data returned within the first or second string, so you are right in the fact it is not the 'Database variant to data' VI which is the problem.

An example of the probe details provides is as follows

[<Variant: Value -> 3>,

<Variant: >,

<Variant: >,

<Variant: Value -> 4th>,

<Variant: Value -> 0.000E+0>,

<Variant: Value -> 1.000E+0>,

<Variant: Value -> 2.000E+0>,

<Variant: Value -> 50.000E+0>]

Microsoft SQL Server Management Studio Express shows that data does exist for these two string columns. In your example, did you use varchar(MAX) data types within your table and was it Microsoft SQL Server 2008 R2?

[sletrab]

Hello,

Can you provide your Code and a picture of the data of the database table?

My first picture was an LabVIEW example: C:\Program Files (x86)\National Instruments\LabVIEW 2010\examples\database\Convert Database Variant to Data.vi

In the picture you see the frontpanel of the VI. I added a 1D array of variant ("Show Type" is activated). I delete the 3rd cell value in database (it is Access). Now the value is NULL. Is your value also NULL?

Do not care about the differend database. I worked with both, in this case it makes no difference. And do not care about [varchar], because you get the Value "4th".

Your table definitions says that there can not be NULL in the 1st String value, hm.

I think there is something different with your database table. Look at the second picture. A quick look at the whole table ...

[Wildmind]

By luck, I have managed to find the solution. Setting the cursor type to anything other than 'forward-only' allows it to work with the SQL database. The value does not appear to matter when using an access 2003 mdb database.