Thread: Postgresql/Cold Fusion cfinsert date problems
I'm evaluating Postgresql on Linux as a database back end to Cold Fusion on NT, and running into a problem with dates. The test setup is: Cold Fusion 5 on Win2000; the latest ODBC driver from odbc.postgresql.org; Postgresql 7 on Linux. Cold Fusion has two methods of inserting records into the database. The first is where you wrap a standard 'insert into...' SQL command in a <cfquery..> tag. This is working correctly. The second method takes defined form variables and inserts them into a specified table. Essentially it is building the SQL command for you. eg: <cfinsert datasource="postgresql" tablename="test_table" formfields="fielda, fieldb"> The problem with this method is that the date that ends up in date fields in the table is always the servers date, regardless of what information is placed into the form field, and in what format. Is this a Cold Fusion oddity; is there some setting in Cold Fusion or the odbc driver I can modify; is there a way of formatting date variables to get around the problem? Or do I look at not using the cfinsert method altogether? Thanks Paul Haddon Technical Services Manager Hartingdale Internet
Paul Haddon <paullist@mail.hartingdale.com.au> writes:
> The problem with this method is that the date that ends up in date
> fields in the table is always the servers date, regardless of what
> information is placed into the form field, and in what format.
I don't know anything about Cold Fusion, but a good way to investigate
what's going on would be to run the system with query tracing enabled
(for an ODBC client, probably the only convenient way to do this is to
start the postmaster with -d2, or the equivalent setting in
postgresql.conf). Then you could see exactly what queries are being
issued by CF, which might give a clue what the problem is.
regards, tom lane
Is this a formatting issue, or are you actually getting different dates from the ones that you are entering? If the issue is presentation/formatting, just use a function to get the look you want: http://postgresql.planetmirror.com/users-lounge/docs/7.1/postgres/functions- formatting.html If the dates are actually being altered, there are basically two possibilities: 1. You have set up some constraint or trigger which is producing this unwanted result as a side effect, or: 2. Your cold fusion app is doing it. If you suspect 1 above, just post your table and/or trigger definitions and someone will enlighten you. If 2: someone who knows about cold fusion will need to answer. > -----Original Message----- > From: Paul Haddon [SMTP:paullist@mail.hartingdale.com.au] > Sent: Monday, July 02, 2001 9:43 PM > To: pgsql-odbc@postgresql.org > Subject: [ODBC] Postgresql/Cold Fusion cfinsert date problems > > I'm evaluating Postgresql on Linux as a database back end > to Cold Fusion on NT, and running into a problem with dates. > > The test setup is: Cold Fusion 5 on Win2000; the latest > ODBC driver from odbc.postgresql.org; Postgresql 7 on Linux. > > Cold Fusion has two methods of inserting records into the > database. The first is where you wrap a standard 'insert into...' > SQL command in a <cfquery..> tag. This is working correctly. > > The second method takes defined form variables and inserts them > into a specified table. Essentially it is building the SQL command for > you. > eg: <cfinsert datasource="postgresql" tablename="test_table" > formfields="fielda, fieldb"> > > The problem with this method is that the date that ends up in date > fields in the table is always the servers date, regardless of what > information is placed into the form field, and in what format. > > Is this a Cold Fusion oddity; is there some setting in Cold Fusion > or the odbc driver I can modify; is there a way of formatting date > variables to get around the problem? Or do I look at not using > the cfinsert method altogether? > > Thanks > > Paul Haddon > Technical Services Manager > Hartingdale Internet > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On that note, where are the debug (-d) levels documented? I couldn't find it.. I had "query=4" in pg_options for v7.0.2. What would that be in v7.1? Thanks, -Cedar On Tue, 3 Jul 2001, Tom Lane wrote: > Paul Haddon <paullist@mail.hartingdale.com.au> writes: > > The problem with this method is that the date that ends up in date > > fields in the table is always the servers date, regardless of what > > information is placed into the form field, and in what format. > > I don't know anything about Cold Fusion, but a good way to investigate > what's going on would be to run the system with query tracing enabled > (for an ODBC client, probably the only convenient way to do this is to > start the postmaster with -d2, or the equivalent setting in > postgresql.conf). Then you could see exactly what queries are being > issued by CF, which might give a clue what the problem is. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Tue, Jul 03, 2001 at 09:20:30AM -0500, Jeff Eckermann wrote: > Is this a formatting issue, or are you actually getting different dates from > the ones that you are entering? I've tried using the date format functions of CF, and I've tried creating strings such as '2001-02-17'. Nothing makes a difference. Looking at the ODBC log, specified in the ODBC data source, shows that the ODBC driver is passing Postgresql the string 'YYYY-MM-DD', with the numbers being that of todays date. > 2. Your cold fusion app is doing it. Possibly. I'll try and check it through the CF support areas. Thanks Paul Haddon Technical Services Manager Hartingdale Internet