1 / 19

64-bit Cold Fusion 9 and MS Access: problems and possible solutions

64-bit Cold Fusion 9 and MS Access: problems and possible solutions. Simon Kingston Russ DenBleyker. Agenda. Introduce Main Problem and Solution Introduce Secondary Problem and Solution Tips on using ColdFusion with SQL Server. The Main Problem.

kyle-benjamin
Download Presentation

64-bit Cold Fusion 9 and MS Access: problems and possible solutions

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. 64-bit Cold Fusion 9 and MS Access: problems and possible solutions Simon Kingston Russ DenBleyker

  2. Agenda Introduce Main Problem and Solution Introduce Secondary Problem and Solution Tips on using ColdFusion with SQL Server

  3. The Main Problem Sometime this year, I&M Websites are moving to 64-bit servers running 64-bit ColdFusion 9 (CF9) 64-bit CF9 doesn’t natively support MS Access as a data source Therefore, I&M websites that are currently running CF with MS Access data sources will need to be updated to use a supported data source

  4. What Databases are Supported by 64-bit CF 9? DB2 MySQL Oracle Informix PostgreSQL SQL Server Sybase

  5. Proposed Solution to 64-bit CF9 Data Source Problem Deliver MS Access databases to NRPC Convert MS Access Databases to SQL Server Create a new CF9 Data Source using the SQL Server database Update CF to work with SQL Server data source

  6. Updates to Database • Data • Submit replacement MS Access database OR • Update data in SQL Server database (on DEV environment) • Schema • Submit replacement MS Access database OR • Update schema in SQL Server database (on DEV environment)

  7. The Other Problem The current CF app. publication model is as follows Create MS Access (or other) CF Data Source locally Build CF app. locally using the Data Source Send in database to NRSS Ft. Collins IT Team Wait for IT team to create CF Data Source Post CF code to production server

  8. Proposed Solution to the Other Problem Post CF code to DEV server and get CF working Move CF code to TEST server and do QA checks, corrections Submit Help Desk ticket to get CF code published to Production

  9. Process Flowchart

  10. Setting Up a Local CF Dev.vs. Using the WASO CF Dev. Server • Advantages of using local development server • You can view CF server logs and you can specify 127.0.0.1 for a debugging output IP address – this makes debugging much easier • You can upgrade to a new version any time you want • The server is always available • Advantages to using WASO server • You don’t have to do a Cold Fusion Developer installation • You don’t have to install SQL Server on your computer

  11. Setting Up a Local Dev. Environment Install Cold Fusion Development Server – downloaded for free from Adobe Install SQL Server (note that WASO is using SQL 2008, NOT SQL 2008 R2) Open SQL Server Network Configuration and verify that the TCP/IP service is enabled. Application databases can be developed in Access and then converted to SQL Server using the upsizing wizard. Create new SQL Server Cold Fusion data sources Convert Cold Fusion web pages from Access SQL to Transact-SQL (T-SQL) Move application to WASO server

  12. Using the WASO Dev. Server Clean up database by removing unnecessary tables and queries, removing linked tables, and cleaning up indexes Submit Access database for upsizing to WASO SQL Server Copy pages to WASO development server Convert Cold Fusion web pages from Access SQL to T-SQL

  13. Converting Access SQL to T-SQL Part 1: Upsizing Wizard data type conversion

  14. Converting Access SQL to T-SQL Part 2: Functions and Null Concatenation • There is no Trim() function in T-SQL • Access: Trim(Fieldname) • T-SQL: LTrim(RTrim(Fieldname)) • Concatenation involving nulls • Access: Using “&”, concatenating null doesn’t yield null. Using “+”, concatenating null yields null • T-SQL: Everything is concatenated using “+”, the result depends on your database options

  15. Converting Access SQL to T-SQL Part 3: Getting Date and Time • Date • Access: SELECT Date() • TSQL: Convert(smalldatetime, getdate()) • Date and Time • Access: SELECT Now() • T-SQL: SELECT Getdate()

  16. Converting Access SQL to T-SQL Part 4: Referring to Date Literals • Date Literals • Access: WHERE VisitDate = #1/1/2011# • T-SQL: WHERE VisitDate = ‘1/1/2011’ • Dates Using Cold Fusion Variables • Access: BETWEEN ###StartDate### AND ###EndDate### • T-SQL: Between #’StartDate’# AND #’EndDate’#

  17. Converting Access SQL to T-SQL Part 5: Formatting Dates • Format() Function Not Valid in T-SQL • Access: SELECT Format(VisitDate, “yyyy/mm”) • T-SQL: CAST(Year(VisitDate) AS Varchar(4)) + ‘/’ + CAST(Month(VisitDate)AS Varchar(2)) • Note that the string concatenation operator in T-SQL is ‘+’ and in T-SQL all pieces of a concatenation string must be converted to character and that T-SQL uses single rather than double quotes around literals. • Use Replicate to insert leading zeros • Access: Format([VisitDate],'yyyy/mm‘) • T_SQL: Cast(Year(VisitDate) AS varchar(4)) + '/' + replicate('0',2-len(MONTH(VisitDate)))

  18. Questions?

More Related