Crystal Reports 8.5 - Memo/String Field datatype WRONG

[PCSAARON]

I hope someone has a good answer for this, besides; "Upgrade or don't use Crystal Reports"... Because I have too much invested in CR.

Ok, I have a MySQL database. The "text" field type is used for a record's notes field.

MySQL Table specs:
id (int 11)
notes (text, Allow Nulls)

When the data is empty in the table, Crystal Reports will pull the field type in as "memo".

As soon as I add the data needed into the table (updated from another "text" field within the same MySQL Database), and go to create a new report, the data type is all of a sudden changed to String(255).

I searched around, and I saw people had a ton of issues with older versions of CR, but does ANYONE know why this is happening? Is there some data within my table that is telling CR what the wrong data type is?

Thanks in advance.

Aaron

 

[synapsevampire]

Upgrade or don't use MYSQL

I suspect that it's a ODBC problem, make sure that you have the latest ODBC driver.

Crystal has special considerations when using a memo field in CR 8.5, the fact that Crystal recognizes it as a memo when null, but a string when it has data is very odd, but keep in mind that MySQL is far from being a ready for prime time database.

Another option might be to cast or convert (not sure the syntax for MySQL) the field into multiple string fields by using a SQL Expression in Crystal (special type of formula field which uses the database syntax to pass the command in the select line of the query).

-k

 

[dfbonilla]

I don't exactly understand the issue but if you drop the field in the report assign the width and make it can grow, it will expand as necesary, no matter is a text or a memo field. Is it printing something weired when empty? Is truncating the information?

Regards, Diego

 

[PCSAARON]

Synapsevampire:

I could try the latest version of MySQL's ODBC. I haven't checked putting some data in the blank table yet. I am curious to see if it is specific data in a field somewhere, or because it is null like you stated.

Casting or coverting may work, I will try that and let you know.

I would beg to differ with you on the not being ready for "prime time database". But I guess it is all about what someone is familiar and comfortable with, and what it is used for. I have had nothing but good luck with MySQL.

Diego:

The issue is, it will truncate the field at 255 characters. Which would make sense if it changes it to String(255). Growing the field didn't work.


I HAVE seen this work before with this report, so it IS possible it is the ODBC driver.

I will try playing with it a little more, if anyone has more suggestions, please let me know. Thanks.

 

[dgillz]

Was this field once a text field in MySQL then changed to a memo field at a later date?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[PCSAARON]

dgillz:

That is a good question, I would need a little clarification on the quesiton. It is a "text" type now, which will allow more than 255 characters in MySQL. However, I had thought about that already, and it could be a possibility that it was a varchar(255) at one time when it was first ported. This database was ported from an access database quite a long time ago, so it may be a possibility that it was. I would have to dig the old database up and check. I doubt I have a backup that old that could tell me if it was in MySQL or not.

It would seem to make sense what your saying. When the MySQL table is BLANK, it will show the table field as a MEMO. However, once the data is used that is in the table, it will show up as string(255). The last thought I had was that it had something to do with the data or records in the table. I have no problems with other reports and "text" field types in Crystal Reports, just this database. This leads me to the question of; How does crystal reports know which field types to use? Does it go to the first record within the database and analyze it? Thanks for all your input.

Aaron

 

[dgillz]

You need to verfy your database. Click on database, verify database. When this happens, Crystal will "see" all the changes that have been made in the database itself. This is not the underlying data, just changes in the databse structure such as data type (could be your issue), length, field names, new fields, deleted fields, etc.

Let me know if this helps.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[PCSAARON]

dgillz:

This report was actually made after the fact that the table was changed in MySQL to a "text" field. (which of course is a memo field in Crystal) I verified the database. It still doesn't recognize it as a memo field.

Anyone else run into this problem before? Thanks.

Aaron