SQL Query returns no records with NI tools, works in Managment Studio

[DrSpaceMan]

Hi,

 

I am connecting to an SQL server running 10.5. Using LabVIEW Database Connectivity toolkit and/or TestStand's Database Viewer I can successfully open a connection and run a simple query which returns the proper records. A more advanced query and no records are returned and the properties show that the state is closed. Copy and pasting this query into Microsoft SQL Server Management Studio successfully fetches the correct records. Also running this same query in python using pyodbc returns the correct record.

 

The VI is straight forward. I attached the snippet.

 

My first thought is that the string contains some bytes in codes that causes the query to be interpreted wrong by NI's tools. Does anybody have experience as to why a query works in other environments but not in NI's tools? Again, a simple query did work, but a more complex one did not.

 

Thanks!

[ShaunR]

Change you SQL query control to "" codes display by right clicking on the control and selecting it.

[DrSpaceMan]

Thanks for the reply ShaunR.

 

I had done that but it doesn't change the functionality, nor lead me anywhere. The more I use NI tools, I think it's not the string "" codes affecting it. If you take LabVIEW out of the equation and use TestStand's Database viewer tool or more simply calling Database steps (Open DB, Open SQL Statement, Data Operation, etc.), records are not returned. This leads me to believe it's something in the implementation of the NI SQL interface.

[LogMAN]

Did you try to use a stored procedure instead? If this would return any records, the next step is to analyze the query for issues or inform NI about a possible bug. Also could you post the SQL query or an example that fails?

[DrSpaceMan]

I've discovered a bit more and it seems to deal with the DECLARE @<variable> statement. For example:

 

    DECLARE @FOO varchar(100) = '123456'    select BaseID, LotID, SubID, SplitID, InstanceID     from MyTable     where DrawingNumber LIKE @FOO and (LotID = 'EWR' or LotID = 'RWK') and Status = 'R' 

That does not work in LabVIEW or TestStand but it does work in other environments. Rewriting, the following works in LabVIEW

 

    select BaseID, LotID, SubID, SplitID, InstanceID     from MyTable     where DrawingNumber LIKE '%s' and (LotID = 'EWR' or LotID = 'RWK') and Status = 'R' 

and TestStand

    "select BaseID, LotID, SubID, SplitID, InstanceID     from MyTable     where DrawingNumber LIKE '" + Locals.PartNumber + "' and (LotID = 'EWR' or LotID = 'RWK') and Status = 'R' "

This indicates to me that somewhere between getting the string of the SQL query and actually giving that string to the SQL Server, something was altered. I tried escaping the @ string but that didn't help. It appears on some ni.com forums that they have issues with DECLARE but I'm unsure as to the degree. (http://forums.ni.com/t5/LabVIEW/SQL-Query-Works-in-MS-SQL-Server-2008-but-not-when-using/td-p/2151492)

 

I could use the Create Parameterized Query and Set Parameter Values VIs but that seems like a work around to translate a SQL query which typically works for use with LabVIEW. I'm trying to have one text document filled with queries that any environment can pull from.

[candidus]

As mentioned in the NI forum, the Database Connectivity Toolkit is essentially a wrapper around an ADO ActiveX object. ADO uses an ODBC connection internally and there are syntax differences when using ODBC compared to using MS SQLServer directly: @variable declarations are not supported via ODBC.

Edited November 21, 2013 by candidus

[LogMAN]

As mentioned in the NI forum, the Database Connectivity Toolkit is essentially a wrapper around an ADO ActiveX object. ADO uses an ODBC connection internally and there are syntax differences when using ODBC compared to using MS SQLServer directly: @variable declarations are not supported via ODBC.

 

This is very interesting. Do you know any particular page we could look up for that kind of stuff (like a comparison between MS-SQL vs. ODBC vs. ...)? My main source is MSDN as well as various pages I could find over Google.

[candidus]

Best resource is indeed MSDN:

On http://msdn.microsoft.com/en-us/library/yy6y35y8%28v=vs.110%29.aspx you can find information how parameters are used when working with OLE DB, ODBC or MS-SQL in .NET , ODBC in .NET is provided via ADO.NET which is similar to ADO ActiveX.

 

Most important, you can't declare parameters as @param, you have to use a question mark instead and pass all your parameters in the right order.

 

I stumbled upon that when I had to write a data table editor in C#.

[DrSpaceMan]

Great advice. Thanks everybody.

 

I was able to resolve this in a very very simple way: changed my provider in my datalink. I was using the more generic OLE DB provider but switching to the more native SQL server provider resolved it. This goes hand in hand with what candidus was saying.

 

Below is an illustration of the difference between the 2 methods of connection:

 

 

[Tim]

Hello everybody,
here is a solution that works for me.

Problem: ODBC Connection, Declare variables and get Result of "Select" SQL Statement

Hope it helps you

[Neil Pate]

Just to comment, I have also been able to get @variables working correctly in a SQL query using the NI DB toolkit using the SQL Server Native Client 11.0 driver.

 

[TDO]

Hey Guys,

I am facing a similar problem. I don`t really have experience in SQL statements, so a colleague of mine created a pretty complex SQL Statement for me to get some information of a postgre SQL table.

The statement should return some numbers and strings from the table. This works fine with a little tool, called HeidiSQL and in SQL Server Management studio from Microsoft.

If I use this statement with Labview it returns just the strings, but not the numbers. I have no idea, why it returns just the strings.

So, I hope here are maybe some SQL/LabView experts who can help me.

The statement is the following:

SELECT
     vk2.id,
     vk2_bestellnr,
     vk2_pos,
     vk2_upos,
     art_nrx25,
     art_bez_50,
     tb.text_block,
     vk2_liefmenge2,
     vk2_bestmenge,
     art_gewichtnetto,
     art_f1
FROM vk2
     LEFT JOIN ar ON 
           vk2_art_nrx25 = art_nrx25
     LEFT JOIN ( 
           SELECT 
                 text_schl,
                 text_sa,
                 STRING_AGG(text_textzeile, ' '||E'\n') AS text_block
           FROM
                 text
           WHERE
                 text_sa = '02'
           GROUP BY
                 text_schl,
                 text_sa
           ) tb ON vk2_art_nrx25 = text_schl
WHERE
     vk2_bestellnr = 1046421

Here is also the code snippet, where the statement is executed:

I would really appreciate if someone could help me.

Best regards

TDO

[VDB]

My educated guess is that you need to escape the string literals in your query:

STRING_AGG(text_textzeile, ' '||E'\n') AS text_block

See here: https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z0000019TrVSAU&l=de-DE

VDB

 

Edited October 4, 2023 by VDB

[TDO]

Hi VDB,

thank you for your reply. Maybe you are right, but I found this thread:

I downloaded and installed the library in this thread for PostgreSQL VIs.

The statement I posted above is working fine with the VIs from this library.