Media object update through SQL

Hi List,
We have a requirement to to update the item features long description for item where ever it is blank.
The long description is stored in Media object table.
Can we do this update through a SQL having data(long desc) in a temporary table and converting it to blob/rtf format .I tried this but the conversion is not working fine.
Will there be any issue in future if this is done through sql
or
Is it best to go with the JDE report and use the existing MO Data structure to achieve this.
Please provide your inputs.

Thanks

can some one suggest please

What's your database?

I know that it can't be done in Oracle using SQL. I remember seeing a procedure that did something with blob columns in JDEList. I'm not sure who posted it. But it may give you an idea how to procede.

Samantha,

Have a look at the following threads:

Post 152862 - How to Pass input Parameters to Stored procedure from JDE
Post 142466 - Blob fields in database
Post 130313 - How to get TXFT from F00165 by SQL--Oracle

Peter,
Thanks for reply.My data base is SQL 2005.

Actually My requirement is to update the media object description for 6000 items in item features applicaiton.
Item features application has DD item for long description which has its value coming from F00165.GDTXFT.
So i want to do bulk update for items where ever the long description is blank.
Report is not possible as we do not have set text option available.
Through application there is an issue like the MO viewer is opened,as i have many items in grid this is not working.
The only option left is to check if anything possible through sql.But i am stuck at converting text to blob data type to insert in F00165.GDTXT and also i may land up with issues in primary key mapping for F00165.
Any inputs on how to deal with this issue will be very helpful.


Thanks in advance.

Samantha,

I have virtualy no experience with MS SQL databases, but my guess is that there must be some type of procedure functionality. You may have to get your DBAs to do the update.

Sorry I can't be of more help.

I would have thought that there was at least one SQL 2005 guru on JDEList, maybe they are all on holidays at this time of year.

All the best with a solution. Don't forget to update this thread with any solution you find. I for one would be interested as some of it would probably be applicable to Oracle DBs.

Hi Peter,
The only solution to this problem seems to be writing a BSFN and use it in a report to do mass upload.
The bsfn would convert ITEM,SEQNO into GDTXKY format and converts text to blob and updates in F00165.GDTXFT.

Thanks

I'm pretty sure that all this is perfectly doable in both PL/SQL & Transact-SQL, given enough resources.

On the other hand, did I mention our "One Attachment Manager" software? - it can do all that and more...

Hi Samantha,

I have been informed that SQL 2005 does have an equivalent to Oracle database procedures, so this is may be an option, depending on the resources and expertise available to you. A third party application may be an option too, depending on the urgency and any ongoing needs combined with any temporary lack of resources available to you.

Thanks Alex but this is a one time activity so we can not go for third party software.

Peter,
If you can provide me more details about the procedure in SQL 2005 it will be very helpful.

Thanks

Alex,
If it is possible through Tsql can this be done in custom stored procedure?
We need to convert text to blob for GD.TXFT and for GD.TXKY
how do we map it as it is the combinaiton of item and sequence number etc.

Thanks

Yes, I believe so. But since I never looked, I have no details. It may not be easy, I just don't know...

Writing a BSFN was what we did, too.
And it was not TOO complicated. I just cloned one of the so many vanilla functions, half a day work
If you search well, you'll find my code there, somewhere.
If you still can't find it, I'll attach it, again

Adrian,
Please attach the code as i could not find it.
Thanks

Hi all,
This issue is resolved.
I have used B90CA892 in a simple report.
we need to create generic key using item,lang pref,sequence number and map it and the media object blob field is updated .
Prior to insert we need to delete the existing blank line in f00165 for the generic key and then inserting a new line with the B90CA892 is working fine
Thanks ALL.

Sorry I'm late Sam. I guess you dont't need my code anymore, do you?

Adrian,
I dont need the code as the standard is working fine for me.thank you.