Couple questions

korrowan · Post by **korrowan** » Fri Feb 04, 2011 5:42 pm

Hello I am trying to automate a process that takes data from an Access Table and inputs into a application on a VPN and then search for the value I need based on two values it can read from my table. I am a DBA by trade but have little experience in programming anything that is not SQL or VBscript and need some help.

I am guessing the best way to about this is to connect to the source, pass the value but I am not sure how to pass that value to the window? I am guessing it would be MouseMove x,y, LClick and then ????

Also the value will not be on the first screen and to scroll though you use F8...how do I go about that... and after the value is passed how do I tell it to stop?

I then need to pass the value ( a date) back to my database... it will know which is the correct value by checking it against 2 other dates (as there can be the same value on multiple date ranges).

Any help would be much appreciated!

Thank you,

Korrowan

korrowan · Post by **korrowan** » Fri Feb 04, 2011 6:36 pm

Another question.... how can I pass a user input parameter to a query? The normal vb strUserInputer = InputBox("","") does not seem to work. It steps through but never actually prompts...

Thanks

adroege · Post by **adroege** » Fri Feb 04, 2011 9:23 pm

I am guessing it would be MouseMove x,y, LClick and then ????

Let>myvalue=something
Send>myvalue

to scroll though you use F8...how do I go about that

Press F8

I then need to pass the value ( a date) back to my database

Either
//use getcontroltext to interface directly to the text in the exe
GetControlText>

or

//do it the hard way using cut/paste
MouseMoveRel>xy
LClick
//select all
Press Ctrl
Send>a
Release Ctrl
Wait>0.5
//Copy to clipboard
Press Ctrl
Send>c
Release Ctrl
//put the clipboard into a variable
GetClipBoard>somevalue

adroege · Post by **adroege** » Fri Feb 04, 2011 9:31 pm

how can I pass a user input parameter to a query?

Example

Code: Select all


//Connect to Datasource
Let>str=Driver={MySQL ODBC 3.51 Driver};Server=someserver.com;Port=3306;Database=example;User=admin;Password=xxxx;Option=3;
DBConnect>str,dbH
 
//Perform SELECT query
Input>myval,What is the value to search?

Let>SQL=select * from customers where custID='%myval%'
DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields
 
//loop through returned recordset
Let>r=0
Repeat>r
Let>r=r+1
Let>f=0
Repeat>f
   Let>f=f+1
   Let>this_field=CUSTOMERS_%r%_%f%
   Message>this_field

   Wait>0.5
Until>f=numfields
Until>r=numrecs
 
//Close database connection
DBClose>dbH

korrowan · Post by **korrowan** » Wed Feb 09, 2011 2:28 pm

Thanks guys that information will help a ton..it is much appreciated!

korrowan · Post by **korrowan** » Thu Feb 10, 2011 3:47 pm

Quote:
I then need to pass the value ( a date) back to my database

Either
//use getcontroltext to interface directly to the text in the exe
GetControlText>

Is it possible to use getcontroltext if it is accessing a program that is in a VM? We use a VM to access a VPN that locks out our internet so we can use one workstation that can access both our network and the VPN at the same time and the information I need is in that VM. From my understanding getcontroltext access a program and takes the text...can it do that when accessing an exe in a vm??? Here is a link to a screenshot that shows the data I am trying to get.

http://tinypic.com/r/bi2rkj/7

Thanks!

Post by **Marcus Tettmar** » Thu Feb 10, 2011 3:52 pm

If Macro Scheduler is on the host and the app is in the VM then no. The VM is an entirely separate machine sandboxed from the host. No process in one will have any kind of access to the other. Only thing that Macro Scheduler can do is send keystrokes/mouse events in and see the screen being projected out. Same as if you were using RDP or similar to access an entirely separate physical machine.

korrowan · Post by **korrowan** » Thu Feb 10, 2011 3:58 pm

mtettmar wrote:If Macro Scheduler is on the host and the app is in the VM then no. The VM is an entirely separate machine sandboxed from the host. No process in one will have any kind of access to the other. Only thing that Macro Scheduler can do is send keystrokes/mouse events in and see the screen being projected out. Same as if you were using RDP or similar to access an entirely separate physical machine.

So the best way to do this would be by having the screen in a fixed spot and pull the data using x,y?

Post by **Marcus Tettmar** » Thu Feb 10, 2011 4:06 pm

