SQLSoft 45 Front St. So., Suite B Issaquah, WA 98027 (206)391-5099 SQL SoftLink is available for $149.95. Visa/Mastercard accepted as well as checks. SQL SOFTLINK USERS MANUAL INTRODUCTION SQLSoft is a leader in the emerging world of client/server computing services for personal computer networks. Our mission is simple: "We weave networks and databases into cost-effective solutions". SQL SoftLink is a utility designed to provide a link between most Windows 2.X and 3.0 applications supporting DDE and Microsoft's OS/2-based SQL Server. Many Windows applications exist today that support DDE. Now, with SQL SoftLink running, users and developers can access the power of the SQL Server relational database . As systems integrators, we are willing to work with our customers in altering SQL SoftLink to satisfy specific needs. If you have a request for such a service don't hesitate to give us a call in order to discuss what we can do for you. Our number is (206)391-5099. What is SQL Server? SQL Server is a high performance, multiuser relational database management system (RDBMS) designed to support high-volume transaction processing - like on-line order entry - as well as less demanding decision-support applications - like sales analysis - on PC-based LANs. SQL Server employs advanced software technology that gives it reliability, performance, and data processing capabilities equal to or better than those found in production-oriented, minicomputer/mainframe-based database management systems (DBMSs). Transaction processing ensures that SQL Server databases are consistent and can be recovered in case of system failure - whatever the cause. Built-in data integrity logic provides a mechanism that enforces complex relational controls within the database itself. Critical business guidelines such as don't delete a customer with open orders can be enforced easily across all applications and users. High performance architecture makes optimum use of current-generation hardware, maintaining high throughput levels as more users are added to the LAN. And advanced administration and security features ensure a secure, easy-to-manage system. All this quality and all these features make true production-oriented, mission-critical applications a reality on local area networks. What is DB-LIBRARY? SQL Server has an open (documented and published) Application Programming Interface (API) that allows any software developer to build client-based (front-end) applications or even server-based applications (back-end applications that include SQL Server itself) that work with SQL Server. DB-LIBRARY is a set of C subroutines that gives application developers a standard way to interface their applications with SQL Server. DB-LIBRARY allows SQL commands to be incorporated into an application, enabling the application to retrieve and update values from a database. How does DB-LIBRARY access SQL Server? DB-LIBRARY routines provide full access to all SQL Server capabilities through a consistent, easy- to-use API. DB-LIBRARY subroutines are divided into the following categories: * Initialization routines allowing front-end applications to connect to a SQL Server or to multiple SQL Servers * Command set-up functions allowing the application to construct SQL queries in a communications buffer and to send these queries over the network to SQL Server * Data retrieval and results processing functions giving front-end applications easy access to the returned data * Close/cleanup routines closing the connection(s) to SQL Server and de-allocating communications structures * Routines handling large text/images, browse, and the two-phase commit protocol for distributed updates * Utility type procedures - datatype conversion, bulk copy, error and message handling, etc. These LIBRARY routines can be used with C, COBOL, or other compilers that conform to Pascal calling conventions. DB-LIBRARY can be used with application programs royalty-free and comes in separate versions for use with DOS-, OS/2- and Microsoft Windows-based client applications. DB- LIBRARY is a static-link library under DOS, a Dynamic link Library under Windows, and a Dynamic Link Library (DLL) under OS/2. The transparent interface of DB-LIBRARY insulates the programmer from networking details and server protocols. This guarantees that an application will run even if the internal specifications of SQL Server change. System software updates won't disrupt the operation of workstation application programs. The approach taken by SQL Server with DB-LIBRARY has several advantages compared to the embedded SQL host language interface used by other DBMSs. Embedded SQL requires that statements be precompiled before the entire application is compiled and linked. Not so with DB- LIBRARY. By avoiding the preprocessing step, writing applications for SQL Server is more straightforward and efficient. When debugging, the developer works with the original source code, not some intermediate form. Not precompiling also saves having to ship a BIND file (a hard-coded access plan associated with each database) with an application. In the future, DB-LIBRARY will also support precompiling to facilitate the porting of mainframe applications to the PC environment, giving developers a choice between the direct call-level interface and the precompiled approach. What is DDE? Dynamic Data Exchange (DDE) is a protocol through which Windows applications can directly communicate. With the use of DDE, Windows applications are able to share information, resulting in a more productive working environment. The difference between DDE and the clipboard is that DDE allows "real time data transfer," while the clipboard allows only for manual, one data transfer. DDE allows these applications to "talk" to each other by establishing active links. These links are created when one application initiates a conversation to another application. The application that initiates the conversation is the "client" and the application that responds is the "server". One requirement of transferring data via DDE is that both applications must be running simultaneously. Many applications utilize the DDE protocol by the use of statements in their macro languages, for example Microsoft Excel and Microsoft Word for Windows. Other applications such as dBFast/Windows are development environments and the DDE statements are part of the procedural code. The basic DDE protocol consists of only nine messages, although each application is slightly different in the way each of these messages are implemented. Only four of these messages are used by the client with SQL SoftLink. These DDE messages are: INITIATE Initiates a DDE conversation between the client and server applications. TERMINATE Terminates a DDE conversation. REQUEST DDE message requesting a one-time transfer of data from the server. POKE DDE message used to send data to the server. DDE Client-Server Diagram Items Poked To SQL SoftLink Server, UserName, Pswd, Dbase Xstr1, Xstr2....Xstr8 Functions Requested From SQL SoftLink Connect, SetOpt, Dbexit, Dbcmd, Dbsqlexec Dbcount, Dbconvert, Dbnextrow, Dbresults, Dbrows Getexcel, Txtdump, Row, Dbfreebuf, Fastins Msgmsg, Dblmsg, Sysmsg What is SQL SoftLink? SQL SoftLink is a Windows utility that runs behind other windows applications, such as Microsoft Excel, Microsoft Word for Windows, and dBFast/Windows. SQL SoftLink makes calls to SQL Server using the DB-LIBRARY dynamic link library for windows. Applications such as Excel can receive data from SQL Server by sending DDE messages to SQL SoftLink which requests the appropriate DB-LIBRARY calls. The first step in making a call to SQL Server is to initiate a conversation between your application and SQL SoftLink. Next is to connect to SQL Server and use the correct database. This is accomplished by poking the information to SQL SoftLink and requesting the appropriate DB- LIBRARY statement. Once connected to SQL Server the actual SQL statement can be poked to SQL SoftLink. The statement will be processed once the correct DB-LIBRARY statement is requested from SQL SoftLink. The final step is to request the data from SQL SoftLink. Once the data is in your application it can be formatted and maneuvered within the constraints of the particular application. After the user is finished accessing data from SQL Server, the conversation can be terminated with the DDE terminate message. Items in SQL SoftLink There are 12 items or variables used while working with SQL SoftLink. They are as follows: ITEMS Server The name of the server on which Microsoft SQL Server resides. UserName The name of the user to be logged on to Microsoft SQL Server. Pswd The password of the user to be logged on to Microsoft SQL Server. Dbase The database to be used on Microsoft SQL Server. Xstr1 Variable used to pass parameters to SQL SoftLink. Xstr2 Variable used to pass parameters to SQL SoftLink. Xstr3 Variable used to pass parameters to SQL SoftLink. Xstr4 Variable used to pass parameters to SQL SoftLink. Xstr5 Variable used to pass parameters to SQL SoftLink. Xstr6 Variable used to pass parameters to SQL SoftLink. Xstr7 Variable used to pass parameters to SQL SoftLink without losing the carriage return and line feed.* Xstr8 Variable used to pass parameters to SQL SoftLink without losing the carriage return and line feed. In addition to the variables listed above, the topic that is called when initiating a conversation in SQL SoftLink is "SQL". A few of the SQL SoftLink functions require that parameters be passed to them to define various items such as data types. Hence, the reasoning for the Xstr1 through Xstr8 Items. *NOTE: Because Microsoft SQL Server does not need a carriage return and line feed, SQL SoftLink filters these out when passed from a client application. We recognize that there might be some instances where a user will want to pass the carriage return and line feed. As a result Xstr7 and Xstr8 can be used to accomplish this task. Xstr7 subtracts 20 from the ascii code of the character it receives and copies this to Xstr8. Therefore, if the user wishes to pass a carriage return to SQL SoftLink he would pass the "!" which has an ascii code of 33. Xstr7 subtracts 20 from this, which is the carriage return (ascii code 13), and copies this to Xstr8. Description of SQL SoftLink Functions SQL SoftLink utilizes about 20 different DB-LIBRARY calls. These calls are combined and used in the 10 SQL SoftLink functions. The following lists the functions and their uses. SQL Soft Link Functions Connect Requests connection to SQL Server and to use a particular database. This function consists of the following DB-LIBRARY calls. Dbclose, Dblogin, Dbsetluser, Dbsetlpwd, Dbopen and Dbuse SetOpt Sets DB-LIBRARY options in SQL Server. This function consists of the following DB- LIBRARY call. Dbsetopt Dbexit Disconnects from SQL Server. This function consists of the following DB-LIBRARY call. Dbexit Dbcmd Adds text to the input buffer. This function consists of the following DB-LIBRARY call. Dbcmd Dbsqlexec Sends the text stored in the input buffer to SQL Server. This function consists of the following DB-LIBRARY call. Dbsqlexec Dbcount Returns the number of rows returned by the SQL statement. This function consists of the following DB-LIBRARY call. Dbcount Dbconvert Returns and convert the data from SQL Server. The data types supported are SQLCHAR, SQLDATETIME, SQLFLT8, SQLINT1, SQLINT2, SQLINT4, and SQLMONEY. The SQL SoftLink translations of these data types are respectively CHAR, DATETIME, FLT8, INT1, INT2, INT4, and MONEY. This function consists of the following DB- LIBRARY calls. Dbconvert, Dbdata, and Dbdatlen Dbnextrow Reads the next row of the query from SQL Server. This function consists of the following DB-LIBRARY call. Dbnextrow Dbresults Sets up the results of the query. This function consists of the following DB-LIBRARY call. Dbresults Dbrows Determines whether the current SQL statement actually returned rows. This function consists of the following DB-LIBRARY call. Dbrows Getexcel Establishes a DDE conversation between SQL SoftLink and Microsoft Excel, where Excel acts as the server and SQL SoftLink is the client. Txtdump Sends entire results query to Microsoft Excel from SQL SoftLink. Row Returns a row of data from SQL SoftLink to the client application. Dbfreebuf Clears the Dbcmd buffer. Fastins Inserts, updates, or deletes data on Microsoft SQL Server. Used when the user is not expecting any data back from Microsoft SQL Server. Msgmsg Returns DBLIB nonerror messages. Dblmsg Returns DBLIB error messages. Sysmsg Returns DOS error messages. SQL SoftLink Functions Connect Syntax: Connect Purpose: To log in to Microsoft SQL Server and to use a particular database Arguments: The server name, user name, password and database need to be poked to SQL SoftLink before the Connect function can be requested. Returns: "F" -if the user did not get logged in. "T" -if the user did get logged in and established a process. "1" -if the user did not establish a process. "2" -if the user could not use the specific database. Example: DDEPoke Channum, "Server", Server$ DDEPoke Channum, "UserName", Name$ DDEPoke Channum, "Pswd", Password$ DDEPoke Channum, "Dbase", Database$ A$=DDERequest$(Channum, "Connect") SetOpt Syntax: SetOpt Purpose: To set DB-LIBRARY options in SQL Server. Arguments: Two parameters need to be passed to SQL SoftLink before calling this function. They are the option and param arguments of the dbsetopt DB- LIBRARY function. The user must pass the option parameter in Xstr1 and the param parameter in Xstr2. Returns: "T" -if the function is successful "F" -if the function is not successful Example: DDEPoke Channum, "Xstr1", "DBBUFFER" DDEPoke Channum, "Xstr2", "500" A$=DDERequest$(Channum, "SetOpt") Dbexit Syntax: Dbexit Purpose: To disconnect from Microsoft SQL Server and close all open processes. Arguments: There are no arguments. Returns: There is no return value. Example: X$=DDERequest$(Channum, "Dbexit") Dbcmd Syntax: Dbcmd Purpose: Adds text to the input buffer. Arguments: One parameter needs to be passed to this function before it is called. The user must use Xstr1 to pass this parameter. Returns: "T" -if successful. "F" -if unsuccessful. Example: DDEPoke Channum, "Xstr1", "select address, city, state, zip from authors" A$=DDERequest$(Channum, "Dbcmd") Dbsqlexec Syntax: Dbsqlexec Purpose: Sends the text stored in the input buffer to Microsoft SQL Server. Arguments: There are no arguments. Returns: "T" -if successful "F" -if unsuccessful Example: B$=DDERequest$(Channum, "Dbsqlexec") Dbcount Syntax: Dbcount Purpose: To determine the number of rows returned by the SQL statement. Arguments: There are no arguments. Returns: The number of rows affected in text format. Example: B$=DDERequest$(Channum, "Dbcount") Dbconvert Syntax: Dbconvert Purpose: Returns and converts the data from Microsoft SQL Server. Arguments: Before calling Dbconvert, two parameters must be passed to it. The first is the number of the item in the SQL statement. For example in the following SQL statement, "Select address, city, state, zip.....", address=1, city=2, sate=3, etc. The other parameter is the type of data that is being returned. The data types supported are SQLCHAR, SQLDATETIME, SQLFLT8, SQLINT1, SQLINT2, SQLINT4, and SQLMONEY. The SQL SoftLink translations of these data types are respectively CHAR, DATETIME, FLT8, INT1, INT2, INT4, and MONEY. Returns: One field of data returned from the requested query. Example: DDEPoke Channum, "Xstr1", "1" DDEPoke Channum, "Xstr2", "CHAR" addr$=DDERequest$(Channum, "Dbconvert") Dbnextrow Syntax: Dbnextrow Purpose: Reads the next row of the query from Microsoft SQL Server. Arguments: There are no arguments. Returns: One of five types of values: "-1" -if a regular row is read. "-2" -if there are no more rows to be read or the statement didn't return any rows. "-3" -If buffering is turned on and reading the next row would cause the buffer to be exceeded. "0" -if the function was unsuccessful. If a compute row is read, the computeid of the row is returned. Example: D$=DDERequest$(Channum, "Dbnextrow") Dbresults Syntax: Dbresults Purpose: Sets up the results of the previous or current query. Arguments: There are no arguments. Returns: One of three types of values: "1" -if successful "0" -if unsuccessful "2" -if there are no more results to be processed. Example: C$=DDERequest$(Channum, "Dbresults") Dbrows Syntax: Dbrows Purpose: To determine whether the current SQL statement actually returned rows. Arguments: There are no arguments. Returns: "1" -if successful "0" -if unsuccessful Example: D$=DDERequest$(Channum, "Dbrows") Getexcel Syntax: Getexcel Purpose: To establish a DDE conversation between SQL SoftLink and Microsoft Excel, where Excel acts as the server and SQL SoftLink is the client. Arguments: One parameter must be passed to SQL SoftLink using Xstr1 before this function can be called. the parameter is the active worksheet and cell. The active cell must be sent in R1C1 format. This can be accomplished using the Selection() function in the Excel macro language. Returns: There is no return. Example: =SELECTION() =REFTEXT(SELECTION()) =FORMULA(A57,B58) =POKE(A4,"Xstr1",B58) =REQUEST(A4,"Getexcel") Txtdump Syntax: Txtdump Purpose: To return data from Microsoft SQL Server. Arguments: There are no arguments, although this function must be called after calling the Getexcel function. Returns: Returns entire query at once from Microsoft SQL Server. Example: =REQUEST(A4,"Getexcel") =REQUEST(A4,"Txtdump") Row Syntax: Row Purpose: To return data from Microsoft SQL Server without having to worry about the data types. Arguments: There are no arguments. Returns: Returns a row at a time from the query to Microsoft SQL Server. The format of the row is a text string with the columns separated with a tab and a carriage return and line feed at the end of each row. After the last row is returned, SQL SoftLink returns a character string 9, which is end of file. Example: While title$ <> Chr$(26) title$ = DDERequest$(Channum, "Row") Wend Dbfreebuf Syntax: Dbfreebuf Purpose: To clear the Dbcmd buffer. Arguments: There are no arguments. Returns: "T" -if successful Example: =REQUEST(A4,"Dbfreebuf") Fastins Syntax: Fastins Purpose: Inserts, updates, or deletes data on Microsoft SQL Server. Used when the user is not expecting any data back from Microsoft SQL Server. Arguments: Before Fastins is called, the user must pass a one line SQL statement to SQL SoftLink with the Xstr1 parameter. Returns: "T" -if successful "F" -if unsuccessful Example: DDEPoke Channum, "Xstr1", "Update authors set au_lname = 'MacBadden' where au_lname = 'McBadden'" X$=DDERequest$(Channum, "Fastins") Msgmsg Syntax: Msgmsg Purpose: To return DB-LIBRARY non-error messages. Arguments: There are no arguments. This will also reset the message buffer once the function has been called. Returns: DB-LIBRARY non-error messages. Example: M$=DDERequest$(Channum, "Msgmsg") Dblmsg Syntax: Dblmsg Purpose: To return DB-LIBRARY error messages. Arguments: There are no arguments This will also reset the message buffer once the function has been called. Returns: DB-LIBRARY error messages. Example: M$=DDERequest$(Channum, "Dblmsg") Sysmsg Syntax: Sysmsg Purpose: To return DOS error messages. Arguments: There are no arguments This will also reset the message buffer once the function has been called. Returns: DOS error messages. Example: M$=DDERequest$(Channum, "Sysmsg") Client Requirements The requirements of the client consist of the following. 386, 386sx or 286 PC set up as a DOS workstation. Microsoft Windows 286 ,386 or Windows 3.0 Microsoft Lan Manager 1.1 or equivalent. Windows front end with DDE capabilities (Microsoft Word for Windows, Microsoft Excel or dBFast/Windows.) 1 Mb RAM Suggested: 386 to the Max memory manager or Qemm. 2 Mb RAM or more. In addition, when running SQL SoftLink under Windows 2.x, the following files are necessary, and should be either in the working directory or in the path. WIN87EM.EXE DBLIBW.EXE If running SQL SoftLink under Windows 3.0, the following files need to be in either the working directory or in the path. DBNMP3.DLL W3DBLIB.DLL WIN87EM.DLL All of the files listed above should be found in the following software packages. Microsoft Windows 286, 386 or 3.0 Microsoft LAN Manager or equivalent Microsoft SQL Server Server Requirements The requirements of the server consist of the following. 386 or 386sx PC set up as a server. OS/2 1.1 Microsoft Lan Manager 1.1 or equivalent. Microsoft SQL Server 1.0 8 Mb RAM Suggested: 10 Mb RAM or more Potential Memory Constraints In the Microsoft Windows 2.x environment memory considerations can be quite a headache. A suggestion we have is to have at least 2 Mb of RAM and take advantage of a memory manager such as 386 To The Max. Here at SQLSoft have spent a great deal of time fine tuning our workstations and have found it extremely rewarding. For example on a 386 DOS Workstation running 3+Open 1.1 with 4 Mb of RAM we are able to get 598k of free memory and after starting Microsoft Windows we have about 430k of conventional memory. We offer support on these memory issues to all registered users of SQL SoftLink. To register please see the registration section. Installation of EXE files Installation of sample macros SQL SOFTLINK AND MICROSOFT WORD FOR WINDOWS The Microsoft Word For Windows macro language, WordBasic is used to send and receive the DDE messages. WordBasic is very similar to the Basic programming language, therefore an understanding of the Basic language is helpful in writing macros in WordBasic. In addition to most of the Basic language, WordBasic has added many Word for Windows functions as well as the DDE messages needed to have a conversation with SQL SoftLink and access SQL Server. DDE Statements The DDE statements used by WordBasic are as follows. DDEInitiate() Syntax: Channum=DDEInitiate(App$, Topic$) Channum is a variable which represents the DDE Channel between Word for windows and SQL SoftLink. This is the channel over which the DDE conversation will take place. App$ is the name of the other application. Topic$ is the name of something being accessed in the other application. In the case of SQL SoftLink the topic is "SQL". Example: Channum=DDEInitiate("SQL_SoftLink", "SQL") This establishes a link between Microsoft Word for Windows and SQL SoftLink. DDEPoke Syntax: DDEPoke Channum, Item$, Data$ Channum refers to the channel established by DDEInitiate. DDEPoke sends the string defined by Data$ to the item in SQL SoftLink specified in Item$. Example: DDEPoke Channum, "Username", Name$ This sends the username specified by Name$ to the item in SQL SoftLink defined as "Username." DDERequest$() Syntax: A$=DDERequest$(Channum, Item$) Channum refers to the channel established by DDEInitiate. DDERequest$() requests the item in SQL SoftLink specified by Item$. Example: A$=DDERequest$(Channum, "Connect") This requests that SQL SoftLink perform the "Connect" function. DDETerminate Syntax: DDETerminate Channum Channum refers to the channel established by DDEInitiate. Example: DDETerminate Channum This terminates the link between Microsoft Word for Windows and SQL SoftLink. How to Implement In order to access Microsoft SQL Server from Microsoft Word for Windows through SQL SoftLink, the first thing needed is to make sure that both Microsoft Word for Windows and SQL SoftLink are up and running (SQL SoftLink may be iconized.) In addition, Microsoft SQL Server must be running on a server on the network. To establish a DDE link between Microsoft Word for Windows and SQL SoftLink a macro needs to be written in WordBasic. The first part of the macro initiates the DDE channel between the two applications. This is performed with the DDEInitiate statement. For example: Channum= DDEInitiate("SQL_SoftLink", "SQL") In order to connect to Microsoft SQL Server the server name, username, password and database need to be poked to SQL SoftLink. Once this is accomplished a request needs to be sent to SQL SoftLink asking for the "Connect" function. For example: DDEPoke Channum, "Server", Server$ DDEPoke Channum, "UserName", Name$ DDEPoke Channum, "Pswd", Password$ DDEPoke Channum, "Dbase", Database$ A$=DDERequest$(Channum, "Connect") After these functions have been accomplished, there should be a connection between Microsoft Word for Windows and Microsoft SQL Server through SQL SoftLink. Now SQL statements can be passed to SQL SoftLink in order to receive queries from Microsoft SQL Server. For example: DDEPoke Channum, "Xstr1", "select address, city, state, zip from authors" Next is to place this SQL statement in a buffer. For example: A$=DDERequest$(Channum, "Dbcmd") Additions to the SQL statement can be made by poking the information in the same manner as above and then requesting "Dbcmd." Once the entire SQL statement is in the buffer, a series of SQL SoftLink functions need to be requested in order to execute the query. The order of these is as follows. B$=DDERequest$(Channum, "Dbsqlexec") C$=DDERequest$(Channum, "Dbresults") D$=DDERequest$(Channum, "Dbrows") Depending on the number of rows returned, a loop may be set up for the next section. This next section retrieves the data obtained in the query. First, a row of data needs to be read. For example: D$=DDERequest$(Channum, "Dbnextrow") Next, the data needs to be returned and converted. This can be accomplished one of two ways; through the use of the Dbconvert or Row functions in SQL SoftLink. Before calling Dbconvert, two parameters must be passed to it. The first is the number of the item in the SQL statement. For example in the SQL statement above: address=1, city=2, sate=3, etc. The other parameter is the type of data that is being returned. For example: DDEPoke Channum, "Xstr1", "1" DDEPoke Channum, "Xstr2", "CHAR" addr$=DDERequest$(Channum, "Dbconvert") DDEPoke Channum, "Xstr1", "2" DDEPoke Channum, "Xstr2", "CHAR" city$=DDERequest$(Channum, "Dbconvert") DDEPoke Channum, "Xstr1", "3" DDEPoke Channum, "Xstr2", "CHAR" state$=DDERequest$(Channum, "Dbconvert") (etc.) With the use of the Row function, there is no need to pass the data type to SQL SoftLink. The types of data are determined within SQL SoftLink. Therefore all that is needed is to set up a loop that requests the Row function. For example: While title$ <> Chr$(26) title$ = DDERequest$(Channum, "Row") Wend The data returned can now be formatted and manipulated with other WordBasic functions. Once all work with Microsoft SQL Server is completed it is best to terminate the link. First the connection to Microsoft SQL Server must be terminated and then terminate the DDE link between Microsoft Word for Windows and SQL SoftLink. For example: X$=DDERequest$(Channum, "Dbexit") DDETerminate Channum Sample Macros The following macros are a demonstration of Microsoft Word for Windows accessing data from Microsoft SQL Server. They can be found by running the "normal.dot" file found on the diskette. The first macro displays a dialog box requesting the server, username, password and database. It then passes these parameters to SQL SoftLink and attempts to connect to Microsoft SQL Server. Dim Shared Channum Sub MAIN Begin Dialog UserDialog 300, 125 OKButton 200, 6, 64, 18 CancelButton 200, 32, 64, 18 Text 8, 10, 52, 12, "&Server" TextBox 75, 8, 106, 18, .TB1 Text 8, 40, 42, 12, "&Name" TextBox 75, 38, 106, 18, .TB2 Text 8, 70, 82, 12, "&Password" TextBox 75, 68, 106, 18, .TB3 Text 8, 100, 82, 12, "&Database" TextBox 75, 98, 106, 18, .TB4 End Dialog Dim dlg As Dialog UserDialog dlg.TB1 = "CEO" dlg.TB2 = "SA" dlg.TB3 = "" dlg.TB4 = "PUBS" GetCurValues dlg Dialog dlg Server$ = dlg.TB1 Name$ = dlg.TB2 Password$ = dlg.TB3 Database$ = dlg.TB4 Print Server$, Name$, Password$, Database$ Channum = DDEInitiate("SQL_SoftLink", "SQL") DDEPoke Channum, "Server", Server$ DDEPoke Channum, "Username", Name$ DDEPoke Channum, "Pswd", Password$ DDEPoke Channum, "Dbase", Database$ A$ = DDERequest$(Channum, "Connect") Print A$ SetGlossary Channel$, Str$(Channum), 0 End Sub This macro logs off of Microsoft SQL Server as well as terminates the DDE link between Microsoft Word for Windows and SQL SoftLink. Sub MAIN C$ = GetGlossary$(Channel$, 0) Channum = Val(C$) If MsgBox("Terminate SQLLink?", 33) Then x$ = DDERequest$(Channum, "Dbexit") DDETerminate Channum MsgBox "SQLLink Terminated.", "", 0 End If End Sub The macro listed below requests the name of an author in the pubs database and then queries the database for that author's address. In addition, it takes advantage of the Row function in order to retrieve all of the titles and year to date sales for the requested author. Sub MAIN C$ = GetGlossary$(Channel$, 0) Channum = Val(C$) addr$ = " " city$ = " " state$ = " " zip$ = " " Prompt$ = "Input Author's First Name." Fname$ = InputBox$(Prompt$) Prompt$ = "Input Author's Last Name." Lname$ = InputBox$(Prompt$) DDEPoke Channum, "Xstr1", "select address, city, state, zip from authors" A1$ = DDERequest$(Channum, "Dbcmd") DDEPoke Channum, "Xstr1", " where au_lname=" + "'" + Lname$ + "'" + " and au_fname=" + "'" + fname$ + "'" A$ = DDERequest$(Channum, "Dbcmd") B$ = DDERequest$(Channum, "Dbsqlexec") C$ = DDERequest$(Channum, "Dbresults") dbr$ = DDERequest$(Channum, "Dbrows") If dbr$ = "0" Then MsgBox "Author Not Found.", "Error", 0 Goto Quitx : End If D$ = DDERequest$(Channum, "Dbnextrow") DDEPoke Channum, "Xstr1", "1" DDEPoke Channum, "Xstr2", "CHAR" addr$ = DDERequest$(Channum, "Dbconvert") DDEPoke Channum, "Xstr1", "2" DDEPoke Channum, "Xstr2", "CHAR" city$ = DDERequest$(Channum, "Dbconvert") DDEPoke Channum, "Xstr1", "3" DDEPoke Channum, "Xstr2", "CHAR" state$ = DDERequest$(Channum, "Dbconvert") DDEPoke Channum, "Xstr1", "4" DDEPoke Channum, "Xstr2", "CHAR" zip$ = DDERequest$(Channum, "Dbconvert") Insert Fname$ + " " + Lname$ LineDown Insert addr$ LineDown Insert City$ + ", " + State$ + " " + Zip$ LineDown LineDown HLine 4 Insert " " + Fname$ + "," E$ = DDERequest$(Channum, "Dbnextrow") LineDown LineDown LineDown LineDown LineDown LineDown LineDown DDEPoke Channum, "Xstr1", "select t1.title, t1.ytd_sales" A1$ = DDERequest$(Channum, "Dbcmd") DDEPoke Channum, "Xstr1", " from titles t1, authors t2, titleauthor t3" A1$ = DDERequest$(Channum, "Dbcmd") DDEPoke Channum, "Xstr1", " where (t2.au_id=t3.au_id) and (t3.title_id=t1.title_id) and" A1$ = DDERequest$(Channum, "Dbcmd") DDEPoke Channum, "Xstr1", " t2.au_fname = " + "'" + Fname$ + "'" + " and t2.au_lname=" + "'" + Lname$ + "'" A1$ = DDERequest$(Channum, "Dbcmd") B$ = DDERequest$(Channum, "Dbsqlexec") C$ = DDERequest$(Channum, "Dbresults") dbr$ = DDERequest$(Channum, "Dbrows") title$ = " " While title$ <> Chr$(26) title$ = DDERequest$(Channum, "Row") If title$ = Chr$(26) Then Goto Quitx : End If Insert title$ LineDown Wend Quitx: If MsgBox("Terminate SQLLink?", 33) Then x$ = DDERequest$(Channum, "Dbexit") Print x$ DDETerminate Channum MsgBox "SQLLink Terminated.", "", 0 End If End Sub SQL SOFTLINK AND MICROSOFT EXCEL The Microsoft Excel Macro Language is used to send and receive the DDE messages. The Macro Language contains the necessary DDE statements required to have a conversation between Microsoft Excel and SQL SoftLink. DDE Statements The DDE Statements used in the Microsoft Excel Macro Language are as follows: Initiate() Syntax: =Initiate(App$, Topic$) The Initiate() command creates a DDE Channel between Microsoft Excel and SQL SoftLink. This is the channel over which the DDE conversation will take place. App$ is the name of the other application. Topic$ is the name of something being accessed in the other application. In the case of SQL SoftLink the Topic$ is "SQL". Example: =Initiate("SQL_SoftLink", "SQL") This establishes a link between Microsoft Excel and SQL SoftLink. Poke() Syntax: =Poke(Channum, Item$, Data) Channum refers to the channel established by Initiate(). Poke sends the string defined by Data to the item in SQL SoftLink specified in Item$. Example: =Poke(A4,"UserName",I19) This sends the username defined by cell I19 to the item in SQL SoftLink defined as "UserName" across the channel defined in cell A4 by the Initiate statement. Request() Syntax: =Request(Channum, Item) Channum refers to the channel established by Initiate(). Request() requests the item in SQL SoftLink specified by Item. Example: =Request(A4, "Connect") This requests that SQL SoftLink perform the "Connect" function across the DDE channel defined in cell A4 by the Initiate() statement. Terminate(Channum) Syntax: Terminate(Channum) Channum refers to the channel established by DDEInitiate. Example: Terminate(A4) This terminates the link between Microsoft Word for Windows and SQL SoftLink that was created by the Initiate() statement in cell A4. How to Implement In order to access Microsoft SQL Server from Microsoft Excel through SQL SoftLink, the first thing needed is to make sure that both Microsoft Excel and SQL SoftLink are up and running (SQL SoftLink may be iconized.) In addition, Microsoft SQL Server must be running on a server on the network. To establish a DDE link between Microsoft Excel and SQL SoftLink a macro needs to be written in the Microsoft Excel Macro Language. The first part of the macro initiates the DDE channel between the two applications. This is performed with the Initiate statement. For example: =Initiate("SQL_SoftLink", "SQL") In order to connect to Microsoft SQL Server the server name, username, password and database need to be poked to SQL SoftLink. Once this is accomplished a request needs to be sent to SQL SoftLink asking for the "Connect" function. For example: =Poke(A4,"Server",I17) =Poke(A4,"UserName",I19) =Poke(A4,"Pswd",I21) =Poke(A4,"Dbase",I23) =Request(A4,"Connect") A4=cell location of Channum I17, I19, I21, I23 are cell locations of Servername, Username, Password, and Database respectively. After these functions have been accomplished, there should be a connection between Microsoft Excel and Microsoft SQL Server through SQL SoftLink. Now SQL statements can be passed to SQL SoftLink in order to receive queries from Microsoft SQL Server. For example: Cell Location A33 A34 A35 Macro Statements ="select address, city, state, zip from authors" =Formula(A33,B32) =Poke(A4,"Xstr1",B32) The Formula() function is necessary to place the SQL statement literally in a cell. The poke statement then sends the SQL statement to SQL SoftLink. Next is to place this SQL statement in a buffer. For example: Cell Location A36 Macro Statements =Request(A4,"Dbcmd") Additions to the SQL statement can be made by poking the information in the same manner as above and then requesting "Dbcmd." Once the entire SQL statement is in the buffer, a series of SQL SoftLink functions need to be requested in order to execute the query. The order of these is as follows. Cell Location A49 A50 A51 Macro Statements =Request(A4,"Dbsqlexec") =Request(A4,"Dbresults") =Request(A4,"Dbrows") Next, the data needs to be returned and converted. There are two ways to receive this data. The first is much faster than the second, although both will accomplish the same task. This first method consists of the use of the Getexcel and Txtdump functions. Instead of having to convert all the data using Dbconvert, it is possible to have SQL SoftLink perform this task. In order to use these functions it is required that the active cell be poked to SQL SoftLink. This can be accomplished with the following Excel macro functions. =SELECTION() =REFTEXT(SELECTION()) =FORMULA(A57,B58) =POKE(A4,"Xstr1",B58) Once this information has been transferred to SQL SoftLink the Getexcel and Txtdump functions can be requested. The results will be sent back to Excel starting at the location of the active cell. =REQUEST(A4,"Getexcel") =REQUEST(A4,"Txtdump") The second method is much, much slower. It is implemented in the following way. Depending on the number of rows returned, a loop may be set up for the next section. This next section retrieves the data obtained in the query. First, a row of data needs to be read. For example: Cell Location A55 Macro Statements =Request(A4,"Dbnextrow") This is done by calling the Dbconvert function in SQL SoftLink. Before this function can be called, two parameters must be passed to it. The first is the number of the item in the SQL statement. For example in the SQL statement above: address=1, city=2, state=3, etc. The other parameter is the type of data that is being returned. For example: Cell Location A57 Macro Statements 1 A58 =Poke(A4,"Xstr1",A57) A59 CHAR A60 =Poke(A4,"Xstr2",A59) A61 =Request(A4,"Dbconvert) A62 2 A63 =Poke(A4,"Xstr1",A62) A64 CHAR A65 =Poke(A4,"Xstr1",A67) A66 =Poke(A4,"Xstr2",A64) A67 =Request(A4,"Dbconvert) A68 3 A69 CHAR A70 =Poke(A4,"Xstr2",A69) A71 =Request(A4,"Dbconvert) (etc.) Cell A61 contains the address. Cell A66 contains the city. Cell A71 contains the state. The data returned can now be formatted and manipulated with other Microsoft Excel Macro Language functions. Once all work with Microsoft SQL Server is completed it is best to terminate the link. First the connection to Microsoft SQL Server must be terminated and then terminate the DDE link between Microsoft Excel and SQL SoftLink. For example: =Request(A4,"Dbexit") =Terminate(A4) Sample Macros The following macros are the same macros found on the supplied diskette. The first macro is an example of a connection to Microsoft SQL Server. A2 SQL.Connect A3 =WORKSPACE(,,,,,,,FALSE) A4 =INITIATE("SQL_SoftLink","SQL") A5 =DIALOG.BOX(C13:I23) A6 =IF(A5=FALSE,GOTO(A13)) A7 =POKE(A4,"Server",I17) A8 =POKE(A4,"UserName",I19) A9 =POKE(A4,"Pswd",I21) A10 =POKE(A4,"Dbase",I23) A11 =REQUEST(A4,"Connect") A12 =IF(A10="T",ALERT("Connected to SQLServer",2),ALERT("Not Connected to SQLServer",2)) A13 =RETURN() This macro is an example of a disconnection from Microsoft SQL Server. A16 SQL.Disconnect A17 =ALERT("Terminate SQLServer Connection?",1) A18 =A17 A19 =IF(A17=TRUE,GOTO(A20),GOTO(A23)) A20 =REQUEST(A4,"Dbexit") A21 =TERMINATE(A4) A22 =ALERT("SQLServer Connection Terminated",2) A23 =RETURN() The following macro is an example of a query to Microsoft SQL Server and takes advantage of the Getexcel and Txtdump functions. A31 SQL.Query A32 ="select t1.Au_fname,t1.au_lname, t3.title,t3.price" A33 =FORMULA(A32,B32) A34 =POKE(A4,"Xstr1",B32) A35 =REQUEST(A4,"Dbcmd") A36 =" from authors t1, titleauthor t2, titles t3" A37 =FORMULA(A36,B36) A38 =POKE(A4,"Xstr1",B36) A39 =REQUEST(A4,"Dbcmd") A40 =" where (t2.title_id=t3.title_id) and (t1.au_id=t2.au_id)" A41 =FORMULA(A40,B40) A42 =POKE(A4,"Xstr1",B40) A43 =REQUEST(A4,"Dbcmd") A44 =" order by t3.price desc" A45 =FORMULA(A44,B44) A46 =POKE(A4,"Xstr1",B44) A47 =REQUEST(A4,"Dbcmd") A48 =REQUEST(A4,"Dbsqlexec") A49 =REQUEST(A4,"Dbresults") A50 =REQUEST(A4,"Dbrows") A51 =IF(A50="0",ALERT("Author Not Found",2)) A52 =SELECTION() A53 =REFTEXT(SELECTION()) A54 =FORMULA(A53,B58) A55 =POKE(A4,"Xstr1",B58) A56 =REQUEST(A4,"Getexcel") A57 =REQUEST(A4,"Txtdump") A58 =RETURN() The last macro found on the disk executes a single query to Microsoft SQL Server based on a specific author in the Pubs database. It shows how to take advantage of the Dbconvert function. B116 SQL.Query2 B117 =DIALOG.BOX(C30:I36) B118 =IF(B117=FALSE,GOTO(B186)) B119 ="select t1.au_id, t2.title_id, t3.title, t3.price, t3. ytd_sales,t1.Au_fna B120 =FORMULA(B119,C120) B121 =POKE(A4,"Xstr1",C120) B122 =REQUEST(A4,"Dbcmd") B123 =" from authors t1, titleauthor t2, titles t3" B124 =FORMULA(B123,C124) B125 =POKE(A4,"Xstr1",C124) B126 =REQUEST(A4,"Dbcmd") B127 =" where (t1.au_fname='"&I34&"' and t1.au_lname='"&I36&"')" B128 =FORMULA(B127,C128) B129 =POKE(A4,"Xstr1",C128) B130 =REQUEST(A4,"Dbcmd") B131 =" and (t2.title_id=t3.title_id) and (t1.au_id=t2.au_id)" B132 =FORMULA(B131,C132) B133 =POKE(A4,"Xstr1",C132) B134 =REQUEST(A4,"Dbcmd") B135 =REQUEST(A4,"Dbsqlexec") B136 =REQUEST(A4,"Dbresults") B137 =REQUEST(A4,"Dbrows") B138 =IF(B137=0,ALERT("Author Not Found",2)) B139 =WHILE(TRUE) B140 =REQUEST(B109,"Dbnextrow") B141 =IF(B140<>-1,GOTO(B186)) B142 1 B143 =POKE(B91,"Xstr1",B142) B144 CHAR B145 =POKE(B91,"Xstr2",B144) B146 =REQUEST(B91,"Dbconvert") B147 3 B148 =POKE(B91,"Xstr1",B147) B149 CHAR B150 =POKE(B91,"Xstr2",B149) B151 =REQUEST(B91,"Dbconvert") B152 4 B153 =POKE(B91,"Xstr1",B152) B154 MONEY B155 =POKE(B91,"Xstr2",B154) B156 =REQUEST(B91,"Dbconvert") B157 5 B158 =POKE(B91,"Xstr1",B157) B159 INT4 B160 =POKE(B91,"Xstr2",B159) B161 =REQUEST(B91,"Dbconvert") B162 6 B163 =POKE(B91,"Xstr1",B162) B164 CHAR B165 =POKE(B91,"Xstr2",B164) B166 =REQUEST(B91,"Dbconvert") B167 7 B168 =POKE(B91,"Xstr1",B167) B169 CHAR B170 =POKE(B91,"Xstr2",B169) B171 =REQUEST(B91,"Dbconvert") B172 =FORMULA(B146) B173 =SELECT("rc[1]") B174 =FORMULA(B166) B175 =SELECT("rc[1]") B176 =FORMULA(B171) B177 =SELECT("rc[1]") B178 =FORMULA(B151) B179 =SELECT("rc[1]") B180 =FORMULA(B156) B181 =SELECT("rc[1]") B182 =FORMULA(B161) B183 =SELECT("r[1]c[-5]") B184 =NEXT() B185 =RETURN() The following is the macro for the dialog box in the SQL.Connect macro. The following is the macro for the dialog box in the SQL.Query2 macro. SQL SOFTLINK AND DBFAST/WINDOWS dBFast/Windows is a dBase compiler for the Microsoft Windows environment. It takes the dBase code and adds many windows functions as well as the DDE messages necessary to talk to SQL SoftLink and therefore Microsoft SQL Server. DDE Statements The DDE Statements used in the dBFast/Windows development environment are as follows: Talk Syntax: TALK TO ABOUT The Talk statement is similar to the Initiate() command in the other to front end applications described in that it creates a DDE Channel between the application being developed withe dBFast/Windows and SQL SoftLink. This is the channel over which the DDE conversation will take place. Application is the name of the other application. Topic is the name of something being accessed in the other application. In the case of SQL SoftLink the topic is "SQL". Example: TALK TO SQL_SoftLink ABOUT SQL This establishes a link between your application and SQL SoftLink. Contact() Syntax: Contact() This DDE function is not found in the other two front end applications. This function returns a logical value indicating whether or not there is an active DDE channel. Example: CONTACT() With this function it is possible to determine whether or not there is a connection with the other application, for example SQL SoftLink. Senddata() Syntax: Senddata(Item, Data) Senddata() sends the string defined by Data to the item in SQL SoftLink specified in Item. Example: SENDDATA("UserName",name) This sends the username defined by name to the item in SQL SoftLink defined as "UserName". Request() Syntax: Request(Item) Request() requests the item in SQL SoftLink specified by Item. Example: x=REQUEST("Connect") This requests that SQL SoftLink perform the "Connect" function across the DDE channel. Terminate Syntax: Terminate This will terminate the DDE channel opened up with the Talk command. Example: TERMINATE This terminates the link between your application and SQL SoftLink that was created by the Talk command. How to Implement In order to access Microsoft SQL Server from the application being developed with dBFast/Windows through SQL SoftLink, the first thing needed is to make sure that SQL SoftLink is up and running (SQL SoftLink may be iconized.) In addition, Microsoft SQL Server must be running on a server on the network. To establish a DDE link between the application and SQL SoftLink the DDE statements need to be implemented in the program. The first part of the program initiates the DDE channel between the two applications. This is performed with the Talk statement. For example: TALK TO "SQL_SoftLink" ABOUT "SQL" In order to connect to Microsoft SQL Server the server name, username, password and database need to be poked to SQL SoftLink. Once this is accomplished a request needs to be sent to SQL SoftLink asking for the "Connect" function. For example: SENDDATA("Server",server) SENDDATA("UserName",name) SENDDATA("Pswd",password) SENDDATA("Dbase",database) x=REQUEST("Connect") After these functions have been accomplished, there should be a connection between your application and Microsoft SQL Server through SQL SoftLink. Now SQL statements can be passed to SQL SoftLink in order to receive queries from Microsoft SQL Server. For example: SENDDATA("Xstr1","select address, city, state, zip from authors") The Senddata function sends the SQL statement to SQL SoftLink. Next is to place this SQL statement in a buffer. For example: REQUEST("Dbcmd") Additions to the SQL statement can be made by poking the information in the same manner as above and then requesting "Dbcmd." Once the entire SQL statement is in the buffer, a series of SQL SoftLink functions need to be requested in order to execute the query. The order of these is as follows. REQUEST("Dbsqlexec") REQUEST("Dbresults") REQUEST("Dbrows") Depending on the number of rows returned, a loop may be set up for the next section. This next section retrieves the data obtained in the query. First, a row of data needs to be read. For example: REQUEST("Dbnextrow") Next, the data needs to be returned and converted. This is done by calling the Dbconvert function in SQL SoftLink. Before this function can be called, two parameters must be passed to it. The first is the number of the item in the SQL statement. For example in the SQL statement above: address=1, city=2, state=3, etc. The other parameter is the type of data that is being returned. For example: SENDDATA("Xstr1", "1") SENDDATA("Xstr2", "CHAR") addr=REQUEST("Dbconvert") SENDDATA("Xstr1", "2") SENDDATA("Xstr2", "CHAR") city=REQUEST("Dbconvert") SENDDATA("Xstr1", "3") SENDDATA("Xstr2", "CHAR") state=REQUEST("Dbconvert") (etc.) The data returned can now be formatted and manipulated with other dBFast/Windows functions and commands. Once all work with Microsoft SQL Server is completed it is best to terminate the link. First the connection to Microsoft SQL Server must be terminated and then terminate the DDE link between Microsoft Excel and SQL SoftLink. For example: REQUEST("Dbexit") TERMINATE Sample Program ON the disk you will find a sample program written in dBFast/Windows that queries the Pubs database on Microsoft SQL Server. The compiled program is SSLDBF.EXE. The PRG for this program has also been provided; SSLDBF.PRG. SUGGESTED IMPLEMENTATION AIDS Integrating Applications Using Dynamic Data Exchange WexTech Systems, Inc. 60 East 42nd Street New York, NY 10165 (212)949-9595 Microsoft Word for Windows Technical Reference Manual Microsoft Excel Technical Reference Manual DDEWatch by Horizon Technologies This is an excellent product which allows a user to watch the DDE statements being passed between the client and server. It is an excellent debugging tool. Horizon Technologies Inc. 2356 Science Parkway, Suite 100 Okemos, MI 48864 (517)347-0800 Microsoft Product Support for support in writing macros in Excel and Word for Windows. (206)454-2030 Microsoft Online for information on implementing DDE. FUTURE ENHANCEMENTS Future upgrades and products that we are currently working on are: Microsoft SQL Server connection with OS/2 Presentation Manager Microsoft SQL Server connection with Superbase 4 REGISTRATION By purchasing a copy of SQL SoftLink you are automatically registered and eligible for technical support. TECHNICAL SUPPORT If you have a technical question regarding SQL SoftLink please feel free to call us at (206)391-5099, Monday -Friday between 8:00am and 5:00pm Pacific Time, or write us at: SQLSoft 45 Front St., So., Suite B Issaquah, WA 98027 We do not charge a fee for telephone support for the first 15 minutes during the initial 30 day period to ensure that you get all your questions answered. If you need further technical support, you will be billed at the rate of $120.00 per hour or $20.00 for each 10 minute interval. If you require customization work please contact us for our rates. We appreciate all suggestions and notifications of possible bugs. -------------------------------------------------------------------------------------------------------------------------------- SQL SoftLink- Copyright (c) 1990 SQLSoft. All rights reserved (206)391-5099 7