Parse result of a SQL ODBC query to HMS Script via macro

[kevinkenny]

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 all

select field1, field2, field3 from hmlog_createtestqueue

HM would parse the results and push into a %MACRO% value:

Code: Select all

"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 all

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: Select all

ImportTest.hms

Code: Select all

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

[KS-Soft]

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

[kevinkenny]

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

[KS-Soft]

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

[kevinkenny]

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 all

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: Select all

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

[KS-Soft]

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

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