1 / 13

BOB 50 SQL : Migrating from BDE platform

BOB 50 SQL : Migrating from BDE platform. 3/09/2009. Agenda. Advantage Database Server : philosophy From BDE to BOB 50 SQL Warning and cautions BOB-link BOB-script BOB-dev BOB-ole Custom tools Conclusion/summary & questions. Sybase Advantage Database Server : philosophy.

ziv
Download Presentation

BOB 50 SQL : Migrating from BDE platform

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BOB 50 SQL :Migrating from BDE platform 3/09/2009

  2. Agenda • Advantage Database Server : philosophy • From BDE to BOB 50 SQL • Warning and cautions • BOB-link • BOB-script • BOB-dev • BOB-ole • Custom tools • Conclusion/summary & questions

  3. Sybase Advantage Database Server : philosophy Commercialized under « SQL » acronym rather than « ADS » or « Advantage Database Server » because it’s better known Advantages vs. other RDBMS concurrent solutions Small memory footprint (suitable for servers but also for traditional workstations) Very light administration work (parameters are adjusting themselves automatically) Installation is simple, maintenance is almost inexistent BOB.EXE process limited size Rights management through Windows NTFS permissions Developed by Sybase, company whose core business is RDBMS Advantages for BOB 50 : Advantage Database Server was already used with BOB Software for more than 150 licenses. Technology is already mastered by Sage. However, we use version 9 today to benefit from latest features. More robust database capable of handling large files and giving end-users high productivity features (filters, reporting, sort mechanisms,…) 3

  4. Conversion from BDE installations  BOB 50 SQL Only with the BOB Migration Kit, on a workstation/server with BDE. Better if all BDE files are stored locally. Soon in the Migration Kit (v2.1+): Conversion of Fista BDE data to SQL Fista version To convert from BOB 50 BDE to BOB 50 SQL, change the path of « BOB Software folder » to the one of your « BOB 50 BDE » installation. BOB 50 BDE companies will appear in the left column. New BOB 50 SQL installations must work in « NetSetup » mode (except for TS/Citrix). Sage won’t give support to other types of installation in the next months. 4

  5. Warnings and cautions File and folder structure in BOB 50 company folders : No more prefix with the two letters in filenames Prefixes becom AC_ (accounting, global files), IV_ (invoicing), FA_ (Fixed assets), FI_ (Fiscal) et BK_ (Bank) All operational files are in the same folder BAK sub-folder contains file backups after reorganization « NULL » has its own signification : With the SQL version, NULL has its own existence at filter level E.g. : (TCURRENCY<>’BEF’) becomes (TCURRENCY <> ‘BEF’) OR (TCURRENCY IS NULL) Don’t use quotation marks in filter expressions. Always use single quotes. Use brackets around tables and fields names to be sure that the SQL Engine does not interpret it as some SQL reserved words.E.g. : SELECT [LICENCES].[DATE] FROM [LICENCES] 5

  6. BOB-link BOBLINK.EXE and BOBLINKADSADT.EXE files BOBLINK.EXE uses TXT, DB and DBF files as input Warning : Please do not forget to install BDE BOBLINKADSADT.EXE is used to import ADT files (Advantage Database Format) only : BDE is not necessary In the SQL version, the LINK folder was relocated to the LINK\xx sub-directory where xx is the company id In the BOB.INI file, LINK=C:\B50ADSDEMO\LINK 6

  7. BOB-script Cautions : Change names and filepaths where they are hard-coded with the old notation structure. Cases where you don’t use « BOBOpen ». Check the filter expressions (own existence of NULL and use of single quotes) Code optimization : Use TBOBQuery to execute a SQL statement : Select, Insert, Update, Delete, Update or another on ADT tables. Avoid use of loops with « do while » and « next » and replace with WHERE conditions in the queries. For other database types (SQL Server, Oracle…), ADO objects are available Execution optimization : Add indexes on your custom fields via « General configuration » (currently with RUNMODE=BETA) 7

  8. BOB-script TBOBQuery : Properties : Sql: string Query definition RecordCount : integer Record count Bof : boolean At the beginning of the table Eof : boolean At the end of the table RowsAffected : integer Number of updated records Methods : procedure Open; Open the query in the recordset procedure Close; Close the recordset procedure ExecSql; Executes the query procedure First; Go to First record procedure Last; Go to Last record procedure Prior; Go to Previous record procedure Next; Go to Next record function GetFieldValue(AFieldName: string): variant; 8

  9. BOB-script Example 1  (Pascal) : myquery := tbobquery.create; myquery.sql := 'select * from AC_COMPAN'; myquery.open; ShowMessage(inttostr(myquery.recordcount)); myquery.last; showmessage(myquery.getfieldvalue('CID')); myquery.free; myquery := nil; Example 2 (Pascal) : myquery := tbobquery.create; myquery.sql := 'update AC_COMPAN set CADDRESS2 = ''AAA'' where CLANG = ''F'''; myquery.execsql; showmessage(inttostr(myquery.RowsAffected)); myquery.free; Myquery := nil; 9

  10. BOB-dev No changes Check names and filepaths that are hardcoded Take care with DatabaseName in the BOBDataSet Be careful with filter expressions 10

  11. BOB-OLE Be careful with filter expressions Some BOB Excel functions are optimized for the SQL version AccountBalance, CompanyAccountBalance, CostBalance Some BOB Excel functions could be slower with SQL Server, some other could be faster Check names and filepaths that are hardcoded Take care with DatabaseName in the BOBDataSet In the next months : Excel BOB function that takes a SQL statement as a parameter and returns a variant 11

  12. Custom tools DbViewer BOB Admin – Sybase Management Installation Diagnostic Advantage Data Architect and others http://www.sage.be/tools/sybase.zip Other tools (support not provided by Sage) : Advantage .NET Data Provider Advantage OLE DB Provider Advantage ODBC Driver Advantage Crystal Reports Driver Advantage PHP Driver 12

  13. Conclusion and questions Check filenames and filepaths Use custom indexes on tables where it’s necessary Be careful with NULL/ empty strings in filters and queries Use SQL queries whenever it’s possible to improve performance Test your custom queries with Advantage Database Architect Advanced LOGSQL=YES in [OPTIONS]  LogSQL.htm (to disable as soon as possible) AUTOADSINI=NO in [OPTIONS] if you have issues with automatic creation/update of ADS.INI To come in BOB 50 SQL 2.1 : BOB Business Views and BOB Business Reports Your questions and comments 13

More Related