Hi....

first of all, sorry for cross-posting....i had originally posted in the asp.net forum but i waited two days without getting any response....
i'm sorry but i seem to be stuck unless i can solve this problem....

I am having a problem with inserting decimals and dates into mysql database...excerpt from error log follows...couple of things are evident....
firstly:
the decimals are warped...there is a strange character like 'but when i check the contents of the textbox before and after the decimal conversion...the contents seem to be fine i.e. 45.45 comes out as 45.45 ..all fine there....so the extra character must be introduced by nhibernate

secondly:
the date is not in a aformat mysql will accept... i need a DateTime object but a c# datetime format is always dd/mm/yyyy and mysql is just the other way around...how can i get a datetime in the correct format...

thanks in advance..all help will be appreciated....

regards,
sanjeev.....
/////////////////////////////////////////////////////////////////////////////////
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Impl.BatcherImpl [] <> - about to open: -6 open IDbCommands, 0 open DataReaders
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Impl.BatcherImpl [] <> - Building an IDbCommand object for the SqlString: SELECT LAST_INSERT_ID()
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Persister.EntityPersister [] <> - Dehydrating entity: bsm.Customer#
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'dsfdsfs' to parameter: 0
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jhhkk' to parameter: 1
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hj' to parameter: 2
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'khjk' to parameter: 3
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjk' to parameter: 4
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjk' to parameter: 5
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hj' to parameter: 6
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'khj' to parameter: 7
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'kj' to parameter: 8
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'k' to parameter: 9
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jkh' to parameter: 10
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jk' to parameter: 11
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jhkkjh' to parameter: 12
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '56.67' to parameter: 13
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjjkj' to parameter: 14
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hhkj' to parameter: 15
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '67' to parameter: 16
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '45.89' to parameter: 17
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '45.67' to parameter: 18
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jkhjk' to parameter: 19
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '564564' to parameter: 20
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hkjhjk' to parameter: 21
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '78.23' to parameter: 22
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjkjhjk' to parameter: 23
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '78.45' to parameter: 24
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'ghjgj' to parameter: 25
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'ghjgjh' to parameter: 26
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jghgh' to parameter: 27
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjgjhg' to parameter: 28
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '7/07/2005' to parameter: 29
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'dfhjk' to parameter: 30
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jkhkjh' to parameter: 31
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'kjhjkhjk' to parameter: 32
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jkhjk' to parameter: 33
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjhkjh' to parameter: 34
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hkjhj' to parameter: 35
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hkjhj' to parameter: 36
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjkhjk' to parameter: 37
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'jkhj' to parameter: 38
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hkjhjk' to parameter: 39
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '15/07/2005' to parameter: 40
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding 'hjgjhgjh' to parameter: 41
2005-07-15 12:28:40,926 [3044] DEBUG NHibernate.Type.NullableType [] <> - binding '1' to parameter: 42
2005-07-15 12:28:40,926 [3044] INFO NHibernate.Impl.BatcherImpl [] <> - Preparing INSERT INTO bsmcustomer (co_last_name, designation, card_id, card_status, currency_code, picture_notes, identifiers, custom_list_1, custom_list_2, custom_list_3, custom_field_1, custom_field_2, custom_field_3, billing_rate, terms, payment_is_due, discount_days, percent_discount, percent_monthly_charge, tax_code, credit_limit, tax_id_no, volume_discount_percent, sales_purchase_layout, price_level, payment_method, payment_notes, name_on_card, card_number, expiry_date, particulars, code, account, remittance_method, comment, shipping_method, printed_form, freight_tax_code, use_customer_tax_code, verified, lastmodified, lastmodifiedby, salesrep_id) VALUES (?p0, ?p1, ?p2, ?p3, ?p4, ?p5, ?p6, ?p7, ?p8, ?p9, ?p10, ?p11, ?p12, ?p13, ?p14, ?p15, ?p16, ?p17, ?p18, ?p19, ?p20, ?p21, ?p22, ?p23, ?p24, ?p25, ?p26, ?p27, ?p28, ?p29, ?p30, ?p31, ?p32, ?p33, ?p34, ?p35, ?p36, ?p37, ?p38, ?p39, ?p40, ?p41, ?p42)
2005-07-15 12:28:40,926 [3044] ERROR NHibernate.Persister.EntityPersister [] <> - #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '56.67, 'hjjkj', 'hhkj', 67, 45.89, 45.67, 'jkhjk', 564564, 'hkjhjk', 78.23,' at line 1
Exception: MySql.Data.MySqlClient.MySqlException
Message: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '56.67, 'hjjkj', 'hhkj', 67, 45.89, 45.67, 'jkhjk', 564564, 'hkjhjk', 78.23,' at line 1
//////////////////////////////////////////////////////////////////////////////

