64 bit cold fusion 9 and ms access problems and possible solutions
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

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


  • 37 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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

Agenda

Introduce Main Problem and Solution

Introduce Secondary Problem and Solution

Tips on using ColdFusion with SQL Server


The main problem

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


What databases are supported by 64 bit cf 9

What Databases are Supported by 64-bit CF 9?

DB2

MySQL

Oracle

Informix

PostgreSQL

SQL Server

Sybase


Proposed solution to 64 bit cf9 data source problem

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


Updates to database

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)


The other problem

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


Proposed solution to the other problem

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


Process flowchart

Process Flowchart


Setting up a local cf dev vs using the waso cf dev server

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


Setting up a local dev environment

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


Using the waso dev server

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


Converting access sql to t sql

Converting Access SQL to T-SQL

Part 1: Upsizing Wizard data type conversion


Converting access sql to t sql1

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


Converting access sql to t sql2

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()


Converting access sql to t sql3

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’#


Converting access sql to t sql4

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


Questions

Questions?


  • Login