Timestamp import from Database wrong?

[SethRow187]

Brad-

I'm perplexed as well.  I loaded the file that you just posted, and it worked fine (I was able to load data into the portal from all 3 of your example databases).  After this tested OK, the only change I made was to add our MySQL database to the end of the list.

I can still bring in data from your 3 databases, and I can view the tree structure of our MySQL database and select tables / columns.  I can click the "current query row count" button and that works fine.  I can click the set button in the query boxes at the bottom and the dropdowns get populated correctly.  The hangup comes when I try to bring the data into the portal (the error I described before).  I also discovered a new error -  if I click the "show query results in a record dialog" button I get the following error:

The following error occurred when executing the dialog box SQLBrowser from the SQL Wizard.SUD file:

<(Declarations), Line 386>

[MySQL][ODBC 3.51 Driver][mysqld-5.0.30-debian_3-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘100 abs_time FROM staging_tbl2’ at line 1

Any new thoughts?

[Brad_Turpin]

Hi Seth,

Note that after running the SQL Wizard the latest SQL query is stored in the T1 variable, so that you can review it at leisure later.  Based on your error message, it looks like the query syntax is wrong for your MySQL data base.  The part of the SQL query that the error message calls out specifically is:

100 abs_time FROM staging_tbl2

Now, that "100 abs..." part is troubling.  Spaces are taboo in SQL names-- where is that "100" coming from?  Is this the tail end of a LIMIT or a TOP expression, or do you really have a column named "100 abs_time"?  If you do, then we need to add a string character to your line 12 so that the query reads "100 abs_time" FROM staging_tbl2 or '100 abs_time' FROM staging_tbl2.  Note that the first example data base configuration in line 9 already has the " character listed as the string character-- you can use that as an example of what to try in your line 12.

I suppose the other possibility is that MySQL doesn't suppot the TOP or LIMIT expression in an SQL query.  In that case we'd have to substitute the equivalent expression in MySQL syntax or live with querying way more records than we can display, but in all cases it would mean dorking with the code in the SUDialog.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[SethRow187]

Brad-

The column is named "abs_time", so I'm not sure where the 100 is coming from.  The error happens regardless of if I'm trying to bring in the entire column or a specific subset of the column (by setting up a more advanced qurey in the dialog boxes at the bottom).

Based on your advice, I tried both "" and '' as the QuoteStr.  When I ran both cases, I did not get a pop-up error window.  However, this message appeared in the log window at the very bottom of the screen -

[SQL Error] : SELECT COUNT(''abs_time'') FROM ''staging_tbl2'' -

and no data actually got imported into the portal.  If I select a column and click the "Current Query Row Count" button I get a reply of "= [ERROR]", and if I try and click the Set button on any of the advanced query lines at the bottom the column names dropdowns get populated correctly, but the condition value, column min value, and column max value all display "[ERROR]".  Also, if I select a column and press the "show query results in a record dialog" button, I get the same error in <(Declarations), Line 386> as I did before.

I have no problem dorking with the code, but I'm new to VBS script so any guidance you could provide would be greatly appreciated :).  Thanks!

[Brad_Turpin]

Hi Seth,

In that case, it looks like the "Top 100" prefix is not supported in your data base.  It only occurs once in the SQL Wizard.SUD file (in the "RunRecDialog" control callback under the "EventClick" event).  If you open this file up in the SUDialog Editor (launch from SCRIPT) and replace "Top 100" with "", that should help.  You may still need to try different string characters, but based on your comments so far, I'd start with no string characters first.

Let me know if that's not it, I still have some tricks to try,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

[SethRow187]

I changed the EventClick code to read (replaced "Top 100 " with ""):

Sub RunRecDialog_EventClick()
Dim This : Set This = RunRecDialog
   Dim ErrMsg
  ErrMsg = QuerySelData(GroupByTbl.Value, False, "")
  If ErrMsg = "" Then Call SudDlgShow("RecordsDlg", AutoActPath & "SQL Wizard", RecordSet)
End Sub

However, when I run it I still get the same type mismatch error in <(Declarations), Line 403>

What other tricks you got up your sleeve?

[Brad_Turpin]

Hi Seth,

I believe I succeeded in implementing code that will use the Table.Column syntax for all queries that the SQL Wizard executes, with the exception that the condition clauses still use the simple column name for each comparison.  That too could be changed, but it would be harder to change due to the way I originally coded the contruction of the string which stores the multiple conditions, which at the moment happens independent of which Table(s) were selected.  I also suspect that the reason for that Type Mismatch error is due to the MaxLen variable receiving an empty string from the COUNT query.  So I disengaged the buffering status dialog display for that case, which appears to be the only thing using that MaxLen variable.

Try this version, first with no conditions, then adding a single condition, and let me know if it works.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[SethRow187]

Brad-

Still running into some errors.  When I select a column and try to import it into the portal I get the following error:

The following error occurred

When executing the dialog box SQLBrowser

From the SQL Wizard.SUD file:

<(Declarations), Line 404>

Type mismatch

When I select a column and press the "show query results in a record dialog" button, I get the following error:

The following error occurred

When executing the dialog box SQLBrowser

From the SQL Wizard.SUD file:

<(Declarations), Line 387>

[MySQL][ODBC 3.51 Driver}Pmysqld-5.0.30-Debian_3-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘100 staging_tbl2.abs_time FROM staging_tbl2’ at line 1

This was run without any conditions.  I noticed that it is still getting the "100" from somewhere.  Anything else you'ld like me to try?  Thanks for the help.

[Brad_Turpin]

Hi Seth,

Thanks for you patience, I actually think we're close.  First of all, I didn't remove the "TOP 100" from the 102 version I sent you, so you'll need to make that edit again.  Secondly, line 404 again involves the MaxLen variable:

"If MaxLen > 0 Then"

So apparently, the MaxLen variable can not even be coerced to a numeric subtype.  Try adding the following line to further protect the SqlStatusDlg() code:

IF IsNumeric(MaxLen) THEN
  IF MaxLen > 0 THEN MsgBox "OK"
        :                    :                   :
  END IF ' MaxLen > 0
END IF ' IsNumeric(MaxLen)

Let me know-- you may want to just email me directly at brad.turpin@ni.com,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

[SethRow187]

That did the trick!  It's working like a champ now. 

Thanks so much for the help.  DIAdem is once again my friend.

[Michela_74]

Hi Brad,

I am try to adampt your SQL Wizard (101 version) to my sql database (created with Microsoft SQL 2005), but I have several problems. For istance:

1- I am not able to load data (SQL tables) in the Diadem data portal, or better I load them but the tables seems empty (no data just properties);

2- When I try to launch a query I get SQL  Error... The sentence I create using the wizard interface has too many empty spaces... (I pasted the query directly into SQL and it doesn't work either, without cleaning it up from empty spaces)...

Maybe the program was created to manage data from different types of databases (Access, Oracle, ...). Have you a specific wizard just for SQL databases?

Could you give me some suggestions on how to personalize it? Or, by the way, have you some documents to uderstand better wizard code?

Thank you in advance,

Michela