The best way to automate something where you have no access to the underlying API is Image Recognition. A fixed spot should not be necessary.

But, I'm not sure how you are expecting to "pull the data" if by that you are expecting to be able to extract text from a screen in the VM. If the text can be selected and copied to the clipboard then all well and good assuming you can share clipboards. But otherwise, apart from OCR, you aren't going to be able to screen scrape across the boundary between VM and host.

A better solution would be to have a copy of Macro Scheduler running INSIDE the VM for the manipulation of the apps in the VM. If you need to do something outside too then have a macro also running outside and make them communicate via a file in a shared folder or something.

korrowan · Post by **korrowan** » Thu Feb 10, 2011 4:13 pm

The data can be copied and shared between the VM and the PC it is housed on. What I want to do is be able to take data from a dataset and loop through and put in the necessary data into QWS3270 and have it search for the correct record and return the postmark date to me. If it does not find what I need on the screen f8 goes to the next screen and so on.

We get a dataset that can contain up to 400 records that have to be searched for certain things on the City mainframe and that is what I am trying to automate as it can take up a ton of time by hand.

There are several other screens where this will come in handy that are much simpler which all I need to do is enter a number and return one or more values but its all on the same screen.

adroege · Post by **adroege** » Thu Feb 10, 2011 6:22 pm

The devil is in the details....

1.) Is the VM hosted on a machine you control?
2.) How do you interact with that VM?
2a) Do you use RDP (remote desktop Protocol) to open a window to the VM?
2b) OR are you accessing it directly from the VM software?
2c) i.e. Are you using something like VMWare Player to run
the VM on your PC?

3.) If you control the VM, can you install software inside the VM?
4.) What is the VPN for? Is the exe inside this VM connecting to an outside database through the VPN?
5.) Do you have a login/password to connect to this database?

6.)What is QWS3270? Sounds like a terminal emulator for a 3270 mainframe terminal to me. So is the data you need accessed through the terminal emulator?

Knowing the answers to these questions may help me formulate an efficient way for you to proceed.

korrowan · Post by **korrowan** » Thu Feb 10, 2011 6:30 pm

1.) Is the VM hosted on a machine you control?

It is currently hosted on my laptop.

2.) How do you interact with that VM?

I am running Windows 7 XP Mode which I can access directly.

3.) If you control the VM, can you install software inside the VM?

I can if needed if it would make things less complicated.

4.) What is the VPN for? Is the exe inside this VM connecting to an outside database through the VPN?

Yes it is connecting to an outside Database or emulating a terminal as you state below.

5.) Do you have a login/password to connect to this database?

Yes there is a log in for the VPN and a login for QWS3270 not to mention a bunch of keystrokes to get to the correct screens.

6.)What is QWS3270? Sounds like a terminal emulator for a 3270 mainframe terminal to me. So is the data you need accessed through the terminal emulator?

That is correct.

I appreciate your help!

adroege · Post by **adroege** » Thu Feb 10, 2011 6:40 pm

Looking at the web page for QWS3270

http://www.jollygiant.com/products/qws3 ... atures.php

I see the following listed as a windows "feature":
Dynamic Data Exchange Support to allow other Windows applications to send data to the 3270 session and receive data from the 3270 session.

If you have a manual or help file to read up on this feature, sounds like Macro Scheduler could be installed in this VM and made to work with the DDE feature of QWS. If this works, it would be much more reliable that any image recognition /cut/paste approach.

Macro Scheduler has the following commands to interact with a DDE server:
DDEPoke>
DDERequest>

korrowan · Post by **korrowan** » Thu Feb 10, 2011 6:45 pm

adroege wrote:Looking at the web page for QWS3270

http://www.jollygiant.com/products/qws3 ... atures.php

I see the following listed as a windows "feature":
Dynamic Data Exchange Support to allow other Windows applications to send data to the 3270 session and receive data from the 3270 session.

If you have a manual or help file to read up on this feature, sounds like Macro Scheduler could be installed in this VM and made to work with the DDE feature of QWS. If this works, it would be much more reliable that any image recognition /cut/paste approach.

Macro Scheduler has the following commands to interact with a DDE server:
DDEPoke>
DDERequest>

I wish I had a manual but the City just hands it out and tells you what to do with it =). I will install Macro Scheduler on the VM and see what I can do.

adroege · Post by **adroege** » Thu Feb 10, 2011 6:46 pm

On-line help then?