Can Macro Scheduler 'talk' to an Oracle Database?

[OlgaFB]

Hi,

Could you please tell me if Macro Scheduler can work with an Oracle Database, send and get queries? If yes, how do I do that?

Thank you,
Olga.

[pgriffin]

I read and write to Oracle tables with MacroScheduler. All you need is an ODBC connection on the system which is running MacroScheduler. Then you can use the examples you find here for reading/writing to any SQL database.

Ask a more specific question and we can give a more specific answer.

[OlgaFB]

Thank you :)

Here are more specific questions:

1. Which exactly commands of Macro Scheduler should I use to send a select query to a database? Could you please bring an example?

2. How the select query is returned?

3. Which exactly commands of Macro Scheduler should I use to insert/update values in the database?

Thank you!
Olga.

[Marcus Tettmar]

Search the forum for ADO, ADODB or ODBC. You will find lots of examples. They will all be relevant. You will just need a different connection string for your database.

[OlgaFB]

Thank you!! Will do.

[OlgaFB]

Marcus,

Where do you find all this information about the automation objects? How do you know, which functions are available for Excel, Word objects? And for the database objects, too - is there anywhere a good reference to it?

Thank you very much,
Olga.

[pgriffin]

This site may help you if you have questions about the object accessible via VBScript.

there is also a reference site at msdn.microsoft.com You'll have to navigate to the actual page from there...


http://www.devguru.com/technologies/VBScript/index.asp

[OlgaFB]

Thank you! I will investigate it.

[Sbleck]

Hi,

To other newcomers (like me) that would connect to Oracle databases, I sent the code (slightly modified from the example inside Macro Scheduler Manual, page 47) I used to connect sucessfully:

Code: Select all

//Connect to Datasource
Let>str=Driver={Oracle in XE};dbq=<IP_address_of_the_server>:<1521_as_default_or_other_port_you_or_the_admin_know>/<alias_you_know_in_tnsnames_file>;Uid=<user>;Pwd=<password>;
//
// Example:
//
// Let>str=Driver={Oracle in XE};dbq=192.168.1.1:1521/orcl;Uid=system;Pwd=manager;
//
// Show the resulting connection string
MessageModal>str
DBConnect>str,dbH
//Perform SELECT query
Let>SQL=select * from scott.emp where ename='KING'
'Let>SQL=select * from scott.emp
DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields
MessageModal>SQL: %SQL%%CRLF%Numrecs: %numrecs%%CRLF%Numfields: %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
Message>Closing connection...
//Close database connection
DBClose>dbH

Please note that you could change the data between in the connect string, to reflect your configuration, or course. The "Oracle in XE" was the driver name, as mentioned in the ODBC Administrator (for Windows XP: Start->Control Panel->Administrative Tools->Data Sources (ODBC)->what appeared in the driver column, for User DSN or System DSN tab). Change it, to what you have. If you had not ones, check how to install it...

Please note too that the selected table should exist and could be accessed from the connection user/password you have put in the connection string !

Code: Select all

SQL*Plus: Release 9.2.0.1.0 - Production on Qui Ago 21 18:33:14 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect <user>/<password>@<alias>
Connected.

SQL> select * from scott.emp where ename='KING';

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17/11/81       5000                    10

SQL> disconnect
Disconnected from Oracle Database 10g Release 10.1.0.3.0 - Production

HTH,
Sven