Solved

MySql Truncates my varchar(4000) to about 255 characters

Posted on 2012-04-02
3
614 Views
Last Modified: 2012-04-08
I have a table created in MySql with the following sql.

CREATE TABLE `rpheader` (
  `RPH_KEY` decimal(11,0) NOT NULL,
  `RPH_DESC` varchar(50) NOT NULL,
  `RPH_FTH` decimal(11,0) DEFAULT NULL,
  `RPH_TYPE` varchar(10) NOT NULL DEFAULT 'TABLE',
  `RPH_OWNER` char(8) NOT NULL,
  `RPH_TABLE` varchar(8) DEFAULT NULL,
  `RPH_FMT` char(1) DEFAULT 'H',
  `RPH_FLD1` varchar(20) DEFAULT NULL,
  `RPH_FLD2` varchar(20) DEFAULT NULL,
  `RPH_FLD3` varchar(20) DEFAULT NULL,
  `RPH_GRNAME` varchar(254) DEFAULT NULL,
  `RPH_TMPL` varchar(254) DEFAULT NULL,
  `RPH_HDR1` varchar(30) DEFAULT NULL,
  `RPH_HDR2` varchar(30) DEFAULT NULL,
  `RPH_HDR3` varchar(30) DEFAULT NULL,
  `RPH_HDR4` varchar(30) DEFAULT NULL,
  `RPH_SQL` varchar(4000) DEFAULT NULL,
  `RPH_GENSQL` char(1) DEFAULT NULL,
  `RPH_TSTMP` varchar(12) DEFAULT NULL,
  `RPH_RSRC` varchar(256) DEFAULT NULL,
  `RPH_GRID` char(1) DEFAULT 'T',
  `RPH_ORNT` char(1) DEFAULT 'L',
  `RPH_SFD` char(1) DEFAULT NULL,
  `RPH_FTHREQ` char(1) DEFAULT NULL,
  `RPH_HZERO` char(1) DEFAULT NULL,
  PRIMARY KEY (`RPH_KEY`),
  KEY `RPH_DESC` (`RPH_DESC`),
  KEY `RPH_TSTMP` (`RPH_TSTMP`),
  KEY `RPH_TYPE` (`RPH_TYPE`),
  KEY `RPH_FTH` (`RPH_FTH`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

I then run the following SQL to store a SQL statement in the column called rph_sql which is a varchar 4000.

update rpheader set rph_sql = 'SELECT EMH_NAME,
CASE WHEN PRJ_NAME LIKE ''P%'' THEN SUM(PSD_MIN) ELSE 0 END AS DIRHRS,
CASE WHEN PRJ_NAME LIKE ''M-%'' OR PRJ_NAME=''MARKETING'' THEN SUM(PSD_MIN) ELSE 0 END AS MARHRS,  
CASE WHEN CHH_CODE IN (''E2PM.03'',''E2PM.02'',''E2PM.01'',''E2PM.09'',''E2PM.08'') THEN SUM(PSD_MIN) ELSE 0 END AS ADMHRS,
CASE WHEN CHH_CODE IN (''E2PM.07'',''E2PM.06'',''E2PM.01'',''E2PM.05'') THEN SUM(PSD_MIN) ELSE 0 END AS TRNHRS,
CASE WHEN CHH_CODE IN (''E2PM.04'') THEN SUM(PSD_MIN) ELSE 0 END AS VPSHHRS,
CASE WHEN CHH_CODE NOT IN (''E2PM.04'') THEN SUM(PSD_MIN) ELSE 0 END AS NOVPSHHRS,
CASE WHEN PSL_RAT_CD IN (''OVERTIME'') THEN SUM(PSD_MIN) ELSE 0 END AS OTHRS,
SUM(PSD_MIN) AS TOTHRS
FROM PSDETAIL LEFT JOIN PSLINES ON PSD_PSL=PSL_KEY,TCPROJ,PSHEADER,EMPHIS,EMPLOYEE,CHRHIS WHERE PSL_PSH=PSH_KEY
AND PSH_EMH=EMH_KEY AND EMH_EMP=EMP_KEY AND PSL_PRJ=PRJ_KEY AND PSL_CHH=CHH_KEY  [ANDFILTER] GROUP BY EMH_NAME ORDER BY EMH_NAME ASC'
where rph_key=58;

The problem is my application only seems to read about 255 characters of it or until
SELECT EMH_NAME, CASE WHEN PRJ_NAME LIKE 'P%' THEN SUM(PSD_MIN) ELSE 0 END AS DIRHRS, CASE WHEN PRJ_NAME LIKE 'M-%' OR PRJ_NAME='MARKETING' THEN SUM(PSD_MIN) ELSE 0 END AS MARHRS, CASE WHEN CHH_CODE IN ('E2PM.03','E2PM.02','E2PM.01','E2PM.09','E2PM.08'

MySql workbench gives me this with the little dots at the end making me think there is more but I'm not sure it even stored in the table at this point.

SELECT EMH_NAME, CASE WHEN PRJ_NAME LIKE 'P%' THEN SUM(PSD_MIN) ELSE 0 END AS DIRHRS, CASE WHEN PRJ_NAME LIKE 'M-%' OR PRJ_NAME='MARKETING' THEN SUM(PSD_MIN) ELSE 0 END AS MARHRS, CASE WHEN CHH_CODE IN ('E2PM.03','E2PM.02','E2PM.01','E2PM.09','E2PM.08'...

How do I get it in correctly or if it is in correctly how do I get it out correctly?  How can I even tell if it is stored in correctly?
0
Comment
Question by:hmstechsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 300 total points
ID: 37799410
Used your create sql above then did the following:

mysql> insert into rpheader (rph_sql) values ('SELECT EMH_NAME,
    '> CASE WHEN PRJ_NAME LIKE ''P%'' THEN SUM(PSD_MIN) ELSE 0 END AS DIRHRS,
    '> CASE WHEN PRJ_NAME LIKE ''M-%'' OR PRJ_NAME=''MARKETING'' THEN SUM(PSD_MIN) ELSE 0 END AS MARHRS,  
    '> CASE WHEN CHH_CODE IN (''E2PM.03'',''E2PM.02'',''E2PM.01'',''E2PM.09'',''E2PM.08'') THEN SUM(PSD_MIN) ELSE 0 END AS ADMHRS,
    '> CASE WHEN CHH_CODE IN (''E2PM.07'',''E2PM.06'',''E2PM.01'',''E2PM.05'') THEN SUM(PSD_MIN) ELSE 0 END AS TRNHRS,
    '> CASE WHEN CHH_CODE IN (''E2PM.04'') THEN SUM(PSD_MIN) ELSE 0 END AS VPSHHRS,
    '> CASE WHEN CHH_CODE NOT IN (''E2PM.04'') THEN SUM(PSD_MIN) ELSE 0 END AS NOVPSHHRS,
    '> CASE WHEN PSL_RAT_CD IN (''OVERTIME'') THEN SUM(PSD_MIN) ELSE 0 END AS OTHRS,
    '> SUM(PSD_MIN) AS TOTHRS
    '> FROM PSDETAIL LEFT JOIN PSLINES ON PSD_PSL=PSL_KEY,TCPROJ,PSHEADER,EMPHIS,EMPLOYEE,CHRHIS WHERE PSL_PSH=PSH_KEY
    '> AND PSH_EMH=EMH_KEY AND EMH_EMP=EMP_KEY AND PSL_PRJ=PRJ_KEY AND PSL_CHH=CHH_KEY  [ANDFILTER] GROUP BY EMH_NAME ORDER BY EMH_NAME ASC');
Query OK, 1 row affected, 3 warnings (0.06 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'RPH_KEY' doesn't have a default value   |
| Warning | 1364 | Field 'RPH_DESC' doesn't have a default value  |
| Warning | 1364 | Field 'RPH_OWNER' doesn't have a default value |
+---------+------+------------------------------------------------+
3 rows in set (0.00 sec)


mysql> select char_length(rph_sql) from rpheader;
+----------------------+
| char_length(rph_sql) |
+----------------------+
|                  899 |
+----------------------+
1 row in set (0.00 sec)

mysql> select rph_sql from rpheader\G
*************************** 1. row ***************************
rph_sql: SELECT EMH_NAME,
CASE WHEN PRJ_NAME LIKE 'P%' THEN SUM(PSD_MIN) ELSE 0 END AS DIRHRS,
CASE WHEN PRJ_NAME LIKE 'M-%' OR PRJ_NAME='MARKETING' THEN SUM(PSD_MIN) ELSE 0 END AS MARHRS,  
CASE WHEN CHH_CODE IN ('E2PM.03','E2PM.02','E2PM.01','E2PM.09','E2PM.08') THEN SUM(PSD_MIN) ELSE 0 END AS ADMHRS,
CASE WHEN CHH_CODE IN ('E2PM.07','E2PM.06','E2PM.01','E2PM.05') THEN SUM(PSD_MIN) ELSE 0 END AS TRNHRS,
CASE WHEN CHH_CODE IN ('E2PM.04') THEN SUM(PSD_MIN) ELSE 0 END AS VPSHHRS,
CASE WHEN CHH_CODE NOT IN ('E2PM.04') THEN SUM(PSD_MIN) ELSE 0 END AS NOVPSHHRS,
CASE WHEN PSL_RAT_CD IN ('OVERTIME') THEN SUM(PSD_MIN) ELSE 0 END AS OTHRS,
SUM(PSD_MIN) AS TOTHRS
FROM PSDETAIL LEFT JOIN PSLINES ON PSD_PSL=PSL_KEY,TCPROJ,PSHEADER,EMPHIS,EMPLOYEE,CHRHIS WHERE PSL_PSH=PSH_KEY
AND PSH_EMH=EMH_KEY AND EMH_EMP=EMP_KEY AND PSL_PRJ=PRJ_KEY AND PSL_CHH=CHH_KEY  [ANDFILTER] GROUP BY EMH_NAME ORDER BY EMH_NAME ASC
1 row in set (0.00 sec)

Open in new window


So it does get stored correctly.  On MySQL Workbench you can right click on the field, then choose "Open Value in Viewer"

How does your application actually pull the data?  It should have been pretty straightforward, so I need more details in order to help troubleshoot.
0
 

Accepted Solution

by:
hmstechsupport earned 0 total points
ID: 37801090
Thanks.  You are right that I can see the data is getting into the MySQL database so my problem lies in my application which is more of a hornets nest I'm afraid.  It is Delphi 5 using ODBC DAC Pro from SoftVector.
So I think the basic question is answered and that I must follow up with SoftVector or re-post this as a new question in a new category.

Thanks for your help
0
 

Author Closing Comment

by:hmstechsupport
ID: 37820883
The issue turns out to be that the field type varchar(4000) is being interpreted by my ODBC driver as a varchar(255).  Changing the type to TEXT is the best solution.
However without assistance I would not have gone that route.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question