dbTalk Databases Forums  

RE: ODBC - Correct solution for passing a date field to Informix viaODBCand C#.NET

Go Back dbTalk Databases Forums Databases comp.databases.informix RE: ODBC - Correct solution for passing a date field to Informix viaODBCand C#.NET

comp.databases.informix comp.databases.informix


Discuss RE: ODBC - Correct solution for passing a date field to Informix viaODBCand C#.NET in the comp.databases.informix forum.



Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old   
David McPaul
 
Posts: n/a

Default RE: ODBC - Correct solution for passing a date field to Informix viaODBCand C#.NET - 09-17-2007 , 07:50 PM





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/1...on-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 (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of group (AT) phillyc (DOT) com
Sent: Tuesday, 18 September 2007 9:09 AM
To: 'Mike Aubury'; informix-list (AT) iiug (DOT) org; 'Everett Mills'
Cc: jcunningham3 (AT) verizon (DOT) 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 (AT) aubit (DOT) com]
Sent: Monday, September 17, 2007 2:07 PM
To: informix-list (AT) iiug (DOT) org; spyros (AT) phillyc (DOT) 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 (AT) phillyc (DOT) com wrote:
Quote:
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_s tart_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_s tart_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_s tart_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 (AT) iiug (DOT) 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.


Reply With Quote
  #2  
Old   
Sheshnarayan Agrawal
 
Posts: n/a

Default RE: ODBC - Correct solution for passing a date field to Informix viaODBCand C#.NET - 09-18-2007 , 02:23 AM





Quote:
sql += 56 + "," + "{D '2007/09/13'}" + ",";
This is as per the ODBC escape sequence standard and correct way of using
date format. This is good if your application intends to connect to many
heterogeneous databases. Whereas each native ODBC drivers will take care
of converting this date format to its equivalent native date format.

In case your application talks to only Informix database you might want to
inturn code the above statement as follows and save a bit of processing
time. Default Informix date format is Y4MD-

sql += 56 + "," + '2007-09-13' + ",";

For more information about DATE format in Informix, refer following link
and type DBDATE on the search box.

http://publib.boulder.ibm.com/infoce.../v10/index.jsp

HTH

-Shesh




"David McPaul" <dmcpaul (AT) lumley (DOT) com.au>
Sent by: informix-list-bounces (AT) iiug (DOT) org
18/09/2007 05:20

To
<spyros (AT) phillyc (DOT) com>
cc
informix-list (AT) iiug (DOT) org
Subject
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/1...on-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 (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of group (AT) phillyc (DOT) com
Sent: Tuesday, 18 September 2007 9:09 AM
To: 'Mike Aubury'; informix-list (AT) iiug (DOT) org; 'Everett Mills'
Cc: jcunningham3 (AT) verizon (DOT) 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 (AT) aubit (DOT) com]
Sent: Monday, September 17, 2007 2:07 PM
To: informix-list (AT) iiug (DOT) org; spyros (AT) phillyc (DOT) 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 (AT) phillyc (DOT) com wrote:
Quote:
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_s tart_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_s tart_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_s tart_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 (AT) iiug (DOT) 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.
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list




Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top