1 Topic by mathmathou (edited by mathmathou 2016-09-29 22:42:36)

  • Registered: 2015-02-24
  • Posts: 446

Topic: [SOLVED]-Scratching my head over this one... Save image by script

Hello Dmitry and all MVD fans,


I known how to load an image FROM database TO a component in a form or extract an image FROM database and save it to a file.


Extracting the file would be done with something like :

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
   s: string;
begin
     s := SaveFileFromDataBase('tablename', 'fieldname', Form1.TableGrid1.dbItemID); // save file to disk and get filename in system temp folder
     OpenFile(s); // open file
     CopyFile(s, 'C:\'+ExtractFileName(s)); // copy the file to disk C:\
end;

But what I do not known is how to to the reverse : how do you, by script, save a file into database ?


I've tried to use TJpegimage, TBlobField, TMemoryStream to load from file into memory and save but no success. The missing link is in the query :

SQLexecute('INSERT INTO img(id_asset,asset_img,asset_img_filename) VALUES (1,'+ms.LoadFromFile('test.jpg')+',"test.jpg")');

I just don't known how to pass the TImage variable to the query.



I've tried with parametrised queries as well, but failed miserably. Any clue for me ?


By the way, I'm asking because I have a loop scraping numerous web pages, and I don't want to load each image in a DBImage component and clicking on the button to save each image one by one.


Thanks and cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor

2 Reply by mathmathou (edited by mathmathou 2016-09-23 03:03:10)

  • Registered: 2015-02-24
  • Posts: 446

Re: [SOLVED]-Scratching my head over this one... Save image by script

By the way, here is what i get when I log the sqlite.db file activity as a request for image insert :

INSERT INTO "main"."img" ("image", "image_filename") VALUES (X'FFD8FFE000104A46494600010101012C012C0000FFED008450686F746F73686F7020332E30003842494D04040000000000671C0228006246424D443031303030616137303330303030663130343030303063643036303030303134303730303030366530373030303066653038303030303738306230303030643430623030303033343063303030306138306330303030303831313030303000FFE2021C4943435F50524F46494C450001010000020C6C636D73021000006D6E74725247422058595A2007DC00010019000300290039616373704150504C0000000000000000000000000000000000000000000000000000F6D6000100000000D32D6C636D7300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A64657363000000FC0000005E637072740000015C0000000B777470740000016800000014626B70740000017C000000147258595A00000190000000146758595A000001A4000000146258595A000001B80000001472545243000001CC0000004067545243000001CC0000004062545243000001CC0000004064657363000000000000000363320000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000074657874000000004642000058595A20000000000000F6D6000100000000D32D58595A20000000000000031600000333000002A458595A200000000000006FA2000038F50000039058595A2000000000000062990000B785000018DA58595A2000000000000024A000000F840000B6CF63757276000000000000001A000000CB01C903630592086B0BF6103F15511B3421F1299032183B92460551775DED6B707A0589B19A7CAC69BF7DD3C3E930FFFFFFDB00430006040506050406060506070706080A100A0A09090A140E0F0C1017141818171416161A1D251F1A1B231C1616202C20232627292A29191F2D302D283025282928FFDB0043010707070A080A130A0A13281A161A2828282828282828282828282828282828282828282828282828282828282828282828282828282828282828282828282828FFC2001108003C003C03011100021101031101FFC4001B00000203010101000000000000000000000506020304070001FFC4001801000301010000000000000000000000000001020304FFDA000C030100021003100000012372B84DA23456F4332ADAE7C2E753622954E69C7A15EF328CEB440DB9DF8EA0C49C5DF96C2748CF795D59C03AC63ACE92A2B5D8D0B96B5BF1C5CFD135E5B2C69045BCB16E3168BBF3C914A580AF05C1A5B63414969971060D9720906B61E1F47CA8554F3AA4103C8B87F5A611F4BCEB105D4BFFC400221000010402020203010000000000000000020001030405111012133106212241FFDA0008010100010502B300CAC4CE2F0B3A7CD9560ADF20AD22AD6E0B2DC5AA860A2B1E452C7D54EC3301B3C658FB250587CBD465367A203592AB19046FD1E306672AE5DE2FCCAE5B2406A584660B948FB464FDFC6E4C7A65F7C5DC98C2525EB0651E4AC8B453F437207C66DC513F75EB9DA158867392E631BC660405B6E1FD8ADE870EFAB6322C8508EC8BD5937FD43EF6B1AFAB8EA577106863D7FFC4001D110002020203010000000000000000000000010211103012202131FFDA0008010301013F01188AEF4461E0D6197982C706CF83E898AC6F8A1FB96F2A64A56B4BD55A19FFC4001B11000301010101010000000000000000000001111002202130FFDA0008010201013F01B947E6971F5F44F66F4CA2EBC528D5C4A8B572420F81733F065F6F6F97973FFFC4002C1000010301060502070000000000000000010002112103101231415104202261912371133233427281D1FFDA0008010100063F029B370ED0A1E174BABB202D598CA8B40E61F217A168D7DF8F8334CFE1FF00161B7F9B7283984141AEF28B4194D7B739465F9050C6621BCDC6DA431CDCCEEA9F7046B4D9695C957444EF7C67AA68E1DB34AFBA845CD6CBB454F37E0B187BB53A2936AEFD185F527F2AAEA01C15ABF86A97749DDABB73B98D7E0C4D5E9032D51AAA8E4EE533C283922F674DA23CBC3FBDC709843A02FFC40022100100020202020203010000000000000001001121314151617191A11081D1C1FFDA0008010100013F21B7E2E7F84F9F49B215D727D4B3E1815A67B032A0C9A4D8393F52BF03ADAE5D5EE0AF8EEF167A7A847BF41D4526F9F698C81D935B4D2525CA9D6DE8960355D2726F12ADBAD35EF2E13A39C9286CD96175C2CE4CF60997479417A94E1C9D407C0D0E981A0CC0B4F663ECB82B865181295BCF6954CD93D3F53DCACE1FD83CBAD150F88573F542D8E77C31B9D6B81DDFEA696C3A66220DF316D432E5D07985DFA21BF88DB930B046A06BB8811438879D8F53AB8C7E0614318E7FC441DB1D8C98E62B5E14CB2FE04E6B89A48272F31AB797D41897896254B4F89FFFDA000C03010002000300000010C188A12655316FB822FB5999DC007B7DA9BD6D99AB3C506AFFC4001C1101010100030101010000000000000000010011102131204151FFDA0008010301013F1048397F16AC7649E327C85B7A1B0665CB36EDEECC3A9D293F86ECDD493D36708A9356C19C613AC3920DE25ADEF3BC757EC27A8F90993E847961B0EEFFC4001C1101010101000301010000000000000000010011311021412051FFDA0008010201013F109CE586C596DBE3D60F2E1B3B3D529640FB17DB1274EC05D832D8A4C9619028612DB7F583220CF56C8C493F05C8EC412E4B6C36CB086323D67C97305ADCF1FFC4002110010002020202030101000000000000000100112141316151718191C1B1A1FFDA0008010100013F103202CCBB3CF911E5B35E4710A792F683B7E4FDC4C3F8CFB951CB68007B1BBEAA55965D17B9527D476D4AEA1F6B5C703F47AFEC19C2F905B82B234C25E5D592F92FA653D4353ADABAD54A08AA03091A56939562F27CDD45EE152EA85A5B4DCC77F5005B9AF6625D259722387D313CC8062F04DE84CCB874606162D19F5EE58939C69F2468A9BCC4FD5FDC3714B66F1C7231DA25A94F1296DDDCA18576FC9C021EC9F0D6EB9A6314BC2142B6B86F187C6228105D38B7B8C6962D76FAAB8EB3257DFA7673EE15100E6EB988AF09E210890D87F0572F39A26C4C83D029031C6E03FD25C1ACEC428BF098FB23353002B426B041E33CC3E241B04C4A88016D71F0465B41B9C740688D1A3797DB51553207B185868C98090BBB0A43FB054126376DBDDE549C43E3E9B3A833079730E43CA8462D54607D6E2C8B80E0FD9A56CE88CAE17CB76A7B322E6A3E25345D1D5F0F72BEA2A842234CB67BB0F739F130132771CC26CB55AC20745D76DA122C331FD0A2C78ECEE14800A0ACFFD9', 'test2.jpg')

The big part must be the "image code", so I guess the image is read by the system and converted to it's "hexadecimal content", but I don't know ...how.

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: [SOLVED]-Scratching my head over this one... Save image by script

Hello,


Please first download latest beta version 2.8
https://www.dropbox.com/s/k1wd8mqe3qb35 … a.zip?dl=0


then you can use this example:

procedure Form1_Button4_OnClick (Sender: string; var Cancel: boolean);
var
    sSQL: string;
    sFileName: string;
    sFieldName: string;

    Params: TParams;
    MemoryStream: TMemoryStream;
    Param: TParam;
begin
    sFileName := 'd:\db 1.jpg';
    sFieldName := 'somefile';

    sSQL := 'INSERT INTO test (title, '+sFieldName+', '+sFieldName+'_filename) VALUES("title text", :'+sFieldName+', "'+ ExtractFileName(sFileName)+'")';
    Params := TParams.Create(nil);


    // BLOB
    MemoryStream := TMemoryStream.Create;
    MemoryStream.LoadFromFile(sFileName); // load file to memory
    MemoryStream.Position := 0;
    Params.CreateParam(ftBlob, sFieldName, ptInput).LoadFromStream(MemoryStream, 15);  // 15 ftBlob (TBlobType)

    Form1.SQLConnection.Execute(sSQL, Params);

    MemoryStream.Free;
    Params.Free;
    Form1.TableGrid1.dbUpdate;
end;


also you can download project with this example:

Post's attachments

Attachment icon SaveFileToDatabase script.zip 5.21 kb, 512 downloads since 2016-09-26 

Dmitry.
  • Registered: 2015-02-24
  • Posts: 446

Re: [SOLVED]-Scratching my head over this one... Save image by script

Haaaaa Dmitry, thank you a thousand times !!!


I works and I was sure prepared and parametrized queries were needed...


Anyway, I successfuly added BY SCRIPT images in database with the code structure you indicated and this query :

sSQL := 'INSERT INTO asset (title, '+sFieldName+', '+sFieldName+'_filename) VALUES("title text", :'+sFieldName+', "'+ ExtractFileName(sFileName)+'")';

thank you again for that.


Now, I have a subsidiary question for you (of course...)


What if, instead of an INSERT, I wanted to do an UPDATE ?
I tried this query :

sSQL := 'UPDATE asset SET '+sFieldName+' = :'+sFieldName+','+sFieldName+'_filename = "'+ ExtractFileName(sFileName)+'" WHERE asset.id = "'+IntToStr(asset_id)+'"';

but nothing happens. No error message, no change in database, just nothing...



Do you have an idea of what might go wrong ?



Thanks again a million times for the 2.8 beta and this new gem !!


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: [SOLVED]-Scratching my head over this one... Save image by script

mathmathou

I have checked, it works and for UPDATE, example:

procedure Form1_Button5_OnClick (Sender: string; var Cancel: boolean);
var
    sSQL: string;
    sFileName: string;
    sFieldName: string;

    Params: TParams;
    MemoryStream: TMemoryStream;
    Param: TParam;
begin
    sFileName := 'd:\db 1.jpg';
    sFieldName := 'somefile';

    sSQL := 'UPDATE test SET ' +sFieldName+'= :'+sFieldName+', '+sFieldName+'_filename="'+ExtractFileName(sFileName)+'" WHERE id=1';
    Params := TParams.Create(nil);


    // BLOB
    MemoryStream := TMemoryStream.Create;
    MemoryStream.LoadFromFile(sFileName); // load file to memory
    MemoryStream.Position := 0;
    Params.CreateParam(ftBlob, sFieldName, ptInput).LoadFromStream(MemoryStream, 15);  // 15 ftBlob (TBlobType)

    Form1.SQLConnection.Execute(sSQL, Params);

    MemoryStream.Free;
    Params.Free;
    Form1.TableGrid1.dbUpdate;
end;
Dmitry.
  • Registered: 2015-02-24
  • Posts: 446

Re: [SOLVED]-Scratching my head over this one... Save image by script

As usual, Dmitry, you are my savior.


You were right, the update works, my code was just a little messed-up.


Thank you mate, and have a good week-end.


Cheers



Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor