Search the Community

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!

Hi, I'm trying to insert some NULL values in a datetime field. In the example, the DATA_INSERIMENTO field has a non empty value and it works correctly but DATA_INTERVENTO doesn't accept NULL. If I use an empty string instead of null, the VI run without any errors but it fill the database field with a 1900-01-01 and it's not what I want. If I use the DB Tools NULL VI it gives me another type of error, maybe 'cause I'm connecting a variant to a cluster of string. If i use a Variant to Data VI for the NULL value it returns an empty string so not the result I need. If use the string you see in the label at the bottom of my diagram in SQL Server manager, it works correctly. How can I obtain the same result with labview?

Hi All, Long-time lurker, first-time poster here. As the title (hopefully) implies, I have an issue connecting to a database I've created. The problems started this week after coming back to look at the project after ~3 months. I know I had to repair my Office 365 installation last week and I'm wondering if that could be the cause but other than that I'm at a complete loose end. Microsoft support say it's a LabVIEW issue, NI support say it's an MSDN issue. The official NI forum, after looking at similar posts, was even less helpful. So I thought I'd come and see if the nice guys at LAVA had experienced a similar issue. I'm aware that the error code is a generic one and has multiple causes but I am simply writing a "SELECT [column] FROM [table];" statement to an 'Execute Query' vi. The error returned from my sanity checking vi is: (Code -2147217900) NI_Database_API.lvlib:Conn Execute.vi->Untitled 1<ERR>ADO Error: 0x80040E14 Exception occured in Microsoft Access Database Engine: in query expression '[UserID]'. in NI_Database_API.lvlib:Conn Execute.vi->Untitled 1 The connection string returned is (without line breaks): Provider=Microsoft.ACE.OLEDB.12.0; User ID=Admin; Data Source=T:\PROJECTS\02-ACTIVE\119888 RF Power upgrade\Database\RFPU.accdb; Mode=Share Deny None; Jet OLEDB:System database=""; Jet OLEDB:Registry Path=""; Jet OLEDB:Database Password=""; Jet OLEDB:Engine Type=6; Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Global Bulk Transactions=1; Jet OLEDB:New Database Password=""; Jet OLEDB:Create System Database=False; Jet OLEDB:Encrypt Database=False; Jet OLEDB:Don't Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False; Jet OLEDB:SFP=False; Jet OLEDB:Support Complex Data=False; Jet OLEDB:Bypass UserInfo Validation=False; The BD for my sanity check looks like this: I'm using LabVIEW 2016 32-bit on a W7 Dell Ultrabook with Access2013 as part of our Office 365 installation. BTW the query executes fine in Access2013. Is that enough info? Regards, Boris

Hi all, I need to query data from MS SQL server at different PC using LAN cable. Do you guys have any example to do this? Please help.

My question relates to retrieving decimated data from the database. Given the case where I have 1M X and 1M Y steps (a total of 1000000M data points per channel) how do I efficiently get a decimated overview of the data? I can produce the correct output data be using Select X,Y,Z,Float1 from P WHERE X=0 GROUP BY Y/1000 This will output only 1x1000 data instead of 1x1000000 datapoints, one dimension quickly decimated Problem is that is iterates over all data (and this takes quite some time). If I do a different query to retrieve only 1000 normal points, it executes in under 100ms. I would like to use a CTE to do this, thinking that I could address directly the 1000 elements I am looking for. WITH RECURSIVE cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1000 FROM cnt LIMIT 1000 )WHAT GOES HERE?; So if I can create a cte with column x containing my Y Indices then how do I get from this to directly accessing Float1 FROM P WHERE X=0 AND Y=cte.x SELECT Float1 from P WHERE X IN (SELECT x FROM cnt) AND Y=0 AND Z=0 Using the "IN" statement is apparently quite inefficient (and seems to return wrong values). Any ideas? In addition, accessing even a small number of data points from within an 8GB SQL File (indexed!) is taking upwards ot 30 seconds to execute at the moment. With 1.5GB files it seemed to be in the region of a few milliseconds. Does SQLite have a problem with files above a certain size?

