Query in mysql DB with Dieadem

[hugoc]

Hi,

I am using a mysql database to store some report data and I want to use Diadem to show the data and create the charts.

I can connect to the database using diadem and see the database structure. I also can select specific columns and create charts.

My question is if I can make a query for a specific value, the same way that it can be use datafinder to query a specific value in a TDM file.

I have read in the NI developer zone in http://zone.ni.com/devzone/cda/tut/p/id/4260 in the Getting data out tab there is a dialog that is a good example of what I want.

Do we have to build this tools? How ?

Thanks.

[Brad_Turpin]

Hi Hugoc,

You can interactively browse though the table and column structure of external data bases in the DIAdem NAVIGATOR, and you can even drag&drop one or more columns or tables into the Data Portal to load them into DIAdem.  In practice, though, this is seldom useful beyond quick, high-level sales demos.  In order to run a real SQL query that pulls back selected records based on query conditions, or in order to dynamically merge multiple tables with an INNER JOIN, etc., you need to create and run a DIAdem VBScript.  In older versions of DIAdem prior to 10.1 this was chiefly accomplished via the ODBC data base technology, using built-in DIAdem commands which all had names starting with "SQL_...()".

As of DIAdem 10.1 and later, however, it is preferable to use the ADO data base technology.  There are numerous examples that ship with DIAdem that show how to construct and execute and display the results of SQL queries using ADO objects in a VBScript.  However, these examples require considerable editing before they will connect to YOUR data base and run YOUR queries, so "some assembly is required".

Way back in DIAdem 8.1, I created an "SQL Wizard" data base example that you can use immediately to interactively build and run YOUR queries on YOUR data base.  This example originally used the built-in ODBC commands in DIAdem, whereas newer versions of the example now use the ADO approach.  The latest version of the example also uses the new XTable SUDialog control which released with DIAdem 10.2.  So I'm attaching the latest version that runs in DIAdem 10.2 or later, but if you're using an older version of DIAdem I could send you an earlier example version that matches.

The "SQL Wizard" example does not offer dynamic table linking or merging, but it does support switching between different data base views, so you can add this functionality on the data base end if you need it.  You will need to replace lines 11 - 13 in the "SQL Wizard 102.VBS" file with the corresponding line(s) to connect to YOUR data base(s).  Let me know if you need help creating the required ADO connection string for your external data base.

Run the "SQL Wizard 102.VBS" script to start the example,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

[hugoc]

Hi Brad,

I have seen the wizard and it looks really nice. In the srcipt I have tryied to change the lines wereit connects to the database but without success.

I believe that is a syntax error from my side. Perhaps you can indicate me the rigth syntax... I can't find out the error.

This is the example in the file:

 '       AddDB(DBs, "Data Base Name | UserName | Password | QuoteStr | DateStr | Access File Path or ADO Connection String ")

DBs = AddDB(DBs, " SQL_Weather   |                  |                |    []         |     #      | " & AutoActPath & "SQL_Weather.mdb")
  '    If you have a DSN, your ADO Connection String = "DSN=_____;UID=_____;PWD=_____;"

This is my DSN file:

