marcwolf
Posts: 361
|
| Posted: 05/21/2007, 11:36 PM |
|
Hi Folks
I need some help desperately...
I am writing an application which talks to a MySQL 5.0 database using 3.51.14 ODBC.
The database stores its dates natively in this format "yyyy-mm-dd hh:mm:ss"
Normally it is not a problem when ODBC converts the dates int "dd/mm/yyyy hh:mm:ss" format but in this instance I need to retrieve the date in the native MySQL format.
I am hoping that making a pass through connection/query in Codecharge will help me.
Can someone give me some advice.
Many thanks
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
 |
 |
Benjamin Krajmalnik
|
| Posted: 05/22/2007, 8:34 AM |
|
Dave,
Can you elaborate? I m not sure what it is you are trying to do.
If you are talking about a mechanism by which you can pass a raw SQL query
and have it processes, there are security issues with that (SQL injections,
etc.). If you want to retrieve it in the native format, set up the type
of the receiving field to be a string, and I believe you will get the raw
value as stored in the database.
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/22/2007, 1:32 PM |
|
Hi Benjamin
Ok.. Here is the part of the project.
The client has a lot of letters that they send out and the letters need to be personalised (They are a legal firm - not a spam center)
So what I hope to do is to get them to write the documetns up in Word and ut in tags the same way that Codecharge do their tags. Then I save the document out as XML. This can then be stored somewhere either in the database or on disk.
When a document is retrieved for personalisation the system will look through the Tag table to work out what DB field goes where. This is pretty straigh forward.
Howver I will have embedded tags within tags.
Lets take a Bithdate. It is stored in the database in the format of "1960-10-05 15:45:33"
If I retrieve this it becomes "05/10/1960 15:45:33" due to the influence of Odbc/Codecharge or something.
However I can write (and get the DBA of the client ) to write very simple MySQL V5 Functions that can do additional formatting/conversion.
So that fnLegalDate('1960-10-05 15:45:33) becomes '5th October in the Year 1960'
So in the XML Document we have a tag called {LegalBirthDate} in the tag table we have this instructin (if this if NOT a straight field) SqlSpecial = fnLegalDate({BirthDate})
When the system reads this it will then need to extract the births date for a very large view and I need it to be in the format of yyyy-mm-dd hh:mm:ss otherwise the function call to MySQl will not recogonise the BirthDate as a date.
I know that I could do the formatting in the initial retrieval view but with dates, names etc the can be SO many combination it will make the view more massive than what it already is!!!.
So I get the base data and if necessary use MySQL funtions to format it more. Hence no CODE changes to the application for new date formats and the DBA can maintain the system by himself. This can also be reworked easily into dotNET when we migrate.
I hope this makes thing clearer. I need to somehow tell ODBC etc NOT to format Dates or Numbers when they are returned.
Many many thanks
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 05/23/2007, 10:08 AM |
|
Maybe casting the values in the query?
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/23/2007, 5:58 PM |
|
Hi Benjamin
Looks like that is what I will need to do. Create a MySQl function to cast the date into a string and return it that way.
Many thanks
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
