EM412 Using Adaptive Server Anywhere with Visual Basic

1. EM412 Using Adaptive Server Anywhere with Visual Basic • Ali Chalhoub • Technical Support Consultant • iAnywhere Solutions • achalhou@sybase.com

2. Visual Basic and its Three Data Interfaces. ADO Programming Using Adaptive Server Anywhere OLE DB Provider. Working with Blobs. Authenticate a Visual Basic Application. Introduction to Windows CE. Summary. Using Adaptive Server Anywhere with Visual Basic

3. Visual Basic and its Three Data Interfaces • Visual Basic has three methods to establish a connection with a database. These methods are: • DAO • RDO • ADO

4. Visual Basic and its Three Data Interfaces • DAO (Data Access Object) • DAO was the first data access technology introduced in Visual Basic. It is used to access desktop databases or remote databases. • RDO (Remote Data Object) • RDO is used to access remote databases. It is designed to work with client/server databases such as Adaptive Server Anywhere.

5. Visual Basic and its Three Data Interfaces • ADO (ActiveX Data Object) • ADO is an application programming interface to OLE DB. OLE DB is a low-level interface to all types of data. That’s why it is called Universal Data Access (UDA). • OLE DB is a data access model from Microsoft. It uses • Component Object Model (COM) interfaces that • provide applications with uniform access to data stored • in diverse information sources.

6. Visual Basic and its Three Data Interfaces • Why use ADO? • What is ADO Control? • How to Bind Data-Bound Control to an ADO Data Control?

7. Why Use ADO? • Easy-to-use interface for data access. • Current technology • Low memory overhead, small footprint, high speed and ease of use.

8. Connection Flow VB Application DAO ADO ODBC Direct Jet RDO MS OLE DB Provider ASA OLE DB Provider ODBC ASA

9. ADO Data Control • The ADO data control uses Microsoft ADO to quickly create connections between data-bound controls and data providers. • Data-bound controls are any controls that feature a DataSource property. • TextBox • Labels • PictureBox • ListBox

10. Using ADO Data Control • To add the ADO Data • control to the toolbox • Select Components from the Project menu.

11. Using ADO Data Control • Select Microsoft ADO Data Control 6.0 (OLE DB).

12. Setting up an ADO Data Control • Once the ADO Data control is • added to the toolbox, the following • should be done. • Add the ADO control to your form. • Set the ConnectionString property. ADO Data Control ADO Data Control Added to the Form

13. Configuring an ADO Data Control • To establish a connection from an ADO Data Control to • Adaptive Server Anywhere database at design time, the • following should be done: • Right click the ADO control and select ADODC Properties. • Three data source options are available to use. • Use Data Link File. • Use ODBC Data Source Name. • Use Connection String.

14. Configuring an ADO Data Control • Click on the dropdown list for “Use ODBC Data Source Name”. • Select ASA 7.0 Sample.

15. Connection Flow VB Application ADO MS OLE DB Provider ASA OLE DB Provider ODBC ASA

16. Configuring an ADO Data Control • Click on the RecordSource tab. • Select adCmdTable. ASA Engine

17. Configuring an ADO Data Control • Select customer table from the “Table or Stored Procedure Name” option. • Click on Apply. • Click on OK.

18. Binding a TextBox Control • Once the ADO properties are set correctly, you can • bind a data bound control to show data. • Add a textbox control to the form where the ADO control was added and set the following properties: • Set DataSource to “Adodc1”. • Set DataField to “fname”.

19. Binding a TextBox Control 1) DataSource 2) DataField

20. Displaying Data Once all the controls are bounded, you should be able to navigate through.

21. Visual Basic and its Three Data Interfaces. ADO Programming Using Adaptive ServerAnywhere OLE DB Provider. Working with Blobs. Authenticate a Visual Basic Application. Introduction to Windows CE. Summary. Where Are We?

22. Adaptive Server Anywhere OLE Provider • Introduction to ASA OLE DB Provider. • Advantage of using ASA OLE DB Provider. • Supported Platforms. • Registry Entry. • ADO Programming with Adaptive Server Anywhere.

