MYSQL uploading text files to text fields

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,
RGuillermoProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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 ManagerAuthor Commented:
its data type TEXT
Pawan KumarDatabase ExpertCommented:
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

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Pawan KumarDatabase ExpertCommented:
Also try moving the file to c drive.
RGuillermoProject ManagerAuthor 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 ManagerAuthor 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 ExpertCommented:
>>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 ExpertCommented:
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.
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RGuillermoProject ManagerAuthor Commented:
Thanl you so Much Experts!
Pawan KumarDatabase ExpertCommented:
@RGuillermo - Is my comment assisted ?? I gave your all the details with working code., David just repeated what i said earlier ? Split -equal ??
RGuillermoProject ManagerAuthor 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 ExpertCommented:
>>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 !!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.