| View previous topic :: View next topic |
| Author |
Message |
kevinkenny
Joined: 12 Jan 2010
Posts: 16
|
 Posted: Tue Jan 19, 2010 10:24 am Post subject: Parse result of a SQL ODBC query to HMS Script via macro |
 |
|
It would be nice to be able to get the result of an ODBC SQL Query into a %MACRO% variable.
Ideal:
Parse the SQL query result into a delimited string, for example:
| Code: | | select field1, field2, field3 from hmlog_createtestqueue |
HM would parse the results and push into a %MACRO% value:
| Code: | | "fieldvalue1a","fieldvalue1b","fieldvalue1c"|"fieldvalue2a","fieldvalue2b","fieldvalue2c" |
where commas separate the columns and the | character separate is the row separator.
But I could live with just returning the first row in the %MACRO% value, or even a single value, e.g.
| Code: | | select top 1 new_test_id from hmlog_newtestqueue where created = 0 |
What I'm trying to achieve is passing a value or set of values from the database which is acting like a queue for creating new tests. I'd like to pass the result to a HMS Script which then executes a simple console application to create a test import file.
e.g.
Good Action:
| Code: | ExecuteProgram 10000 c:\database\generator.exe %ODBC_Result% c:\HostMon\import1.txt
NewTestList
ImportFromFile c:\HostMon\import1.txt
SaveTestList c:\HostMon\temp1.hml |
Cheers and thanks for looking
Kev |
|
| Back to top |
|
 |
KS-Soft
Joined: 03 Apr 2002
Posts: 12795
Location: USA
|
| Posted: Tue Jan 19, 2010 10:49 am Post subject: |
|
|
ODBC test method may return value of single field.
Regarding your needs... I cannot say I like this idea 
I think its much better to modify your utility (generator.exe?) so it will be able to retrieve data from your database and generate import file for HostMonitor (without using ODBC test). ODBC test is just a test, it should not perform operations that are really unnecessary for a test.
Regards
Alex |
|
| Back to top |
|
|
kevinkenny
Joined: 12 Jan 2010
Posts: 16
|
| Posted: Tue Jan 19, 2010 11:08 am Post subject: |
|
|
| KS-Soft wrote: | ODBC test method may return value of single field.
Regarding your needs... I cannot say I like this idea
I think its much better to modify your utility (generator.exe?) so it will be able to retrieve data from your database and generate import file for HostMonitor (without using ODBC test). ODBC test is just a test, it should not perform operations that are really unnecessary for a test.
Regards
Alex |
Hi Alex,
Thank you again for the prompt response.
Is the single field value returned parsed into a %MACRO% variable? If so which one?
Thanks
Kev |
|
| Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002
Posts: 12795
Location: USA
|
| Posted: Tue Jan 19, 2010 11:20 am Post subject: |
|
|
Value of the database field that you need to check is displayed in Reply field of the test so you may use %Reply% macro variable.
You may also use %Reply_CStyle%, %Reply_Number% or %Reply_Integer% when necessary.
PS I think you may create select request that will join several database fields into one value...
Regards
Alex |
|
| Back to top |
|
|
kevinkenny
Joined: 12 Jan 2010
Posts: 16
|
| Posted: Wed Jan 20, 2010 7:50 am Post subject: |
|
|
| KS-Soft wrote: | Value of the database field that you need to check is displayed in Reply field of the test so you may use %Reply% macro variable.
You may also use %Reply_CStyle%, %Reply_Number% or %Reply_Integer% when necessary.
PS I think you may create select request that will join several database fields into one value...
Regards
Alex |
Alex,
What I'm trying to do is integrate with our hosting provisioning system and I need a way to delete tests once a customer closes a hosting service on their account.
I have an ODBC SQL test that does -
| Code: | | select top 1 new_test_id from hmlog_modifytestsqueue where completed = 0 AND action='delete' |
This will return a single row with a single column, however the value that is in the database (there's only one record at the moment) isn't being parsed into the %Reply% macro. I get a single digit integer value which looks like the time taken the execute the query in milliseconds(?)
(nb: please don't confuse the new_test_id field name with the HM Test ID, they're not related, it's actually a value generated by our system that forms part of the test name/title)
In my HMS Script, which is called when the test is good, I'm doing the following:
| Code: |
DeleteTest %Reply%-Ping
DeleteTest %Reply%-URL
ExecuteProgram 10000 c:\HM\SetDeleteTestCompleted.exe %Reply%
|
The reason for this is that the test import text file seems to ignore the DeleteTest command.
The other way I could do this is to call a simple webservice using a URL test and return the new_test_id I'm trying to delete in the %HttpPage% macro, but this is a bit more work.
Thanks again for your time.
Kevin |
|
| Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002
Posts: 12795
Location: USA
|
| Posted: Wed Jan 20, 2010 6:20 pm Post subject: |
|
|
| Quote: | This will return a single row with a single column, however the value that is in the database (there's only one record at the moment) isn't being parsed into the %Reply% macro. I get a single digit integer value which looks like the time taken the execute the query in milliseconds(?)
|
I assume you have not setup test to check for specific field. Please use "Alert when field in N row M col is ..." option
| Quote: | | The reason for this is that the test import text file seems to ignore the DeleteTest command. |
There is no such command for import file.
Regards
Alex |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in
