Jump to content

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


Recommended Posts

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!

post-47092-0-71009800-1384888322_thumb.p

Link to comment

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.

Link to comment

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.

Link to comment
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.

Link to comment

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#.

  • Like 1
Link to comment
  • 2 weeks later...

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:

 

Screen+Shot+2013-12-03+at+9.08.14+AM.png Screen+Shot+2013-12-03+at+9.07.27+AM.png

Link to comment
  • 2 years later...
  • 1 year later...
  • 5 years later...

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:

SQLQuery.jpg.ab1b8d44d53b0b826e3bde709f45b1bd.jpg

I would really appreciate if someone could help me.

Best regards

TDO

Link to comment

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.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.