[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
UID=root
PORT=3306
DATABASE=test
SERVER=140.30.135.20

How would the final string be defined in the script file ? Would it be something like this ?

DBs = AddDB(DBs, "    |   test       |                |    []         |     #      | " & AutoActPath & "DSN=140.30.135.20;UID=root;PWD=xxxtp;")

Thanks.

Best regards,

Hugo

[Brad_Turpin]

Hi hugoc,

Try the following reference web site ADO connection string suggestions:

http://www.connectionstrings.com/

then use the resulting "ADOConnectionString" you've found/constructed in the VBScript this way:

  DBs = AddDB(DBs, " hugoc   |          |          |    []    |    #    | " & "ADOConectionString")

Brad Turpin

DIAdem Product Support Engineer
National Instruments

[hugoc]

Hi Brad,

Thanks, the final string is

DBs = AddDB(DBs, " servername     |           |            |          |    #    | " & "DSN=dsnname;UID=user;PWD=password")

And I get a successful connection.

I have made some experiences with the wizard and it looks rally helpful.

But there some things that I need to change. I have looked the VB script and checked the sud files and I can't figure out where I need to make the changes.

For example, when I "SET" a column where the type is float I can make the "Current Query Row Count". But if I make the enumarate and select a specific value , in the operator I choose "=" I get an error message.

I have analyzed the error message and debug it in the mysql, basically this error is because we need to format the query:

There query that the wizard generates:

[MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]Operand should contain 1 column(s) : SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (noise=7,56E-10)

Queries that work:

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (format (noise,11) = format (7.56e-10,11) );

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (format (noise,11) = format (0.000000756,11) ); 

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE noise like 7.56e-10; -- the decimal point instead of the comma  and "like" operator instead of "=".

Can you tell were I can make the change to verify when column type is float, then format the "where" expression...

Another thing is when I choose the option "Show query results in a record dialog" the results are never exported... is this the expected result ?

Thanks,

Best regards,

Hugo

[Brad_Turpin]

Hi Hugo,

Try this DB connection line instead:

DBs = AddDB(DBs, " servername     |           |            |    []    |    #    | " & "DSN=dsnname;UID=user;PWD=password")

The "[]" paramter tells the wizard to surround all [table names] and [column names] with the [] charaters.  I believe the issue is that your column names have spaces in them.  Some data bases use " or ' characters to do this, so that is why I exposed it as a parameter in the above connection line.  If you leave that parameter empty, no characters are added around the table and column names in te SQL queries.

Brad Turpin

DIAdem Product Support Engineer
National Instruments

[hugoc]

Hi Brad,

I already have tried with [], but with like this I can't make the enumarate. I get an error instead of the table values.

Also the names that I used for the tables don't have spaces.

And for the rest of my questions, can you advise any solution ?

"

1) But there some things that I need to change. I have looked the VB script and checked the sud files and I can't figure out where I need to make the changes.

For example, when I "SET" a column where the type is float I can make the "Current Query Row Count". But if I make the enumarate and select a specific value , in the operator I choose "=" I get an error message.

I have analyzed the error message and debug it in the mysql, basically this error is because we need to format the query:

There query that the wizard generates:

[MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]Operand should contain 1 column(s) : SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (noise=7,56E-10)

Queries that work:

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (format (noise,11) = format (7.56e-10,11) );

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE (format (noise,11) = format (0.000000756,11) ); 

SELECT COUNT(rxpower.iddesc) FROM rxpower WHERE noise like 7.56e-10; -- the decimal point instead of the comma  and "like" operator instead of "=".

Can you tell were I can make the change to verify when column type is float, then format the "where" expression...

3) Another thing is when I choose the option "Show query results in a record dialog" the results are never exported... is this the expected result ?

"

[Brad_Turpin]

Hi hugoc,

I'm sorry, I don't know how I saw spaces yesterday-- there are none now in your column or table names.  The "Show Query Results in Dialog" button should pop up a dialog with a table of values in it.  So the fact that you're not getting anything to happen is not expected and is a problem of some sort.

You could always put back in the 3 data base connection lines so that you can try out the Wizard with the 3 data bases it contacts by default.  Then you should see that dialog pop up.  This would be a general way to tell what "should" happen.  I've not seen this SQL command FORMAT before.  Does this dynmically convert a string column to a float column for the purposes of the query?  If so, why are you storing float values in a string column in the data base?  If not, then why is the FORMAT command necessary-- if we're doing float comparisons, what need is there for a format specification?  Or is the FORMAT determining the display of the values that are returned by the query?

The WHERE clause in the query is cotained in the "CondStr" variable in the SUDialog file.  This happens in the Function "QuerySelData()" at the following line:

T1 = "SELECT " & Prefix & QColStr & " FROM " & QTables(k) & CondStr & SortStr

It also occurs in the Function "GetQueryProp()" in the following line:

If (CondBool) Then T1 = T1 & CondStr

Brad Turpin

DIAdem Product Support Engineer
National Instruments