Extracting one column from Database

[Pablop]

Hi Claire,

I don't think it's a multiple execution think, and I'm not trying to write to the table, just read (see jpg). It's not an Access Database, it's a SQL database, so that's why I was assuming the Microsoft Jet Database driver was the wrong verbiage.

I think that method I used is valid for an Access database that I have rights to, but not a SQL database that I don't have rights to. I notice in the error message quoted below, the phrase "Could not update; currently locked by user 'Admin' on machine 'XXX'". Seems like an user access rights issue.

I think theer's a way to pass a user name and login along with the connect string, I just don't know how to do that. As a method, that would be best. Because then the right to access the database would be contained in the connect string, and not be a function of the rights of the lowly user who happens to be logged into the test station. Test operators don't have rights to acccess server drives, and rightlly so. I'm just not savvy enough to come up with the correct command structures to accomplish this, but I'll crash my way through at some point.

When I search the forums, there is a lot of information, but it's almost too much, and it's hard to find a clear solution. I'll keep looking.

[Pablop]

I found a new string to use:

Provider=MSDASQL.1;Extended; Properties="DRIVER={SQL Server};SERVER=AT-CAL\SQL1A;READONLY=0;DATABASE=Calib";
Data Source=\\Us06nt04\SQL\MSSQL\Data\JHMiddleware.mdf

and I get a different error, see jpg

[Claire_Reid]

All I can tell from this error is that the data source name is too long? Are you still getting this error?

[Pablop]

Hi Claire,

I got it worked out. There was some syntax issues and permission isues. Funny, if you look on the web for SQL connect strings, there are way too many options out there. And the one that finally worked had syntax in it that I couldn't find on a website.

Thanks again.

[Claire_Reid]

Would you mind posting what actually worked in the end to help people in the future? Thanks!

[Pablop]

Hi Claire, something weird is going with either my Firefox installtion, the NI board, or something else. In any event I can't reply to the actual post you requested a reply, the forum keeps keeps throwing up error. Maybe a repsose to any earlier post will work:

A Google search for SQL connect string yields lots of choices. Also, it turns out that server name is sufficient (no back slashes or full paths). Also the Database Name is sufficient, no back slashes, no full path or actual file name with extension is required. Even though the actual database file exists several directories down from the server root, and the actual database filename is different the the database name.

Here is sanitized sample of a functioning connect string:

Driver={SQL Server};Server=ServerName;Database=DataBaseName;Uid=UserID;Pwd=Password

Very simple actually but this string would not work for me with my company login and password, even though my comapny login had rights to the database. But after my MIS department added a separate user with rights to the SQL database, the above string worked and created a valid connection. The next problem I had was actually reading out some data. The Database I was working on for trial had some unusual prefixes to the table names and this prefix text had to be enclosed in brackets, as in:

SELECT  ColumnName_1, ColumnName_2
FROM      [TableNamePrefix].TableName

This select string succsessfully retrieved  data from the table, but only after I included the table name prefix in brackets.

I have no idea how this rule is applied, because when I moved to the actual database and table I wanted to work with, I see prefixes, but I didn't have to use them. Weird.

Hope this helps, problably rudimentary to folks who deal with databases regularly. It's all about syntax and rights.

[rolfk]

---

Pablop wrote:

Very simple actually but this string would not work for me with my company login and password, even though my comapny login had rights to the database. But after my MIS department added a separate user with rights to the SQL database, the above string worked and created a valid connection. The next problem I had was actually reading out some data. The Database I was working on for trial had some unusual prefixes to the table names and this prefix text had to be enclosed in brackets, as in:

SELECT  ColumnName_1, ColumnName_2
FROM      [TableNamePrefix].TableName

This select string succsessfully retrieved  data from the table, but only after I included the table name prefix in brackets.

I have no idea how this rule is applied, because when I moved to the actual database and table I wanted to work with, I see prefixes, but I didn't have to use them. Weird.

Hope this helps, problably rudimentary to folks who deal with databases regularly. It's all about syntax and rights.

---

tablename is probably a reserved keyword for your database. So the SQL parser tried to parse the command applying whatever placeholder it gives to that keyword. By applying brackets around an expression you tell the SQL parser that it is a whole identifier (for instance also necessary when an identifier contains spaces) and then the SQL parser assumes that the part behind the dot needs to be an identifier too instead of a keyword. bracketing tablename should probably work too.

Or tablenameprefix is a keyword. Not really sure about that but in the end it is all logical if you know the possible details.

Rolf Kalbermatter 

[Claire_Reid]

Hey Pual,

Weird that you were not able to reply to the post. But, thanks for posting what finally worked for you.

Have a great weekend.

[kc64]

I'm using the ADO toolkit from Mike that is linked above.  I'm having connection troubles on a newly established system.  I have installed the 64-bit version of MySQL and the MyODBC driver.  I ran a simple query to test out the code and got the following error from the Open Connection+.vi.  Any suggestions?

* Open Connection+.vi

>> DSN=sxxxxxxxxb_test_data;

SERVER=localhost;

UID=xxxxxx;

PWD=xxxxxx;

DATABASE=sxxxxxxxxxb_test_data;

PORT=3306

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager]

The specified DSN contains an architecture mismatch between the Driver and Application -- LabVIEW:  (Hex 0x80004005) Unspecified error.

=========================

NI System Configuration:  (Hex 0x80004005)

Miscellaneous operation failure.

<EVENTCLASS: Database Exception>

* Open Connection+.vi>>DSN=sxxxxxxxxb_test_data;SERVER=localhost;UID=sxxxxxx;PWD=apc;DATABASE=sxxxxxxxxb_test_data;PORT=3306

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager]

The specified DSN contains an architecture mismatch between the Driver and Application -- LabVIEW:  (Hex 0x80004005) Unspecified error.

=========================

NI System Configuration:  (Hex 0x80004005) Miscellaneous operation failure.<EVENTCLASS: Database Exception>

[kc64]

I should have done a better job searching.  See this post.

http://digital.ni.com/public.nsf/allkb/E7984C0DA0F0E65086257694005B4CB7