Hi, I have a big project to manage which includes :-TestStand + LabVIEW-SQL Database for test inputs (UUT characteristics, sequences parameters,...) -HTML Reports -almost 10000 different part numbers (that's THE point) with common characteristics (almost 3000 families) + specific characteristics Everything has to be imaginated. The SQL inputs tables have to be created. The number of sequences to code has to be reduced to the minimum because, of course, it's impossible to code 8000 sequences differents files... The final solution must be as simple as possible. Possible ways of reflexion : -XML and Xpath for families and their children -Intermediate LabVIEW OOP layer for families and their children -Dynamically created sequences + variables (TS scripting)-Dynamically created sequences (concatenating a bunch of generic sequences to build a specific sequence) There is a lot of documentation about TestStand and SQL, but only for results (outputs), not really about inputs. Does anybody manage this kind of project ? In which way ? What would be your advices ? Thanks, Sébastien CLA

Name: libpq Submitter: SDietrich Submitted: 01 Mar 2014 Category: Database & File IO LabVIEW Version: 2009License Type: Other (included with download) This is a package containing LabVIEW bindings to the client library of the PostgreSQL database server (libpq). The DLL version 9.3.2 and its dependencies are included in the package. This DLLs are taken out of a binary distribution from the Postgres-Website and are thread-safe (e.g. the call to PQisthreadsafe() returns 1). As of the moment the DLLs are 32bit only. The VIs are saved in LabVIEW 2009. So this package works out of the box if you have a 32bit LabVIEW 2009 or higher on any supported Windows operating system. Because this obviously is a derived work from PostgreSQL it is licensed by the PostgreSQL license. A few words regarding the documentation: This package is meant for developers who know how to use the libpq. You have to read and understand the excellent documentation for the library. Nonetheless all VIs contain extracts of that documentation as their help text. What's coming next? - adding support for 64bit - adding support for Linux (anybody out there to volunteer for testing?) - adding support for MAC (anybody out there to volunteer for testing?) Click here to download this file

I have a VI that reads an image directory and the user can cycle through the images with a next and previous button. The image directory is stored in the SQL database just wondering how would I cycle through the images and display the information on the images that is stored in the database sequentially as the user cycles through the images? Cycle Through Images.vi

LabVIEW 2013 SQL Server 2012 I would like to read an image path from a SQL database and display the image on the front panel in LabVIEW. The database side works ok but when I run the VI i get an error saying Error 7 file not found. I have checked the directory and the path is correct. I have attached a screenshot of the VI and error.

Hi all, I've developed program that calls subvi that queries data from MySQL data base. It works fine till I publish it as a web service. While running program, I receive errorCode and errorDesc from subvi as follows: errorCode=-2147467259 errorDesc=NI_Database_API.lvlib:DB Tools Open Connec (String).vi->checkLog.vi->vigateway.vi->vigateway.vi.ProxyCaller<ERR>ADO Error: 0x80004005 Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->checkLog.vi->vigateway.vi->vigateway.vi.ProxyCaller It happens on LV 2012 and 2013. I've tried also with wi-fi switched off and it's the same. ODBC is supposed to be properly configured (it works for undeployed program). Everything is fine with Web Server configuration. VI works when it is run as application and web service but the problematic is VI I attached. I'd be grateful for your help. Best regards, Piotr checkLog.vi

Hello, I'm having a fair bit of difficulty tracking down these errors that I'm getting. I'm not sure exactly what to provide to get the best help here, so I'll post the errors and start prepping some code snippets if they are needed. I recently migrated from an Access database to an SQL database, my code has a number of read/writes on the same connection (i.e. I open the connection at the beginning of the software with multiple parallel loops, and utilize that connection, sometimes in parallel loops, without closing it until the end). I'm running into errors with a few of the simple recordset and properties functions as follows: -1/18/2012 4:13:10 PM------------------1,-2147217906,NI_Database_API.lvlib:Rec Move To Next Record ®.vi:2->Tag_Data to Cluster.vi:1->Device Table DB - Return Router EUIs and Positions.vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi<ERR>ADO Error: 0x80040E0EException occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft] Invalid bookmark value in NI_Database_API.lvlib:Rec Move To Next Record ®.vi:2->Tag_Data to Cluster.vi:1->Device Table DB - Return Router EUIs and Positions.vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi-1/18/2012 4:15:43 PM------------------1,-2147352567,NI_Database_API.lvlib:Rec Get Recordset Properties ®.vi:2->NI_Database_API.lvlib:DB Tools List Columns.vi:2->Device Table DB - Return Device Data for EUI.vi->LE & Device Table DB - Log Location Calculation (SQL).vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi<ERR>ADO Error: 0x80020009Property Node (arg 2) in NI_Database_API.lvlib:Rec Get Recordset Properties ®.vi:2->NI_Database_API.lvlib:DB Tools List Columns.vi:2->Device Table DB - Return Device Data for EUI.vi->LE & Device Table DB - Log Location Calculation (SQL).vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi These errors occur at random intervals, though it seems that the first one ([color=#000000]-2147217906) happens much more frequently. I've been swatting these two bugs (i.e. suppressing and logging) and ultimately was faced with a third error:[/color] [color=#000000]1,1,"NI_Database_API.lvlib:Rec Delete.vi"<ERR>Object 0x000000 is not valid.[/color] [color=#000000]--that ultimately killed the program.[/color] I think it might be something with synchronous selects/writes but it's very hard to tell given the data I have... [color=#000000]Can anyone help me with a next step?[/color] [color=#000000]Thanks![/color] [color=#000000]Erik[/color]

There seem to be very few examples of how to get results out of the standard Teststand SQL database. There is a top level table called UUT_RESULT but it may not be obvious to a new user how to get the results from a test run in the UUT_RESULT table with a given ID GUID. To this end I give an example below in the hope it saves someone else the pain of surfing the internet trying to find an example. The SQL script below when run will return all the results for a test. Obviously you will still need to read up on SQL but it is a good starting point. There is a comment in the code on how to make sure only tests with results are returned. The SQL script is a lot more readable when coloured in a SQL editor. DECLARE @UGID [nvarchar](50) SET @UGID = 'C26760D9-3FE4-4500-99FA-6B4B26341563' SELECT RTRIM (UUT_RESULT.UUT_SERIAL_NUMBER) AS UUT_SERIAL_NUMBER,RTRIM (UUT_RESULT.START_DATE_TIME) AS START_DATE_TIME, RTRIM (UUT_RESULT.USER_LOGIN_NAME) AS USER_LOGIN_NAME, RTRIM(UUT_RESULT.STATION_ID) AS STATION_ID, RTRIM (UUT_RESULT.UUT_STATUS) AS UUT_STATUS,RTRIM (STEP_RESULT.ORDER_NUMBER) AS ORDER_NUMBER,RTRIM(STEP_RESULT.STEP_GROUP) AS STEP_GROUP, RTRIM(STEP_SEQCALL.SEQUENCE_NAME) AS SEQUENCE_NAME, RTRIM(STEP_RESULT.STEP_NAME) AS STEP_NAME,RTRIM(PROP_NUMERICLIMIT.COMP_OPERATOR) AS COMP_OPERATOR, RTRIM(STEP_RESULT.STEP_TYPE) AS STEP_TYPE, RTRIM(STEP_RESULT.STATUS) AS STATUS,RTRIM(PROP_RESULT.DATA) AS RESULT, RTRIM(PROP_NUMERICLIMIT.LOW_LIMIT) AS LOW_LIMIT,RTRIM(PROP_NUMERICLIMIT.HIGH_LIMIT) AS HIGH_LIMIT, RTRIM(STEP_RESULT.MODULE_TIME) AS MODULE_TIME,RTRIM(STEP_SEQCALL.SEQUENCE_FILE_PATH) AS SEQUENCE_FILE_PATH,RTRIM(PROP_NUMERICLIMIT.UNITS) AS UNITS, RTRIM(UUT_RESULT.EXECUTION_TIME) AS EXECUTION_TIME, RTRIM(STEP_RESULT.ERROR_CODE) AS ERROR_CODE,RTRIM(STEP_RESULT.ERROR_MESSAGE) AS ERROR_MESSAGE,RTRIM(UUT_RESULT.UUT_ERROR_CODE) AS UUT_ERROR_CODE,RTRIM(UUT_RESULT.UUT_ERROR_MESSAGE) AS UUT_ERROR_MESSAGE FROM UUT_RESULT INNER JOIN STEP_RESULT INNER JOIN STEP_SEQCALL ON STEP_RESULT.STEP_PARENT = STEP_SEQCALL.STEP_RESULT ON UUT_RESULT.ID = STEP_RESULT.UUT_RESULT LEFT OUTER /*INNER */JOIN -- SWAP LEFT OUTER FOR INNER TO miss out steps without results. PROP_NUMERICLIMIT INNER JOIN PROP_RESULT ON PROP_NUMERICLIMIT.PROP_RESULT = PROP_RESULT.ID ON STEP_RESULT.ID = PROP_RESULT.STEP_RESULTWHERE (STEP_RESULT.UUT_RESULT = @UGID)ORDER BY STEP_RESULT.ORDER_NUMBER