I remember some bug like this with MySQL, though I don't know now what caused it. Make sure you are using the latest version of the MySQL Connector.

hi

i tried everything, but the decimals don't come out ok, dunno
what is wrong there....how come i can see a strange character in the generated sql...

second is the datetime problem....i create a datetime object with a
string in a textbox like 2005-05-05, but when i convert this to datetime the formatting is lost - it appears as 05-05-2005 in the generated sql...
how can i have a DateTime object in the correct format i.e. 2005-05-05 ?

i get the DateTime object but it always appears as 05-05-2005 and not 2005-05-05 ....this seems like a stupid thing....having a DateTime convertible into many formats but after conversion becomes a *string* ? now if i convert this datetime string into DateTime object....it's back to square one...05-05-2005....i lose the formatting ?

aaaarrrrrggghhhhh....whatamimissinghere ?

I don't understand your problem with DateTime...
Do you want to know how to convert a DateTime to string (with the order you want) ?
(read: DateTime.ToString Method)

Or is it related to this?

Hi KPixel,

No i don't want a string DateTime...i can easily get a string datetime in any format i want ...what i want is a DateTime object - in the correct format....ie. yyyy-MM-dd as is the mysql standard...

my problem is this datetime object always contains the default formatting..i.e...dd-MM-yyyy

how can i get a DateTime object which is interpreted as yyyy-MM-dd instead of dd-MM-yyyy

i hope i make myself clear enough....thanks for your help anyway....


cheers,
sanjeev

hi people,

i tried everything and the result seems to be the same....

1..the decimals get a strange character introduced in the query string.
2. i get a cannot convert mysqldatetime to system.datetime error even though my parameters are getting bound correctly, even for decimals as you can see below....

When interpreted, a DateTime object is converted ToString based on the current language. Instead of binding this object, you can bind its string representation: dateTime.ToString("yyyyMMdd");

Try to do the same for decimal :)

Hi KPixel,

thanks for your response, but i'm afraid i don't fully understand....

if i am to bind a string - then the database column type will also have to
be string - no ? how else can i have a datetime field in db but bind string from nhibernate ?

i'm kinda new to dotnet so please excuse my ignorance....

regards,
sanjeev.

hi all,

i switched to Postgre and now everything is working perfectly....dunno what it is with mysql....surprised no one faced this problem before...

cheers,
sanjeev.

I am having a problem with inserting decimals and dates into mysql database...excerpt from error log follows...couple of things are evident....
firstly:
the decimals are warped...there is a strange character like 'but when i check the contents of the textbox before and after the decimal conversion...the contents seem to be fine i.e. 45.45 comes out as 45.45 ..all fine there....so the extra character must be introduced by nhibernate
--------------------------------------------------------------------------------

I've seen this weird behaviour while using MySQL 5.0 (beta) but i could not reproduce yet when switched to MySQL 4.0.25.

I've read that the DECIMAL type was completely changed in 5.0 . ( In a nutshell: It used to be treated as string and now as a number). I guess that the .NET connector is not compatible with this new version.

Is this your case ?

Sebastian Talamoni

This is the relevant bug: http://bugs.mysql.com/bug.php?id=11550