23. Introduction to ASA OLE DB Provider Adaptive Server Anywhere includes an OLE DB provider named ASAProv. This provider is available for windows and windows CE. Adaptive Server Anywhere can also be accessed through Microsoft’s OLE DB provider, MSDASQL, to be used with the Adaptive Server Anywhere ODBC Driver.

24. Adaptive Server Anywhere OLE Provider • Introduction to ASA OLE DB Provider. • Advantage of using ASA OLE DB Provider. • Supported Platforms. • Registry Entry. • ADO Programming with Adaptive Server Anywhere.

25. Advantage of Using ASA OLE DB Provider • Why using Adaptive Server Anywhere is better than • Using Microsoft’s generic OLE DB provider? • No need to use ODBC in your deployment if ASA OLE DB provider is used. • Bookmark is supported when Dynamic cursor is used. • Recordcount is supported when Dynamic cursor is used.

26. Adaptive Server Anywhere OLE Provider • Introduction to ASA OLE DB Provider. • Advantage of using ASA OLE DB Provider. • Supported Platforms. • Registry Entry. • ADO Programming with Adaptive Server Anywhere.

27. Supported Platform • The ASA OLE DB provider works with ADOCE 3.0 and later. • ADOCE 3.0 is included in the new Windows CE 3.0 devices, such as PocketPC.

28. Adaptive Server Anywhere OLE Provider • Introduction to ASA OLE DB Provider. • Advantage of using ASA OLE DB Provider. • Supported Platforms. • Registry Entry. • ADO Programming with Adaptive Server Anywhere.

29. Registry Entry • When the ASAProv provider is installed, it registers itself. The ASAProv is registered under the following key: HKEY_CLASSES_ROOT\ASAProv • If you change the location of your DLL, you must reregister it

30. Registry Entry • To register the OLE DB provider: • Open a command prompt ( DOS prompt) • Change to the directory where the OLE DB provider is installed. (Default C:\Program Files\Sybase\SQL Anywhere 7\win32) • Enter the following command to register the provider: • Regsvr32 dboledb7.dll

31. Adaptive Server Anywhere OLE Provider • Introduction to ASA OLE DB Provider. • Advantage of using ASA OLE DB Provider. • Supported Platforms. • Registry Entry. • ADO Programming with Adaptive Server Anywhere.

32. ADO Programming with Adaptive Server Anywhere • Connecting to a database using the connection object. • Executing statements with the command object. • Working with stored procedures. • Working with Recordset object.

33. Connecting to a Database Using the Connection Object In order to connect to Adaptive Server Anywhere you must have at least the user id, password and provider. Check the following table:

34. Connecting to a Database with the Connection Object • Before the connection can be • established, a reference to the • ADO library must be set. To set the • reference to the library. • The following should be done: • Select References from the Project Menu. • Set a reference to the Microsoft ActiveX Data Object 2.x Library (i.e ActiveX Data Object 2.6 ).

35. Option Explicit Dim adoConn asnew ADODB.connection PrivateSub cmdConnect_Click() adoConn.Provider = “ASAProv” adoConn.ConnectionString= “uid=dba;pwd=sql;dbf=“ & app.path & “\asademo.db” adoConn.Open End Sub Connecting to a Database with the Connection Object

36. ADO Programming with Adaptive Server Anywhere • Connecting to a database using the connection object. • Executing statements with the command object. • Working with stored procedures. • Working with Recordset objects.

37. Option Explicit Dim adoConn asnew ADODB.connection PrivateSub cmdConnect_Click() Dim adoCmd As New ADODB.Command Set adoCmd.ActiveConnection = adoConn adoCmd.CommandText = "INSERT INTO department VALUES(600,'Accounting',1090)" adoCmd.CommandType = adCmdText adoCmd.Execute End Sub Executing Statements with the Command Object

38. Prepared Statement • Improved performance • The first time the SQL statement is executed • it is parsed and the parsed tree is saved in the engine. • At this point, the optimizer generates the execution plan. • Correctness of all referenced database objects is verified • On subsequent calls, this “ground work” does not have to be repeated

