database logging and reporting SOLVED

ericm · Post by **ericm** » Mon May 22, 2006 5:22 am

I am logginf to a odbc msaccess database. I want to strip the % off the end of the replies for Alive and Dead because when I try to run a query to give me the average of a certain test I get a error that says mismatched data. I have been doing this through a query in access but over time the log has grown and it takes to long to complete the query now. Is there a way to strip that % sign off the end of the reply before it gets posted to the database?

Yoorix · Post by **Yoorix** » Mon May 22, 2006 6:12 am

For Reply field you may use Reply_Number or Reply_Integer variables, but for AliveRatio and DeadRatio you should try MSAccess string function to strip ' %'. I know nothing about MSAccess functions, but in MySQL it looks like Replace('%AliveRatio%',' %','')
http://www.ks-soft.net/hostmon.eng/mfra ... .htm#macro

E.g. INSERT INTO hmlog (DATE1, TIME1, TEST_NAME, TestID, STATUS, REPLY,ALIVERATIO) VALUES (%Date%, '%Time%', '%TestName%', %TestID%, '%Status%', '%Reply%',Replace('%AliveRatio%',' %',''))

It works.

Regards,
Yoorix

ericm · Post by **ericm** » Mon May 22, 2006 6:37 am

Thank You
I will try today.

ericm · Post by **ericm** » Mon May 22, 2006 7:47 am

maybe it is my setup but the replace function does not work with a odbc connection.

Yoorix · Post by **Yoorix** » Mon May 22, 2006 9:05 am

ericm wrote:maybe it is my setup but the replace function does not work with a odbc connection.

Did you try to execute such clause in Access worksheet directly? What SQL Error do you see? Could it be syntax error?
Maybe, you should perform additional data convertion, like Cast(Replace('%AliveRatio%',' %','') as double) or something like that. You should read Access manual about conversion functions.

Regards,
Yoorix

ericm · Post by **ericm** » Mon May 22, 2006 9:29 am

I tried it in the odbc string in advanced log settings
Undefined function 'Replace' in expression

Yoorix · Post by **Yoorix** » Mon May 22, 2006 11:09 am

Instead of replace you should use combination of string functions, like
left('%AliveRatio%',instr('%AliveRatio%',' %')-1)

I have checked it with Access 2002. It works. But, I think, it is not the best solution

Regards,
Yoorix

ericm · Post by **ericm** » Mon May 22, 2006 11:15 am

Thank You
Maybe not the best but the working solution
It works for what I need it for.

KS-Soft · Post by **KS-Soft** » Mon May 22, 2006 11:30 am

Probably we can add new option, something like "ODBCLog_NoPercentSign"

Regards
Alex

KS-Soft · Post by **KS-Soft** » Sat May 27, 2006 7:27 pm

Done. Version 6.00 available at http://www.ks-soft.net/hostmon.eng/downpage.htm

Use ODBCLog_NoPercentSign option
http://www.ks-soft.net/hostmon.eng/news.htm

Regards
Alex