cancel
Showing results for 
Search instead for 
Did you mean: 

Incorrect translation of a Mediumint field

Former Member
0 Kudos

I have a field in a MySQL database that is a Mediumint data type, when it is displayed in Crystal Reports it is read as a memo data type. The problem comes in that the field in the MySQL database looks like this:

a:1:}

and in Crystal Reports looks like this:

613A313A7B693A32383B613A323A7B693A303B733A31333A22416E6E6F756E63656D656E7473223B693A313B693A303B7D7D

Is there a setting I can change to make this display probably? Also, can I make Crystal Reports read this as a string instead of a memo so that I may manipulate it?

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hello,

What version of Crystal Reports are you using and how are you connecting to the database?

If using ODBC then go to the Buisnessobjects tab above, Downloads and install all the patches for your version and test again. If it still doesn't work then download our DataDirect 5.3 version of our connectivity package and test using one of those drivers.

If you copy the SQL into some other ODBC test tool does that work? As suggested, it may be that your DBA needs to change the field type.

Thank you

Don

Answers (2)

Answers (2)

Former Member
0 Kudos

The data is showing up as it should in the field, but I still can't convert it into another field type so that I can manipulate it. I will talk to my DBA's and try to convince them to change the type. Thanks for the help.

Former Member
0 Kudos

You might cast it as a string:

cast (source expression as datatype)

For example if you have a memo field which runs on forever, you could say:

cast (problemfield as char(8000))

Which should convert problemfield from a memo to a 8000 character string...

Former Member
0 Kudos

The field is not appearing for selection in the SQL Expression Editor. When I manually place it in the formula the following error occurs

Error in compiling SQL Expression: Query Engine Error: '42000:{MySQL][OBDC 3.51 Driver][mysqld-5.0.45-log]

Any ideas what is causing that?

Former Member
0 Kudos

Can you do this conversion in a SQL Command instead of as a SQL expression?

Fuskie

Who thinks you might have better luck if you do the conversion before the data is read into Crystal Reports...

Former Member
0 Kudos

Sorry I am fairly new to Crystal Reports; SQL Command would be on the actual database correct, before it hits Crystal Reports?

My DBA's are convinced I can solve this in Crystal Reports and would like to not have me make them change anything on the database side.

Former Member
0 Kudos

You can add your own database command inside a Crystal report. In the Database Expert, navigate to a DB connection, and there will be an Add Command option. Add it to the report, and you can type any SQL query that your database supports.

HTH,

Carl

Former Member
0 Kudos

>

>My DBA's are convinced I can solve this in Crystal Reports and would like to not have me make them change anything on the database side.

DBA's are always convinced that they don't have to overextend themselves.

In the Database Expert, instead of selecting a table from the database, you can click the Add Command option and you will be given the opportunity to enter a SQL query.

Fuskie

Who recommends reading up on the feature in the online documentation if interested...