We help IT Professionals succeed at work.

MYSQL uploading text files to text fields

RGuillermo
RGuillermo asked
on
Hello experts,
I need to store in trext fields some text files no bigger than a 100 k

using the following
UPDATE TBL_PATIENTS SET PAT_REM = LOAD_FILE('D:/TMP/PAT_MEMO.TXT') WHERE PAT_COD = 2;
it retuns ok but no change in the field... remains empty.

What am I doing wrong, is there a better way ?
Regards,
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Check whether the user has File Privilege access or not.

https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file

Query looks fine . This column ( PAT_REM ) has datatype - TEXT / BLOB? correct.
RGuillermoProject Manager

Author

Commented:
its data type TEXT
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please see if this works..

CREATE TABLE testr
(
ID	INTEGER AUTO_INCREMENT,
Fr	TEXT, 
PRIMARY KEY (ID)
);

INSERT INTO testr(ID, Fr) VALUES (1, LOAD_FILE('D:/TMP/PAT_MEMO.txt'));

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Also try moving the file to c drive.
RGuillermoProject Manager

Author

Commented:
Pawan Kumar,
Yes it works but in my case my patient table is already populated... so i do not need to create another register,
is there anyway to do the same with update ?
RGuillermoProject Manager

Author

Commented:
oh you mean I work in a paralel table and transfer after uploading ?
got you .. testing

But is there anyway to do it without using a paralel temporal table ?
Regards
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
>>But is there anyway to do it without using a paralel temporal table ?
I think the issue is that it works with INSERT not with UPDATE. Getting more details.
Please refer this- https://dba.stackexchange.com/questions/125464/update-a-column-using-load-file-function
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last comment. Please refresh the page and check my last comment.
->>LOAD FILE loads an entire table, not one column, not one cell.
Fractional CTO
Distinguished Expert 2019
Commented:
As Pawan suggested, it appears you're trying to use LOADFILE to load an entire file into a BLOB.

Likely better to write some custom code to do this. Your code will be very simple. Likely best to just write a simple loader script that...

1) Reads the file.

2) Does an INSERT of file contents into a BLOB.

Keep in mind record separators when you do this. Make sure your code avoids stripping or changing separators, as this may give you one long string of text, with no indication of record breaks... making subsequent use of your data difficult.
RGuillermoProject Manager

Author

Commented:
Thanl you so Much Experts!
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@RGuillermo - Is my comment assisted ?? I gave your all the details with working code., David just repeated what i said earlier ? Split -equal ??
RGuillermoProject Manager

Author

Commented:
Pawan Kumar,
After several tests I find David Favor's suggestion much more in control and useful than yours. Your´s worked only on specific cases and did not fill my expectations.....

this is my evaluation, I appreciate the effort all experts do but I do not consider appropiate or polite to question a somebody´s personal evaluation and point of view according to your personal standards.

if you disagree with my personal evaluation maybe you should contact experts-exchange to reconsider the system

In ten years using Experts- Exchange, I have never seen this kind of reaction,
I hope the high level and profesionalism of this site remains the same.

I hope a moderator reads this post.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>>After several tests I find David Favor's suggestion much more in control and useful than yours.
That's all right.. Apologies didn't want to offend you. Its just that the closing comments did not have much details so just asking to my knowledge for forward :)

Cheers !!