0000-00-00 00:00:00 to TIMESTAMP

Jan 27 2010

posted by Gary Funk in ColdFusion, SQL | 4 Comments Bookmark and Share

I read ColdFusion Bloggers every day to learn what is going on in the ColdFusion community. Today I came across a blog entry that solved an old problem with MySQL and the zero timestamp.

In MySQL, if a timestamp is blank, it is set to 0000-00-00 00:00:00 and this causes a problem for ColdFusion, which will return an error of: Cannot convert value '0000-00-00 00:00:00? from column X to TIMESTAMP. Thanks to John Sieber at Thoughts from the mountains we have the simple answer. IN the ColdFusion Administrator, go to Data Sources, edit the MySQL (4/5) data source, click on Show Advanced Settings and enter

view plain print about
1noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull

in the Connection String dialog box.

This will make my job a little easier.

Thanks John.

This entry was posted on January 27, 2010 at 2:26 PM and has received 1064 views. There are currently 4 comments. Print this entry.

SQL and cfscript

Jan 13 2010

posted by Gary Funk in ColdFusion, SQL | 0 Comments Bookmark and Share

I've been working with the following code in ColdFusion 8:

view plain print about
1<cfscript>
2    myfile = FileOpen("D:/door/DS200912010255.txt", "read");
3    while(NOT fileisEOF(myfile)) {
4        x = fileReadLine(myfile);
5        while(left(x,1) == '$') {
6            x = fileReadLine(myfile);
7        }
8        a = listGetAt(x,1,",");
9        b = parseDateTime(listGetAt(x,2,","));
10        c = listGetAt(x,3,",");
11
12        dss = createObject("Java", "coldfusion.server.ServiceFactory").getDataSourceService();
13        conn = theDSS.getDatasource("dds_co").getConnection();
14        sql = "INSERT INTO door (location,statDate,info) VALUES ('#a#',#b#,'#c#')";
15        ps = theCONN.prepareStatement(sql);
16        su = ps.executeUpdate();
17        z = conn.close();
18    }
19    fileClose(myfile);
20</cfscript>

It works well until the variable c contains ', then it throws an error. I am thinking I need to escape the ' in the string.

This entry was posted on January 13, 2010 at 6:00 PM and has received 794 views. There are currently 0 comments. Print this entry.

MySQL: MyISAM or InnoDB

Jan 10 2010

posted by Gary Funk in SQL | 1 Comments Bookmark and Share

During the past two years, I have learned more about SQL than most ColdFusion programmers learn in a lifetime; and I have only touched the surface. I am not out to become an advanced DBA, I just want to be able to make the correct decisions, the first time. I also want to know what happens at the server engine and how I can get the best results.

Ray Camden (BlogCFC and CFBloggers) had posted an entry on his blog Looking for help with a bad query. I offered to check his database and Ray sent me an export to work with.

[More]

This entry was posted on January 10, 2010 at 3:45 PM and has received 790 views. There are currently 1 comments. Print this entry.

SQLyog and BlogCFC 'Default'

Jan 5 2010

posted by Gary Funk in SQL | 0 Comments Bookmark and Share

Here is an interesting feature I ran into in playing with MySQL and BlogCFC.

I use SQLyog to edit my MySQL data. I have been playing around with ways to set up blogs without having define them in the .ini file.

I had changed the blog field of an entry in the table tblbkigentires, to 'NewKid.' When I went to change it back to 'Default' I discovered that each time, the field ended up blank. I could change it to any string value, except 'Default.'

It turns out that the GUI of SQLyog treats 'default' as a special keyword. You may read more about it here and here.

This entry was posted on January 5, 2010 at 8:33 PM and has received 681 views. There are currently 0 comments. Print this entry.

Verify a DSN is online

Jan 2 2010

posted by Gary Funk in ColdFusion, SQL | 1 Comments Bookmark and Share

One thing all developers have to consider is not being able to connect to a DSN. There are several ways to deal with this problem. We can wrap each query in a Try/Catch block, we can check each time we make a query call, or we can just run the query and hope for the best. I prefer the first two choices.

[More]

This entry was posted on January 2, 2010 at 4:50 PM and has received 1026 views. There are currently 1 comments. Print this entry.