Tutorial sederhana pemrograman Java GUI dengan Netbeans dan database MySQL
Ada yang mw belajar PHP dari dasar? Barangkali tutoriaini bisa membantu, silahkan download ajjah....Full description
Descripción completa
Pemorgraman web WAP
rencana layanan terpadu
Digested Case
Deskripsi lengkap
Full description
Case DigestFull description
Full description
Descripción: Report Writer
ACTIVIDAD DE APRENDIZAJE MysqlDescripción completa
Descripción completa
mysqlDescripción completa
Descripción completa
Strategi forex terbaik 2018 anti loss anti mc.. Teknik alamFull description
tehnik radiografi kepala
.
Halaman 9 hingga 10 tidak ditampilkan dalam pratinjau ini.
Halaman 9 hingga 10 tidak ditampilkan dalam pratinjau ini.
Halaman 13 hingga 140 tidak ditampilkan dalam pratinjau ini.
Halaman 144 bukan bagian dari pratinjau buku ini.
Halaman 146 bukan bagian dari pratinjau buku ini.
Halaman 149 hingga 153 tidak ditampilkan dalam pratinjau ini.
Halaman 157 bukan bagian dari pratinjau buku ini.
Halaman 155 bukan bagian dari pratinjau buku ini.
How to Connect to a SQL Server from Visual FoxPro (Page 1 of 2 ) In Microsoft public newsgroups, I've noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I've decided to write this article to cover such an important topic. There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:
SQLConnect() SQLStringConnect()
The SQLConnect() Function There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel. The following example creates a connection to a remote server using the ODBCNorthwind DSN: LOCAL hConn hConn = SQLConnect("ODBCNorthwind", "sa", "") The second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source. The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection: LOCAL hConn CREATE DATABASE cstemp CREATE CONNECTION Northwind ; DATASOURCE "ODBCNorthwind" ; USERID "sa" ; PASSWORD "" hConn = SQLConnect("Northwind") SQLStringConnect() Function The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings. The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver's documentation.
The following table lists some commonly used connection string options for SQL Server: Option
Description
DSN
References an ODBC DSN.
Driver
Specifies the name of the ODBC driver to use.
Server
Specifies the name of the SQL Server to connect to.
UID
Specifies the login ID or username.
PWD
Specifies the password for the given login ID or username.
Database
Specifies the initial database to connect to.
APP
Specifies the name of the application making the connection.
WSID
The name of the workstation making the connection.
Specifies whether the login is Trusted_Connection being validated by the Windows NT Domain. Not all of the options listed in the above table have to be used for each connection. For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect(). Note: You can use the name of your server instead of the string. SQL Server 2000 code example: LOCAL hConn hConn = SQLStringConnect("Driver=SQL Server;Server=;"+ ; UID=sa;PWD=;Database=Northwind") hConn = SQLStringConnect("DSN=ODBCNorthwind;UID=sa;PWD=;Database=Northwind") hConn = SQLStringConnect("DSN=ODBCNorthwind;Database=Northwind;Trusted_Connection=Yes") Handling Connection Errors Both the SQLConnect() and SQLStringConnect() functions return a connection handle. If the connection is established successfully, the handle will be a positive integer. If Visual FoxPro failed to make the connection, the handle will contain a negative integer. A simple call to the AERROR() function can be used to retrieve the error number and message. The following example traps for a failed connection and displays the error number and message using the Visual FoxPro MESSAGEBOX() function.
Visual FoxPro returns error 1526 for all errors against a remote data source. The fifth element of the array returned by AERROR() contains the remote data source-specific error. #define MB_OKBUTTON 0 #define MB_STOPSIGNICON 16 LOCAL hConn hConn = SQLConnect("ODBCNorthwind", "falseuser", "") IF (hConn < 0) LOCAL ARRAY laError[1] AERROR(laError) MESSAGEBOX( ; laError[2], ; MB_OKBUTTON + MB_STOPSIGNICON, ; "Error " + TRANSFORM(laError[5])) ENDIF Disconnecting From SQL Server It is very important that a connection be released when it is no longer needed by the application because connections consume valuable resources on the server, and the number of connections may be limited by licensing constraints. You break the connection to the remote data source using the SQLDisconnect() function. SQLDisconnect() takes one parameter, the connection handle created by a call to either SQLConnect() or SQLStringConnect(). SQLDisconnect() returns a 1 if the connection was correctly terminated and a negative value if an error occurred. The following example establishes a connection to SQL Server, and then drops the connection: LOCAL hConn,lnResult *hConn = SQLStringConnect("Driver=SQL Server;Server=;"+ ; UID=sa;PWD=;Database=Northwind") hConn = SQLConnect("ODBCNorthwind", "sa", "") IF (hConn > 0) MESSAGEBOX("Connection has done") lnResult = SQLDisconnect(hConn) IF lnResult < 0 MESSAGEBOX("Disconnect failed") ENDIF && lnResult < 0 ENDIF && hConn > 0 If the parameter supplied to SQLDisconnect() is not a valid connection handle, Visual FoxPro will return a run-time error (#1466). Currently there is no way to determine whether a connection handle is valid without attempting to use it. To disconnect all SQL pass through connections, you can pass a value of zero to SQLDisconnect().
To establish the connection with the remote server you can use one of two functions SQLConnect() OR SQLStringConnect() and there are two ways to use the SQLConnect() function to connect to a remote data source. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the control panel and the second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The other function is SQLStringConnect() requires a single parameter, a string of semicolondelimited options that describes the remote data source and optional connections settings. We have to know that both the SQLConnect() and SQLStringConnect() functions return a connection handle. To break the connection to the remote data source is by using the SQLDisconnect() function.
Integrating SQL Server with Visual FOXPRO Integrating SQL Server with Visual FoxPro Updated : July 19, 2001 By Robert Green
Introduction Your good friends on the Visual FoxPro team at Microsoft spent a great deal of time to make Visual FoxPro a robust and powerful front-end for client-server applications. Remote views and SQL pass-through provide powerful tools to take advantage of SQL back-ends such as Oracle and SQL Server via ODBC (Open Database Connectivity). One of the great truisms of application development is that there are many ways to do everything. One of the hardest things to do when building an application is to decide on an approach and to know if it is better than the other approaches. In client-server development this is compounded by the fact that you are dealing with two database engines, Visual FoxPro on the front-end and a very powerful SQL database engine on the back-end. This session will explore two approaches to building client-server applications: remote views and SQL pass-through. You will see some of the pros and cons of each approach. Of course, the best solution will vary from application to application but after you see the examples here you will have a better feel for the strengths of these two methods and how you would use them to build client-server applications. The Sample Data The data used in this session's examples is from a SQL Server 6.5 based library application. The library wants to keep track of its members, its books and its loans. The library database schema is shown in Figure 1.
Figure 1: Library Database Schema Table Structures One of the central tables in the Library application is the Member table, which contains one row for every member of the library. An interesting twist here is that juveniles can only be members of the library if they have a sponsoring adult. Since a juvenile lives, presumably, in the same place as the adult there are separate tables for Adult and Juvenile. This saves disk space because all of a juvenile's address information is redundant once you know who the adult is. Also, the juvenile's expiration date is the same as the adult's. Further, you don't care about the adult's birth date although you do care about a juvenile's birth date, but only because on their 18th birthday he or she becomes an adult (at least as far as the tables are concerned!). The following code shows the SQL Server statements used to create the Member, Adult and Juvenile tables: CREATE TABLE member ( member_no member_no lastname shortstring firstname shortstring middleinitial letter photograph image CREATE TABLE adult ( member_no member_no street shortstring city shortstring state statecode zip zipcode phone_no phonenumber expr_date datetime CREATE TABLE juvenile ( member_no member_no adult_member_no member_no birth_date datetime
NOT NULL IDENTITY(1,1), NOT NULL , NOT NULL , NULL , NULL ) NOT NOT NOT NOT NOT
NULL NULL NULL NULL NULL NULL NOT NULL
, , , , , , )
NOT NULL , NOT NULL , NOT NULL )
The member_no field in the Member table is automatically generated by SQL Server when a new row is added. This field is an Identity column. The seed value of 1 and the increment value of 1 specify that the first row entered into the table should have a member_no of 1. For every row inserted after that the member_no is incremented by 1. When adding a row the client does not specify the member_no. SQL Server takes care of it automatically and the client can ask what number was used. The member_no field in the Adult and Juvenile tables is not an Identity column. The value in that column has to match the member_no of the corresponding row in the Member table. When a new member is added to the library, a row is first added to the Member table. The SQL Server global variable @@Identity contains the automatically generated member_no. A row is then added to the Adult or Juvenile table and the value for the member_no will be whatever is in @@Identity.
Declarative Referential Integrity In prior versions of SQL Server referential integrity was enforced through the use of triggers, which is the same way Visual FoxPro enforces referential integrity. SQL Server 6.0 added declarative referential integrity, which allows you to define your RI rules as part of the data structure. The first step is to create a Primary Key constraint in each table, as shown in the following code: ALTER TABLE member ADD CONSTRAINT member_ident PRIMARY KEY CLUSTERED (member_no) ALTER TABLE adult ADD CONSTRAINT adult_ident PRIMARY KEY CLUSTERED (member_no) ALTER TABLE juvenile ADD CONSTRAINT juvenile_ident PRIMARY KEY CLUSTERED (member_no)
The Primary Key constraint creates a Unique index, which enforces the uniqueness of the member_no. In the examples here a clustered index, which physically sorts the data, is created. The second step in defining declarative referential integrity is to create Foreign Key constraints between related tables, as shown in the following code: ALTER TABLE adult ADD CONSTRAINT adult_member_link FOREIGN KEY (member_no) REFERENCES member (member_no) ALTER TABLE juvenile ADD CONSTRAINT juvenile_member_link FOREIGN KEY (member_no) REFERENCES member (member_no) ALTER TABLE juvenile ADD CONSTRAINT juvenile_adult_link FOREIGN KEY (adult_member_no) REFERENCES adult (member_no)
The first Alter Table defines a relationship between the Member and Adult tables. This is a oneto-one relationship, although there is nothing in the code to indicate or enforce that aspect of the relationship. The second Alter Table defines a relationship between the Member and Juvenile tables. The final Alter Table defines a relationship between the Adult and Juvenile tables. This is a one-to-many relationship. Be aware that SQL Server currently does not support cascading updates or deletes. If you wanted those you would use triggers instead of constraints. Using Views - Attempt 1 The first pass at building the Library application uses remote views. Views are easy to set up, can be parameterized so they only return one or a few records at a time, support row and table buffering and support transaction processing. With all that power built in you would be remiss not to consider building client-server applications based on remote views.
The Views In the Library database (the Visual FoxPro version) you will find the remote views vAdultMember and vJuvenileMember. The following shows the SQL statements that define the two views: SELECT Member.member_no, Member.lastname, Member.firstname, ; Member.middleinitial, Adult.street, Adult.city, ; Adult.state, Adult.zip, Adult.phone_no, Adult.expr_date ; FROM dbo.adult Adult, dbo.member Member ; WHERE Adult.member_no = Member.member_no ; AND Member.member_no = ?nMemberID SELECT Member.member_no, Member.lastname, Member.firstname, ; Member.middleinitial, Juvenile.adult_member_no, ; Juvenile.birth_date, Adult.street, Adult.city, Adult.state, ; Adult.zip, Adult.phone_no, Adult.expr_date ; FROM dbo.adult Adult, dbo.juvenile Juvenile, ; dbo.member Member ; WHERE Adult.member_no = Juvenile.adult_member_no ; AND Juvenile.member_no = Member.member_no ; AND Member.member_no = ?nMemberID
The two views are very straightforward. The member's name is in the Member table and the address is in Adult. A juvenile's birth date and sponsoring adult can be found in the Juvenile table. Both views are based on the same connection and are made modifiable by identifying the primary key in each table and by marking the other fields as updatable. This occurs in the Update Criteria tab in the View Designer. Loading the Form The form MEMBVIEW.SCX uses these two views. The following code is the form's Load method. The form comes up with no data loaded because the two views are opened with the NoData option. Optimistic row buffering is then set for the view cursors. Open Database library Use vAdultMember In 0 NoData = CursorSetProp("Buffering", DB_BUFOPTRECORD, ; "vAdultMember") Use vJuvenileMember In 0 NoData = CursorSetProp("Buffering", DB_BUFOPTRECORD, ; "vJuvenileMember")
Locating a Member The user can enter a member's ID and press the Locate button. This supplies a value for the nMemberID parameter and the view is requeried. The following code is in the Click event of the Locate button:
nMemberID = Val(ThisForm.txtMemberID.Value) Select vAdultMember = ReQuery() If RecCount("vAdultMember") = 0 Select vJuvenileMember = ReQuery() If RecCount("vJuvenileMember") = 0 lcMessage = "There is no member with this ID." = MessageBox(lcMessage , MB_ICONINFORMATION)
The code first checks to see if the user had made changes. It then stores the entered ID to the nMemberID variable. The Adult view is requeried first. If no record is found with that ID the Juvenile view is requeried. If no record is found with that ID then the ID is not valid. If the member is found the information is displayed on the form. Adding a Member When the user presses the Add button he or she is presented with an empty form. The record is not actually saved until the Save button is pressed. The Click event code of the Add button first checks to see if the user has made changes to the current member record. TableRevert() is issued on the view that is not in use to make sure Visual FoxPro does not think changes are pending in that view. A blank record is then added to the view in use and the form is refreshed. The user enters the new member's information in the blank record. When the user presses Save, Visual FoxPro will send the new record to SQL Server. Saving Changes One of the nice features of views is that dealing with the back-end is handled for you. The TableUpdate() function is used to save changes to the view's underlying table(s). Visual FoxPro handles this automatically. The following code is in the Click event of the Save button: If TableUpdate() = MessageBox(lcMessage, MB_ICONINFORMATION) Else ThisForm.ShowError Endif
The vAdultMember or vJuvenileMember view is selected, depending on whether the current member is an adult or a juvenile. A TableUpdate() is then issued. If it worked, the data was saved. If it didn't, the user is shown what the problem is. Notice that because this code uses TableUpdate(), it handles both adding new members and making changes to existing members. Deleting a Member To delete a row on the back-end it merely needs to be deleted in the view. The following code is in the Click event of the Delete button:
Select (ThisForm.cViewInUse) Delete If TableUpdate() = MessageBox("This member has been deleted.", ; MB_ICONINFORMATION) Append Blank Else ThisForm.ShowError =TableRevert() Endif
After the user is asked to confirm the deletion the appropriate view is selected, the record is deleted from the view and TableUpdate() is issued. If it works, the record is gone on the backend. If it doesn't, the user is shown what went wrong. Issues Isn't Visual FoxPro wonderful? How easy can client-server be, you ask? This form is elegantly simple and easy to use. There are only three problems with it. You can't add new members To test this, press the Add button, add a new member and press Save. After a small pause you will see the message shown in Figure 2.
Figure 2: Error Message After Adding a Member When the Add button was pressed, a blank record was appended to the vAdultMember view cursor. This view is made up of two tables, Adult and Member. When the Save button is pressed, Visual FoxPro sends the name information to the Member table. SQL Server automatically supplies the member ID. So far so good. Visual FoxPro also sends the address information to the Adult table. But it doesn't send anything for the member's ID. That column is left empty, which is invalid and causes the TableUpdate() to fail. For this to work, the new row needs to be added to the Member table first and the resulting ID needs to be put into the Adult table along with the address information. There is no reason to think that Visual FoxPro view knows this. How could it? Visual FoxPro doesn't know what the ID is and wouldn't know to send it in the Adult table anyway. You can't delete members
In the Library database (the SQL Server version) there is referential integrity defined between the Adult and Juvenile tables and between the Member and Loan tables. You can not delete a member if he or she is an adult responsible for juvenile members or if he or she has outstanding loans. You would expect SQL Server to reject the deletion and send back an error. However, you should be able to delete a member if he or she has no associated juveniles and no outstanding loans. Try this and you will see the error message shown in Figure 3.
Figure 3: Error Message After Deleting a Member This error, once you decipher the SQL Server language, tells you that you have violated referential integrity between the Adult and Member tables. This will happen if you try to delete the Member row before you delete the Adult row. This obviously is what the view is doing. For the delete to work the row has to be deleted from Adult first and then from Member. But again, how is Visual FoxPro supposed to know that? The error messages are incomprehensible If you try to delete a member with juveniles you will be stopped. If you try to delete a member with loans you will be stopped. However, SQL Server will send back to Visual FoxPro an error message with language similar to the one in Figure 3. This is totally incomprehensible to the user. Consider another example. The Adult table has the following Check constraint defined: ALTER TABLE adult WITH NOCHECK ADD CONSTRAINT phone_no_rule CHECK (phone_no LIKE '(206)[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
This constraint forces phone numbers to begin with the 206 area code. If a row is inserted or updated and the phone number violates this rule the data will be rejected. However, the user will be presented with the monstrosity shown in Figure 4.
Figure 4: Check Constraint Violated Error Message
Using Views - Attempt 2 So are views useless in this situation? Should you give up on them? Not without at least one more attempt. The problem above is that the views are made up of two tables and you have no control over how Visual FoxPro tries to insert or update rows. There are specific rules you have to follow based on the Library database schema but you can't make the views behave. As an alternative approach, the form MEMBVEW2.SCX uses three views instead of two. The views vMemberTable, vAdultTable and vJuvenileTable are all based on a single SQL Server table. Loading the Form The form still comes up with no data loaded. All three views are opened with the NoData option and optimistic row buffering is then set for them. The three views are all based on the same shared connection. The ConnectHandle property of any of the views stores the connection handle used by the views. This handle will be used shortly. * What is the connection handle used by the views? ThisForm.nHandle = CursorGetProp("ConnectHandle", ; "vMemberTable")
Locating a Member When the user wants to see a new member's information he or she will still provide a member ID and press Locate. The code in this form differs slightly from the previous version in that the Member view is requeried first. If no record is found then you immediately know the ID is not valid. The Adult view is requeried next. If no record is found then the member must be a juvenile. Saving Changes In the previous version of the form a simple TableUpdate() was issued when the user pressed Save. As you saw, that is insufficient given the table structures. This version of the form takes a more complicated but ultimately much more successful approach. If the user is currently adding a record the first half of Save button's Click event code will be run. If ThisForm.lAddMode = .T. Wait Window "Saving new member information..." NoWait
The code adds the new member to the Member table first. A row will then be added to the Adult table. Both rows should be added or neither should be added. Therefore a transaction has to be started. The Transactions property of a connection is used to control transaction processing. In the form's Load method the view's connection handle was stored to the form property nHandle. SQLSetProp() is then used to start a transaction.
* Begin a transaction = SQLSetProp(ThisForm.nHandle, "Transactions", 2)
The vMemberTable view is then selected and a TableUpdate() is issued. If it works the new member's name information will have been added to the Member table. In addition the member will have a member ID. If the TableUpdate() failed the user should see what happened and the transaction is rolled back using the SQLRollback() function. * Add the new member to the member table Select vMemberTable If Not TableUpdate() ThisForm.ShowError * Rollback the transaction = SQLRollback(ThisForm.nHandle) Return Endif
If the TableUpdate() worked then the new row made it into the Member table. The next step is to find out what member ID was assigned. SQLExec() is used to send a Select statement to SQL Server. This Select retrieves the value of @@Identity, which stores the last Identity value inserted into a table. In this case that will be the member ID. SQLExec() uses the same connection handle the views use. This is efficient because it doesn't require another connection to SQL Server. * Find out the member_no of the new member If SQLExec(ThisForm.nHandle, "Select @@identity")< 0
The member_no in the vAdultTable view is replaced with the value of @@Identity. This enforces the relationship between Member and Adult. Note that this involves changing the primary key of the Adult table. There is no other choice. The expiration date for this member is set to one year from today and TableUpdate() is issued to save this information to the Adult table. If it doesn't work the transaction is rolled back. This would remove the row from the Member table. * Add the new member to the adult table Select vAdultTable * The member_no is the primary key but it is * updatable in the view for just this very reason. * The expiration date is one year from today. Replace member_no With sqlresult.exp, ; expr_date With DToT(GoMonth(Date(),12)) If Not TableUpdate()
If everything worked to this point the transaction is committed with the SQLCommit() function. If that fails then everything is rolled back. * Everything worked so commit the transaction. If SQLCommit(ThisForm.nHandle) < 0 ThisForm.ShowError
* Rollback the transaction = SQLRollback(ThisForm.nHandle) Else
If the user is not adding a new member the code is somewhat simpler. A transaction is still started. The information in the Member table is saved first and then the information in the Adult and Juvenile tables is saved. If everything worked the transaction is committed. If not it is all rolled back. Else Wait Window "Saving member information..." NoWait * Begin a transaction = SQLSetProp(ThisForm.nHandle, "Transactions", 2) * Save the information to the member table Select vMemberTable If Not TableUpdate() * Save the information to the adult table Select vAdultTable If Not TableUpdate() * Everything worked so commit the transaction. If SQLCommit(ThisForm.nHandle) < 0 ThisForm.ShowError * Rollback the transaction = SQLRollback(ThisForm.nHandle) Else = MessageBox("This member's information has been " + ; "saved.", MB_ICONINFORMATION) Endif Endif
Deleting a Member Using one view per table not only allows you to solve the problem of adding a new member, it also allows you to solve the problem of not being able to delete members. When the user presses the Delete button a transaction is started. The member's row from the Adult or Juvenile table is deleted first. The Delete is applied to the view and the TableUpdate() sends it to SQL Server. If the TableUpdate() doesn't work the transaction is rolled back. * Start a transaction on the view connection = SQLSetProp(ThisForm.nHandle, "Transactions", 2) If RecCount("vJuvenileTable") = 0 Select vAdultTable Delete Else Select vJuvenileTable Delete Endif If Not TableUpdate() ThisForm.ShowError * Rollback the transaction
= SQLRollBack(ThisForm.nHandle)
If the corresponding Adult or Juvenile record has already been removed you can remove the Member table record. The Delete may still fail, for instance if this member has outstanding loans. In that case the transaction is rolled back and the Adult or Juvenile record is put back into the relevant table. Select vMemberTable Delete If Not TableUpdate() ThisForm.ShowError * Rollback the transaction = SQLRollBack(ThisForm.nHandle)
If both of the TableUpdate() functions worked then the transaction is committed with the SQLCommit() function. If that doesn't work the transaction is rolled back. Issues Two of the three problems with the first version of the form are solved here. New members can be added and members can be deleted. If referential integrity is violated the deletion will fail. For example, suppose you try to delete a member who has no associated juveniles but does have outstanding loans. You will see the error shown in Figure 5.
Figure 5: Error Message when Member with Loans is Deleted So the problem of the incoherent error messages still remains. You could write code to parse the error messages and look for certain phrases. You could then translate the SQL Server messages into something a user would want to see. This is a lot of work and requires a thorough familiarity with SQL Server error messages. It also makes it quite difficult to port your application to other back-end databases, for example Oracle. Using SQL pass-through The alternative to using views is to rely solely on SQL pass-through. This means that you send SQL statements to the back-end and explicitly tell it what to do. If you want to add a record you send an Insert. To save a record you send an Update. This obviously involves more work than using views. It does however allow you to be in complete control over what happens and when it happens. Loading the Form
The form MEMBEXEC.SCX is the same form used above, only it uses SQL pass-through instead of views. The following code is in the form's Load method. Open Database library ThisForm.nHandle = SQLConnect('cnLibrary') If ThisForm.nHandle < 0 ThisForm.ShowError ThisForm.lConnected = .F. Else lcSQL = "Select member.member_no, lastname, firstname, " + ; " middleinitial, street, city, state, zip, " + ; " phone_no, expr_date, birth_date = null, " + ; " adult_member_no = null " + ; "From member, adult " + ; "Where member.member_no = -99 " If SQLExec(ThisForm.nHandle, lcSQL, "c_member") < 0 ThisForm.ShowError ThisForm.lConnected = .F. Endif = CursorSetProp("Buffering", DB_BUFOPTRECORD, 'c_member') Endif
When the form is loaded SQLConnect() is used to establish a connection to SQL Server using the cnLibrary connection, which is stored in the Visual FoxPro Library database. If the SQLConnect() fails there is not much to do except quit and go home. If the connection works, an empty cursor is created by sending to SQL Server a Select statement looking for member number -99. Visual FoxPro creates the cursor even though no rows are returned. Optimistic buffering is then set on this cursor. The reason for doing this is to be able to use a buffered cursor on this form. This gives back some of the ease of use of views. Locating a Member When views are used on the form, locating a member is as simple as plugging in a value for the view's parameter and requerying. When SQL pass-through is used it is a bit more complex. To retrieve member information a Select statement is constructed and sent to the server. You can see in the code below that this Select is a Union. The member, if he or she exists, is in the Member table and in either the Adult or Juvenile table. lcSQL = "Select member.member_no, lastname, firstname, " + ; " middleinitial, street, city, state, zip, " + ; " phone_no, expr_date, birth_date = null, " + ; " adult_member_no = null " + ; "From member, adult " + ; "Where member.member_no = adult.member_no " + ; " And member.member_no = " + ; " AllTrim(ThisForm.txtMemberID.Value) + " " + ; "Union " + ; "Select member.member_no, lastname, firstname, " + ; " middleinitial, street, city, state, zip, " + ; " phone_no, expr_date, birth_date, " + ; " adult_member_no " + ;
If the c_member cursor is empty there is no member with the entered ID. Otherwise all the member information is in the cursor. Row buffering is set for the cursor and the form controls are then populated with the member information in the cursor. The Union allows you to send one Select to the server and get all the information for the member. In the previous examples either two or three views were requeried whereas here one SQLExec() is all that is required. Note that you cannot create a remote view with a Union if you use the View Designer. You could however create the view by hand using the Create SQL View command. Adding an Adult When the user presses the Add button a blank record is added to the c_member cursor. This is no different than in the previous view examples. Select c_member = TableRevert() Append Blank
Solely for reasons of readability, the code to add a new member is located in the Form method AddMember. Because adding a member involves adding rows to two tables a transaction is started. Just as in the forms with views, the SQLSetProp() function is used to start the transaction. = SQLSetProp(ThisForm.nHandle, "Transactions", 2)
When using views to access remote data you can rely on Visual FoxPro to do most of the behind the scenes work for you. For example, in the previous forms you saw that to send an Insert or Update to the server all you had to do was issue a TableUpdate(). The syntax of the Insert or Update was created for you by Visual FoxPro. The form here uses the SQLExec() function to send SQL statements to the server. That means that you have to construct the SQL statements yourself. After the transaction is started an Insert statement is constructed to add the new row to the Member table. * Add the new member to the member table lcSQL = "Insert member (lastname, firstname, " + ; "middleinitial, photograph) " + ; "Values ('" + ; AllTrim(ThisForm.txtFirstName.Value) + ; "', '" + ;
You now need to know the member_no SQL Server assigned the new member. The code to do this is the same as in the previous form. * Find out the member_no of the new member If SQLExec(ThisForm.nHandle, "Select @@identity") < 0 nNewMemberID = sqlresult.exp
An Insert is then constructed to add the new row to the Adult table. The @@Identity value from the server is used in this Insert to correctly link the Adult row to the Member row. * Add the new member to the adult table lcSQL = "Insert adult (member_no, street, city, state, " + ; "zip, phone_no, expr_date) " + ; "Values (" + AllTrim(Str(nNewMemberID)) + ", '" +; AllTrim(ThisForm.txtStreet.Value) + ; "', '" + ; AllTrim(ThisForm.txtCity.Value) + ; "', '" + ; AllTrim(ThisForm.txtState.Value) + ; "', '" + ; AllTrim(ThisForm.txtZip.Value) + ; "', '" + ; AllTrim(ThisForm.txtPhoneNumber.Value) + ; "', "'" + ; TToC(DToT(GoMonth(Date(),12))) + "' )" If SQLExec(ThisForm.nHandle, lcSQL) < 0
As before, if everything worked the transaction is committed. If not it is all rolled back. Saving Changes The code to save an existing member's information is in the Form method UpdateMember. To save information an Update statement is sent to the server. The form of the Update statement is as follows: Update
Set = , = , etc
It is pretty straightforward, although a bit bulky, to construct an Update statement to send to the server. You know the names of the table's columns and the values are sitting in controls on the form. You can just build the Set part of the Update one column after another. However, you want to be a bit more clever than that. You don't want to waste SQL Server's time updating a column that has not changed. The code here uses the OldVal() function, made possible by the use of the buffered cursor, to check the value of each field in the cursor to its original value. Only if it has changed does it become part of the Update statement. By the way, remote views do this automatically. lcSQL = "" * Update this member in the member table If c_member.firstname <> OldVal("c_member.firstname") lcSQL = lcSQL + "firstname = '" + ; AllTrim(ThisForm.txtFirstName.Value) + "', " Endif If c_member.lastname <> OldVal("c_member.lastname") lcSQL = lcSQL + "lastname = '" +; AllTrim(ThisForm.txtLastName.Value) + "', " Endif
If none of the columns in the Member table have changed, the lcSQL variable will be empty and there is nothing to save for that table. If there is data to save, the rest of the Update statement is constructed and is then sent to the server. If Len(lcSQL) > 0 * Add the Update, strip off the last comma, * add a Where clause lcSQL = "Update member Set " + ; Left(lcSQL, Len(lcSQL) - 2) + ; "Where member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) If SQLExec(ThisForm.nHandle, lcSQL) < 0
The same process as above then occurs for the Adult table. The next part should by now be very familiar. Commit if it all worked and rollback if it didn't. Deleting a Member One of the benefits of using SQL pass-through rather than remote views is that you gain control over what happens and when it happens. The code run when the user clicks the Delete button is a good example of this. There are various reasons you might not be able to delete a member. If the member has associated juveniles or if the member has outstanding loans any Delete will fail. You can easily determine if either of these is true by sending a Select statement to the server. The code here uses SQLExec() to check for both of these conditions. If either is true a user-friendly message is displayed and nothing further happens.
* First check to see if this is an adult with * active juveniles lcSQL = "Select member_no From juvenile " + ; "Where adult_member_no = " + ; ThisForm.txtMemberID.Value If SQLExec(ThisForm.nHandle, lcSQL) < 0 ThisForm.ShowError Return Else If RecCount("sqlresult") <> 0 lcMessage = "This member can not be deleted. " + ; "He/she is an adult with active juveniles." = MessageBox(lcMessage, MB_ICONINFORMATION) Return Endif Endif * Now check to see if this member has active loans lcSQL = "Select member_no From loan " + ; "Where member_no = " + ; ThisForm.txtMemberID.Value If SQLExec(ThisForm.nHandle, lcSQL) < 0 ThisForm.ShowError Return Else If RecCount("sqlresult") <> 0 lcMessage = "This member can not be deleted. " + ; "He/she has active loans." = MessageBox(lcMessage, MB_ICONINFORMATION) Return Endif Endif
If there are additional checks that need to be performed that code can be placed after the code above. You have complete control over what you check and what order the checks are run. If all the checks succeed and the member can be deleted a transaction is started. There are relationships defined between the Member table and the Loanhist and Reservation tables. Loanhist contains a row for every book borrowed and returned by the member. The Reservation table contains a row for every book the member has on reserve. If the member is deleted the associated information in those two tables needs to be removed. It needs to deleted first or else there will be a referential integrity violation. * Delete the loan history records for this member lcSQL = "Delete loanhist Where member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) If SQLExec(ThisForm.nHandle, lcSQL) < 0 * Delete the loan reservation records for this member lcSQL = "Delete reservation Where member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) If SQLExec(ThisForm.nHandle, lcSQL) < 0
To delete an adult member you have to delete the row in the Adult table first and then you can delete the row in the Member table. This is still part of the transaction so if anything goes wrong everything is rolled back. * Delete the member lcSQL = "Delete adult Where member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) If SQLExec(ThisForm.nHandle, lcSQL) < 0 lcSQL = "Delete member Where member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) If SQLExec(ThisForm.nHandle, lcSQL) < 0
If all of the Deletes worked then the entire transaction is committed and the member is removed. The user should then be looking at an empty screen so a blank record is added to c_member and the form is refreshed. Issues What are the tradeoffs between using remote views and using SQL pass-through? This is more work Clearly, using SQL pass-through is more work than using remote views. Views do a lot of the work for you, taking care of communicating with the server and passing Inserts, Updates and Deletes. Views are easy to set up and easy to use. You have more control You saw in the two view examples that one of the problems with using remote views is that you have little control over the communication between Visual FoxPro and the back-end. With many database schemas this might not be a problem. However, with the schema used here an issue arose. The problem was mitigated by using one view per table but the point remains. There may be times when views do not give you the power you need for data entry. When you use SQL pass-through you have complete control over how Visual FoxPro talks to the back-end. You construct the SQL statements and then use SQLExec() to send them to the server. If validations need to be performed or business rules need to checked you decide when and how they occur. Error messages can be made user-friendly Because you are in control of what happens and you are validating by hand you have control over the error messages. You will essentially intercept the SQL Server errors and give the users an understandable message when something predictable happens. You still have the issue of a SQLExec() failing due to unpredictable occurrences, such as a network failure. For those you can decide if you want to parse the messages or display them in their raw form.
On the one hand this provides less interoperability A downside to this approach is that it sacrifices interoperability to a certain degree. The SQL statements sent to the back-end via the SQLExec() are written in the back-end's language. The examples here were designed to work with SQL Server. How much of a rewrite would be involved to port this application to Oracle? It is true that the basic form of a Select, Insert, Update or Delete doesn't vary much from backend to back-end. So the example here might be easily ported. However, the point is an important one and depending on the complexity of the SQL statements you use you might limit your ability to swap back-ends. Of course, if the application is written for one and only one back-end this will not be a big issue. On the other hand this provides more interoperability Consider what happens if you are using remote views and you try to delete a member who has outstanding loans. Referential integrity defined on the server will prevent the deletion. However, the error message sent back by Oracle will differ from the message sent back by SQL Server. You could parse the messages and translate them into a user-friendly form but you will have to parse differently for each back-end. This limits your interoperability because you will have to create parsing routines for each server. With the SQL pass-through approach you would send a Select statement to the back-end looking for this member in the Loan table. If the Select finds a row the member can't be deleted. The message shown to the user is the same regardless of what was sent in the SQLExec(). This serves to increase your interoperability, assuming that the form of the Selects, Inserts, Updates and Deletes doesn't vary greatly among the various back-ends, which is a reasonable assumption. Using Stored Procedures In the approach just reviewed, the work is split between the client and the server. The server does the actual data manipulation, which involves searching for records and adding, updating and deleting records. However, the client controls the logic, telling the server what to do and what order to do it in. An alternative approach is to let the server not only do the work but also control the logic. This is accomplished by relying on stored procedures. A stored procedure is a precompiled set of SQL statements. Because they are precompiled there is a performance gain to using them. In addition, they are essentially black box routines. The SQLExec() function can be used to send not only SQL statements such as Insert and Update, but also can be used to tell SQL Server to run a stored procedure. Visual FoxPro tells the server what stored procedure to run but it doesn't know what is in the various procedures. Visual FoxPro only knows, and in fact only needs to know, whether or not the stored procedures worked.
Loading the Form The form MEMBPROC.SCX uses stored procedures to talk to SQL Server. SQLExec() is still used so a connection to the server needs to be established in the Load method of the form. This also occurred in the previous example and in fact the Load method of both forms is exactly the same. Locating a Member A SQL Server stored procedure called GetMember is used to retrieve information on a member. This stored procedure takes as a parameter the ID of the member whose information you want. This stored procedure is called when the user presses the Locate button. If SQLExec(ThisForm.nHandle, "Execute GetMember " + ; ThisForm.txtMemberID.Value, "c_member") < 0 ThisForm.ShowError Return Endif
The stored procedure runs a Select and therefore returns a result set to the cursor c_member. If the cursor is empty then no member with that ID was found. Otherwise the member's information is in the fields of the cursor and can then be displayed on the form. The SQL Server code that created the GetMember stored procedure is as follows. You can see that it takes one parameter and then runs a Union Select to find the member's information. CREATE PROCEDURE getmember @member_no member_no AS SELECT member.member_no, firstname, lastname, middleinitial, street, city, state, zip, phone_no, expr_date, birth_date = NULL, adult_member_no = NULL FROM member, adult WHERE member.member_no = adult.member_no AND member.member_no = @member_no UNION SELECT member.member_no, firstname, lastname, middleinitial, street, city, state, zip, phone_no, expr_date, birth_date, adult_member_no FROM member, adult, juvenile WHERE member.member_no = juvenile.member_no AND juvenile.adult_member_no = adult.member_no AND member.member_no = @member_no RETURN (@@ROWCOUNT)
Adding an Adult The code to add an adult member is in the form method AddMember. The code calls the stored procedure AddAdult, which takes as parameters the name, address and phone information for the new member.
lcSQL = "Execute addadult " '" + ; AllTrim(ThisForm.txtLastName.Value) + ; "', '" + ; AllTrim(ThisForm.txtFirstName.Value) + ; "', '" + ; AllTrim(ThisForm.txtMiddleInitial.Value) + "', " + ; "'" + AllTrim(ThisForm.txtStreet.Value) + "', " + ; "'" + AllTrim(ThisForm.txtCity.Value) + "', " + ; "'" + AllTrim(ThisForm.txtState.Value) + "', " + ; "'" + AllTrim(ThisForm.txtZip.Value) + "', " + ; "'" + AllTrim(ThisForm.txtPhoneNumber.Value) + "'" If SQLExec(ThisForm.nHandle, lcSQL) < 0 ThisForm.ShowError Else = MessageBox("This member has been added.", ; MB_ICONINFORMATION) * Find out the member_no of the new member ThisForm.txtMemberID.Value = AllTrim(Str(sqlresult.exp1))
The SQL Server code used to create the AddAdult stored procedure is as follows: CREATE PROCEDURE addadult @lastname shortstring, @firstname shortstring, @middleinitial letter = NULL, @street shortstring = NULL, @city shortstring = NULL, @state statecode = NULL, @zip zipcode = NULL, @phone_no phonenumber = NULL AS DECLARE @member_no member_no IF @lastname = NULL OR @firstname = NULL OR @street = NULL OR @zip = NULL BEGIN PRINT " You must provide Last name, First name," PRINT " Street, City, State, and Zip." RETURN END BEGIN TRANSACTION INSERT member (lastname, firstname, middleinitial, photograph) VALUES (@lastname, @firstname, @middleinitial, NULL ) IF @@error != 0 BEGIN ROLLBACK TRAN RAISERROR('The new member was not added. Feel free to try again.',16,1) RETURN END SELECT @member_no = @@IDENTITY INSERT adult ( member_no, street, city, state, zip, phone_no, expr_date) VALUES (@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
IF @@error != 0 BEGIN ROLLBACK TRAN RAISERROR('The new member was not added. Feel free to try again.',16,1) RETURN END PRINT " *** Adult Member added ***" SELECT "This member is number ", @member_no COMMIT TRANSACTION
This stored procedure takes the name, address and phone number information as parameters. If the name or address fields are not supplied then the stored procedure won't continue. They are defined in the Member and Adult tables as Not Null. The stored procedure begins a transaction and adds a new row to the Member table. It then issues a Select to store the value in @@Identity to the @member_no variable. That value is then used when the new row is added to the Adult table. If anything goes wrong the transaction is rolled back and an error number and message is sent back. This is needed so that Visual FoxPro knows the stored procedure failed. This causes the SQLExec() function to return a -1, which is how you know it didn't work. It turns out that the Select used to get the value of @@Identity creates a result set and that this is sent back to Visual FoxPro. This is a happy coincidence as this causes the SQLExec() to create a result set cursor called sqlresult. The value in the exp field in that cursor is the value of @@Identity and is easily retrieved. This is good for another reason. If you waited until the stored procedure had run and then used SQLExec() to ask SQL Server for the value of @@Identity it would come back empty. This is because the second thing the stored procedure does is insert a row into the Adult table, which doesn't have an Identity column. This causes the value of @@Identity to be set to Null. So if the stored procedure didn't send back the @@Identity after the Insert into Member then the information would have been lost. Saving Changes The code to save a member's information is in the form method UpdateMember. The code calls the stored procedure UpdateAdult, which takes as parameters the member ID and the name and new value of any fields that need to be updated. lcSQL = "" If c_member.firstname <> OldVal("c_member.firstname") lcSQL = lcSQL + " @firstname = '" + ; AllTrim(ThisForm.txtFirstName.Value) + "', " Endif If c_member.lastname <> OldVal("c_member.lastname") lcSQL = lcSQL + " @lastname = '" + ; AllTrim(ThisForm.txtLastName.Value) + "', " Endif
If Len(lcSQL) > 0 * Strip off the last comma lcSQL = Left(lcSQL, Len(lcSQL) - 2) lcSQL = "Execute updateadult @member_no = " + ; AllTrim(ThisForm.txtMemberID.Value) + ; ", " + lcSQL Else = MessageBox("There is nothing to save.", ; MB_ICONINFORMATION) Return Endif If SQLExec(ThisForm.nHandle, lcSQL) < 0 ThisForm.ShowError
The SQL Server code used to create the UpdateAdult stored procedure is as follows: CREATE PROCEDURE updateadult @member_no member_no, @lastname shortstring = NULL, @firstname shortstring = NULL, @middleinitial letter = NULL, @street shortstring = NULL, @city shortstring = NULL, @state statecode = NULL, @zip zipcode = NULL, @phone_no phonenumber = NULL AS DECLARE @sqlstring1 varchar(255) DECLARE @sqlstring2 varchar(255) IF @lastname = NULL AND @firstname = NULL AND @middleinitial = NULL AND @street = NULL AND @city = NULL AND @state = NULL AND @zip = NULL AND @phone_no = NULL BEGIN PRINT "Nothing to do." RETURN END SELECT @sqlstring1 = NULL SELECT @sqlstring2 = NULL IF @lastname != NULL SELECT @sqlstring1 = @sqlstring1 + "lastname = '" + @lastname + "'," IF @firstname != NULL SELECT @sqlstring1 = @sqlstring1 + "firstname = '" + @firstname + "'," BEGIN TRANSACTION IF @sqlstring1 != NULL BEGIN SELECT @sqlstring1 = "UPDATE member SET " + SUBSTRING(@sqlstring1, 1, DATALENGTH(@sqlstring1) - 1) + " WHERE member_no = " + CONVERT(char(6), @member_no) EXECUTE (@sqlstring1)
IF @@error != 0 BEGIN ROLLBACK TRAN RAISERROR('The member information was not saved. Feel free to try again.',16,1) RETURN END END IF @sqlstring2 != NULL BEGIN SELECT @sqlstring2 = "UPDATE adult SET " + SUBSTRING(@sqlstring2, 1, DATALENGTH(@sqlstring2) - 1) + " WHERE member_no = " + CONVERT(char(6), @member_no) EXECUTE (@sqlstring2) IF @@error != 0 BEGIN ROLLBACK TRAN RAISERROR('The member information was not saved. Feel free to try again.',16,1) RETURN END END COMMIT TRANSACTION
The member ID is a required parameter for this stored procedure. It couldn't construct an Update statement without some way of identifying the member's record. The other parameters are optional and will default to Null if they are not passed. If they are all Null then there is nothing to do. Note that the Visual FoxPro code will not call the stored procedure if nothing has changed. The stored procedure then constructs two Update statements, one for the Member table and one for the Adult table. For efficiency, only the columns that have changed are included. The Update statements are stored to the variables @sqlstring1 and @sqlstring2. The Execute statement parses the strings and then executes that code. This capability of the Execute statement is a very popular feature that first appeared in SQL Server 6.0. Deleting a Member When the user presses the Delete button SQLExec() is used to tell SQL Server to run the stored procedure RemoveMember, which takes the member's ID as a parameter. If SQLExec(ThisForm.nHandle, "Execute RemoveMember " + ; ThisForm.txtMemberID.Value) < 0 ThisForm.ShowError Else
The SQL Server code to create the RemoveMember stored procedure is as follows: CREATE PROCEDURE removemember @member_no member_no
AS
IF NOT EXISTS (SELECT * FROM member WHERE member_no = @member_no) BEGIN PRINT " Member number not found in Member table." RETURN END IF EXISTS (SELECT member_no FROM juvenile WHERE @member_no = adult_member_no) BEGIN RAISERROR('This member can not be deleted. He/she is an adult with active juveniles.',16,1) RETURN END IF EXISTS (SELECT member_no FROM loan WHERE member_no = @member_no) BEGIN RAISERROR("This member can not be deleted. He/she has active loans.",16,1) RETURN END BEGIN TRANSACTION IF EXISTS (SELECT member_no FROM loanhist WHERE member_no = @member_no) BEGIN PRINT 'Deleting Loan History information' DELETE loanhist WHERE member_no = @member_no IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END END IF EXISTS (SELECT member_no FROM reservation WHERE member_no = @member_no) BEGIN PRINT 'Deleting Loan Reservation information' DELETE reservation WHERE member_no = @member_no IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END END IF EXISTS (SELECT member_no FROM juvenile WHERE member_no = @member_no) BEGIN DELETE juvenile WHERE member_no = @member_no IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END END ELSE IF EXISTS (SELECT member_no FROM adult WHERE member_no = @member_no) BEGIN
DELETE adult WHERE member_no = @member_no IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END END DELETE member WHERE member_no = @member_no IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION
This stored procedure first checks to see if the member exists. It then checks to see if this is an adult with active juveniles or a member with outstanding loans. If so the member can not be deleted and the stored procedure sends back an error and user friendly message. If the deletion can occur a transaction is begun and the member's information is removed from the Loanhist, Reservation, Juvenile, Adult and Member tables. As always, if anything goes wrong the whole thing is rolled back. Issues How does SQL pass-through with hand crafted SQL statements compare to SQL pass-through with stored procedures? This is less work Assuming the stored procedures already exist there is much less work for the Visual FoxPro programmer to do in this scenario. All you need is to know is which stored procedure to call, what parameters it takes and what it returns. You do not need to know how the stored procedure works or in what order it does things. You have less control in Visual FoxPro Because all of your access to SQL Server occurs through stored procedures you have very little control from Visual FoxPro over what happens on the server. Of course, assuming that the stored procedures do what they are supposed to do, you probably won't mind this. You have more control in SQL Server If you are responsible not only for the Visual FoxPro front-end but also the SQL Server back-end then you have control either way. However, what if you are just writing a Visual FoxPro frontend to SQL Server data and that data is controlled by someone else? Providing access to the data solely through stored procedures is a great way for the owners of the SQL Server data to exercise control over that data. They can rest assured that the front-end developers don't mess up the data. This would come in extremely handy if there is a Visual FoxPro, a Visual Basic and an Access front-end to the same data.
This provides more interoperability Because all the work is done in the stored procedures it should be very easy to switch back-ends. At the worst you would have to change each line of code containing a SQLExec() to reflect a different syntax for calling stored procedures. Conclusion In this session you have seen examples of using both views and SQL pass-through for building client-server applications using Visual FoxPro. The obvious question is when should you use one or the other? Using remote views has the benefit of being relatively simple because Visual FoxPro does a lot of the work for you. You can use standard Visual FoxPro functions such as TableUpdate() for data entry. FoxPro handles the communications with the back-end server. A downside to using views is that you give up a large degree of control. A particularly good use of views is for reporting. The View Designer is a very easy way to construct many Select statements. It is also a great way to construct heterogenous queries, where some of the data is coming from the back-end server and some is coming from local Visual FoxPro tables, for instance lookup tables. Using SQL pass-through and sending SQL statements directly to the back-end gives you total control. You create the SQL statements that get run. You can still use buffered cursors and get much of the convenience of views. The cost however is the work involved in constructing the SQL statements. Calling stored procedures on the back-end via SQL pass-through greatly reduces the burden of writing SQL statements but also takes away your control of what happens and when. The purpose of this session is not to convince you to use either views or SQL pass-through. Rather, you should consider both of them to be powerful tools you can use to build applications. If your data structures and validation requirements are not complex then you should consider using views. If you need more control or views can't handle something you want to do you should consider SQL pass-through. You can also use both whenever that makes sense.