hmstechsupport
asked on
MySql Truncates my varchar(4000) to about 255 characters
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,PSH EADER,EMPH IS,EMPLOYE E,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?
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'','
CASE WHEN CHH_CODE IN (''E2PM.07'',''E2PM.06'','
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,PSH
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
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
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
However without assistance I would not have gone that route.