39. Command Object with Prepared Statement Dim adoCmd As New ADODB.Command Set adoCmd.ActiveConnection = adoConn adoCmd.CommandText = "UPDATE employee set emp_fname=?, emp_lname=? WHERE emp_id=?" adoCmd.CommandType = adCmdText adoCmd.Prepared = True Dim prmEmpFName As New ADODB.Parameter Set prmEmpFName = adoCmd.CreateParameter("EmpFName", adChar, adParamInput, 20,txtFirstName.text) adoCmd.Parameters.Append prmEmpFName Dim prmEmpLName As New ADODB.Parameter Set prmEmpLName = adoCmd.CreateParameter("EmpLName", adChar, adParamInput, 20,txtLastName.text) adoCmd.Parameters.Append prmEmpLName Dim prmEmpID As New ADODB.Parameter Set prmEmpID = adoCmd.CreateParameter("EmpID", adInteger, adParamInput, ,val(txtEmpID.text) ) adoCmd.Parameters.Append prmEmpID

40. Private Sub cmdUpdate_Click() adoCmd("EmpFName") = txtFirstName.Text adoCmd("EmpLName") = txtLastName.Text adoCmd("EmpID") = Val(txtEmpID.Text) adoCmd.Execute MsgBox "Successfully Updated!" End Sub Command Object with Prepared Statement

41. ADO Programming with Adaptive Server Anywhere • Connecting to a database using the connection object. • Executing statements with the command object. • Working with stored procedures. • Working with Recordset objects.

42. Working with Stored Procedures • What is a stored procedure? • Stored procedure is procedure kept in the database itself which can be called from client application. • Stored procedure provides a way of providing uniform access to functions automatically, as the procedure is held in the database, not in each client application.

43. CREATE PROCEDURE sp_retrieve_contacts() RESULT(id integer,last_name char(15),first_name char(15),title char(2),street char(30),city char(20),state char(2),zip char(5),phone char(10),fax char(10)) BEGIN SELECT id,last_name,first_name,title,street,city,state,zip,phone,fax FROM contact ORDER BY contact.id asc; END Working with Stored Procedures

44. Private Sub cmdExecuteProcedure_Click() Dim adoRS As New ADODB.Recordset adoRS.Open "sp_retrieve_contacts", adoConn, adOpenStatic, adLockReadOnly, adCmdStoredProc adoRS.MoveFirst Set grdData.DataSource = adoRS grdData.Refresh End Sub Working with Stored Procedures

45. Error Handling Private Sub ErrorHandler() Dim adoErr as ADODB.Error For Each adoErr In adoConn.Errors strErr = strErr & " Description : " & adoErr.Description & vbCrLf & vbCrLf & _ " SQL CODE : " & adoErr.NativeError & vbCrLf & vbCrLf & _ " SQL STATE : " & adoErr.SQLState & vbCrLf Next MsgBox strErr, vbCritical + vbOKOnly, "Error Connecting" End Sub

46. ADO Programming with Adaptive Server Anywhere • Connecting to a database using the connection object. • Executing statements with the command object. • Working with stored procedures. • Working with Recordset objects.

47. Working with Recordset Object • What is a Recordset? • ADO Recordset is a set of rows. • ADO Recordset allows you to set the CursorType property, before you open it. • Check the ASA User’s Guide for “Types of Cursor”

48. Working with Recordset Object Recordset Open method syntax: Recordset-object.Open source, ActiveConection, CursorType, LockType,Options Source It could be in-line SQL, stored procedure or table name. ActiveConnection It tells the open method how to access the database. CursorType It tells the database what cursor to use when opening the Recordest.

49. Working with Recordset Object

50. Cursor Types • SCROLL cursors remember both rows and row positions within a cursor, so your application can be assured that these positions remain unchanged. • DYNAMIC SCROLL cursors are more efficient than SCROLL cursors because they store less information. Therefore, use DYNAMIC SCROLL cursors unless you require the consistent behavior of SCROLL cursors. • INSENSITVE cursors has its membership fixed when it is opened; and a temporary table is created with a copy of all the original rows. Fetching from an INSENSITIVE cursor does not see the effect of any other operation from a different cursor. • No Scroll cursors restricts fetching operations to fetching the next row or the same row again. • Check session AM33 regarding cursors.