M415 Using Adaptive Server Anywhere and UltraLite with Visual Basic

1. M415Using Adaptive Server Anywhere and UltraLite with Visual Basic • Ali Chalhoub • Technical Consultant Specialist • iAnywhere Solutions • achalhou@sybase.com

2. Using Adaptive Server Anywhere with Visual Basic • ADO Programming Using Adaptive Server Anywhere OLE DB Provider. • Working with Blobs. • Introduction to ASA on Windows CE. • Introduction to UltraLite on Windows CE. • Summary

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

4. Visual Basic and its Data Interface • 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 ( Component Object Model ) • 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.

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

6. 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.

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

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

9. 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. • Performance.

10. 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.

11. Supported Platform • The ASA OLE DB provider is designed to support the following: • ADO 2.X • ADO.NET (Check the following M410 & M402 ) • 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.

12. 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.

13. 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

14. 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 8\win32) • Enter the following command to register the provider: • Regsvr32 dboledb8.dll

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

16. 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.

17. 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 for more information.

18. 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 ).

19. Connecting to a Database with the Connection Object 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

20. 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.

21. Executing Statements with the Command Object 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

22. 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

23. 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

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

25. 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.

26. 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.

27. Working with Stored Procedures 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 SELECTid,last_name,first_name,title,street,city,state,zip,phone,fax FROM contact ORDERBY contact.id asc; END

28. Working with Stored Procedures 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

29. 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

30. 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.

31. 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”

32. 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.

33. Working with Recordset Object

34. Working with Recordset Object LockType It specifies the type of locking the database should use on the record set when editing of records occurs. Options It tells the database what type the source is. (I.e adCmdTable, adCmdText,etc..)

35. Working with Recordset Object Locking types are: • Optimistic Locking: The lock is done on row by row basis, when Update is called. • Batch Optimistic Locking: The lock occurs when UpdateBatch is called. • Pessimistic Locking: The lock is done on the edited records at the data source. • Read Only Locking: There is no Locking and the data can’t be changed.

36. Working with Recordset Object PrivateSub cmdOpenRecordSet_Click() On Error GoTo cmdOpenDoubleRecordSet_Err Call GetEmployeesAndDept Call LoadEmployeeName Set adoRS = adoRS.NextRecordset Call LoadDept Call CloseRecordSet Exit Sub cmdOpenDoubleRecordSet_Err: Call ErrorHandler End Sub

37. Working with Recordset Object CREATE PROCEDURE DBA.GetEmployeesAndDept() BEGIN SELECT emp_fname FROM employee; SELECT dept_name FROM department; END

38. Working with Double Recordset PrivateSub GetEmployeesAndDepat() On Error GoTo getEmpAndDept_err Set adoRS = New ADODB.Recordset adoRS.Open "call GetEmployeesAndDept()", adoConn, adOpenStatic, adLockReadOnly, adCmdText Exit Sub getEmpAndDept_err: Call ErrorHandler End Sub Private Sub LoadEmployeeName() lstEmployee.Clear Do While Not adoRS.EOF lstEmployee.AddItem adoRS!emp_fname adoRS.MoveNext Loop End Sub Private Sub LoadDept() lstDept.Clear Do While Not adoRS.EOF lstDept.AddItem adoRS!Dept_name adoRS.MoveNext Loop End Sub

39. Where Are We? • Visual Basic and its Data Interface. • ADO Programming Using Adaptive Server Anywhere OLE DB Provider. • Working with BLOBS. • Introduction to ASA on Windows CE. • Introduction to UltraLite Windows CE. • Summary

40. Working with BLOBS

41. Working with Recordset Object & BLOBS Private Sub cmdFetchDirect_Click() If Len(txtPictID.Text) > 0 Then adors.Open "select id, logo from logos where id =" & CInt(txtPictID.Text), adoconn, adOpenDynamic, adLockOptimistic If adors.BOF And adors.EOF Then MsgBox “No records found" Else Set pctImage.DataSource = adors pctImage.DataField = adors.Fields.Item(1).Name End If adors.Close Set adors = Nothing End If End Sub Private Sub cmdSave_Click() On Error GoTo showError If Len(txtpath.Text) <> 0 Then adocmd.ActiveConnection = adoconn adocmd.CommandText = " INSERT INTO logos(logo) SELECT xp_read_file('" & txtpath.Text & "' )" adocmd.Execute MsgBox "Successfully inserted" End If Exit Sub showError: MsgBox "Failed to insert" End Sub

42. Working with Recordset Object & BLOBS PrivateSub cmdSavedDirect_Click() Dim bytChunk() As Byte Open txtpath.Text For Binary As #1 ReDim bytChunk(FileLen(txtpath.Text)) Get #1, , bytChunk() adors.Open "logos", adoconn, adOpenKeyset, adLockPessimistic, adCmdTable adors.AddNew adors!logo.AppendChunk bytChunk adors.Update adors.Close Set adors = Nothing MsgBox "Successfully Inserted!" Close #1 End Sub

43. Working with Long VarChar • Saving data of type Long VarChar • Replace(expression, find, replace[, start[, count[, compare]]]) Dim strLog As String Set adoCmd = New ADODB.Command 'Escape Character ‘Replace(txtDoc.Text, " ' ", " ' ' ", , , vbTextCompare) strLog = Replace(txtDoc.Text, "'", "''", , , vbTextCompare) adoCmd.ActiveConnection = adoConn adoCmd.CommandText = "INSERT INTO logs(lognote, logdate) VALUES('" & strLog & "','" & Format(clnDate.Value, "mm-dd-yyyy") & "')" adoCmd.CommandType = adCmdText adoCmd.Execute

44. Where Are We? • Visual Basic and its Data Interface. • ADO Programming Using Adaptive Server Anywhere OLE DB Provider. • Working with Blobs. • Introduction to ASA on Windows CE. • Introduction to UltraLite Windows CE. • Summary

45. Introduction to ADOCE • What is ADOCE? • ADOCE Programming with Adaptive Server Anywhere.

46. What is ADOCE? • ADOCE is a subset of ADO. • Microsoft has released an IDE, eVB, to develop for Windows CE.

47. Adaptive Server Anywhere OLE Provider • Connecting to an ASA Engine. • Creating Recordsets. • Navigating Recordsets. • Working with Blobs.

48. Connecting to an ASA Engine • Create a DSN file. • Place the DSN on the root of the CE device. • Create the connection string. [ODBC] uid=dba pwd=sql enginename=asademo Databasename=asademo databasefile=\asademo.db start=\program files\sybase\asa\dbsrv8.exe asademo.dsn

49. Connecting to ASA Engine Private Sub cmdConnect_Click() If (ConnectToSybaseDatabase("image")) Then MsgBox "Connected Successfully!" End If End Sub

50. Connecting to ASA Engine Option Explicit Dim Connection Function ConnectToSybaseDatabase(DSNname) 'Create the ADOCE connection object Set Connection = CreateObject("ADOCE.connection.3.1") 'Open the ASA provider Connection.Open "Provider=ASAProv;Data Source=" & DSNname ConnectToSybaseDatabase = True End Function