RE: ODBC - Correct solution for passing a date field to Informix via ODBCand C#.NET
I cannot stress this enough. Don't use string concatenation to create sql. Use named parameters otherwise you are vulnerable to sql injection attacks. See http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.ht ml It also has the advantage of not having to worry about the correct date format for the database. Cheers David -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of group@phillyc.com Sent: Tuesday, 18 September 2007 9:09 AM To: 'Mike Aubury'; informix-list@iiug.org; 'Everett Mills' Cc: jcunningham3@verizon.net Subject: ODBC - Correct solution for passing a date field to Informix via ODBCand C#.NET Gentlemen, Thank you for your solutions. After implementing them we were still getting a syntax error from Informix. We pulled this solution off of the Microsoft ODBC website, and after trying the solution it worked successfully on the date field. This is the sql string for the Date Field Entry from C#. sql += 56 + "," + "{D '2007/09/13'}" + ","; Spyros Macris, President Philadelphia Candies, Inc. 1546 East State Street Hermitage, PA 16148 Phone: 724 981 6341 Fax: 724 981 6490 -----Original Message----- From: Mike Aubury [mailto:mike.aubury@aubit.com] Sent: Monday, September 17, 2007 2:07 PM To: informix-list@iiug.org; spyros@phillyc.com Subject: Re: ODBC Quote it... You might want to try single quotes... sql += 56 + ",'2007/09/13',"; ... On Monday 17 September 2007 18:53:19 group@phillyc.com wrote: > We are using C# in Visual Studio 2005 to write time values to an > Informix Database (Sun Solaris 2.6, Informix SE and Informix SQL Version 7.20.UD1). > > > > > The following are excerpts: > > > > Statement 1: > > sql += 56 + "," + 2007/09/13 + ","; > > > > Table result 1 > > 1900/01/17 > > ###### > > Statement 2 > > sql += 56 + "," + "2007/09/13" + ","; > > > > Actual sql string via watch value 2 > > "Insert into timeclock > (tc_serial,tc_asoc_num,tc_process_num,tc_date,tc_start_time, > tc_finish_time, tc_elapsed_time) > > values(0,9999,56,2007/09/13,8.3,8.35,0.35)" > > > > Table result 2 > > exception > > {"[Informix][Informix ODBC Driver][Informix]A syntax error has > occurred."} > > > System.Exception {System.Runtime.InteropServices.COMException > > ###### > > Statement 3 > > sql += 56 + "," + 0x22 + "2007/09/13" + 0x22 + ","; > > > > Actual sql string via watch value 3 > > "Insert into timeclock > (tc_serial,tc_asoc_num,tc_process_num,tc_date,tc_start_time, > tc_finish_time, tc_elapsed_time) > > values(0,9999,56,342007/09/1334,8.3,8.35,0.35)" > > > > Table result 3 > > exception > > {"[Informix][Informix ODBC Driver][Informix]A syntax error has > occurred."} > > > System.Exception {System.Runtime.InteropServices.COMException} > > > > ###### > > Statement 4 > > > > sql += 56 + "," + 0x22 + 2007/09/13 + 0x22 + ","; > > > > Actual sql string via watch value 4 > > "Insert into timeclock > (tc_serial,tc_asoc_num,tc_process_num,tc_date,tc_start_time, > tc_finish_time, tc_elapsed_time) > > > > values(0,9999,56,341734,8.3,8.35,0.35)" > > > > Table result 4 > > tc_date [2835/08/20] > > > > Any ideas on how to write this so that Informix will accept the date field? > > > > Spyros Macris, President > > Philadelphia Candies, Inc. > > 1546 East State Street > > Hermitage, PA 16148 > > > > Phone: 724 981 6341 > > Fax: 724 981 6490 > > > > > > > > Spyros Macris, President > > Philadelphia Candies, Inc. > > 1546 East State Street > > Hermitage, PA 16148 > > > > Phone: 724 981 6341 > > Fax: 724 981 6490 -- Mike Aubury Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Murlain Business Centre, Union Street, Chester, CH1 1QP __________ NOD32 2535 (20070917) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com __________ NOD32 2535 (20070917) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list This e-mail is intended for the use of the individual or entity it is addressed to and may contain information that is confidential, legally privileged, commercially sensitive or copyright. If you are not the intended recipient you must not disclose, disseminate, distribute or copy information contained in it. If you have received this e-mail in error, please notify the sender immediately and destroy the original message. While this mail and any attachments have been scanned for common computer viruses and found to be virus free, we recommend you perform your own virus checks before opening any attachments.