{"id":138,"date":"2011-12-07T19:59:47","date_gmt":"2011-12-07T19:59:47","guid":{"rendered":"http:\/\/62.131.51.129\/wordpress\/?p=138"},"modified":"2011-12-07T19:59:47","modified_gmt":"2011-12-07T19:59:47","slug":"add-blobs-in-your-database","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=138","title":{"rendered":"Add blobs in your database"},"content":{"rendered":"<p>For some reason, I always overlooked the possibility to include pictures in a database. I decided to overcome this flaw and started experimenting with it.<br \/>\nThe first step was to create a table in Access. In Access, we have the possibility to use the datatype &#8220;OLE Object&#8221;. A field with this datatype can be used to store pictures. The inclusion of a picture is then straightforward. Rightclick on the field brings you in a menu that allows you to include a picture:<br \/>\n<img decoding=\"async\" src=\"http:\/\/62.131.51.129\/Access.jpg\" alt=\"screenprint\" \/><br \/>\nThat was really easy.<br \/>\nLet us move to Oracle.<br \/>\nIn Oracle, it is really easy sailing. Let us first create a table.<\/p>\n<pre>\nCREATE TABLE \"SCOTT\".\"BLOBJE\" \n   (\t\"NUMMER\" NUMBER, \n\t\"PLAATJE\" BFILE\n   )\n<\/pre>\n<p>Then, we create a storage where to put the images in.<\/p>\n<pre>\nCREATE OR REPLACE DIRECTORY \nPLAATJE_DIR AS \n'\/home\/tom\/oracle';\n<\/pre>\n<p>This is then followed by storing a nice picture in the storagearea (=\/home\/tom\/oracle). Let us say, we stored a file &#8220;thumbsup.gif&#8221; there.<br \/>\nThe final step is to store a record in the table that was created first:<\/p>\n<pre>\nINSERT INTO blobje VALUES(4,bfilename('PLAATJE_DIR','thumbsup.gif'));\n<\/pre>\n<p>We can then see the picture from within an Oracle table:<br \/>\n<img decoding=\"async\" src=\"http:\/\/62.131.51.129\/Oracle.jpg\" alt=\"Oracle\" \/><br \/>\nThen with Teradata, it was not so simple. Like in Oracle, I created a table first:<\/p>\n<pre>\nCREATE SET TABLE financial.blobje\n     (\n      plaatje BLOB(2097088000),\n      nummer INTEGER NOT NULL)\nUNIQUE PRIMARY INDEX ( nummer );\n<\/pre>\n<p>I then changed the mode of Teradata SQL Assistant by listening to import. This was accomplished by File>Import Data. I then started a query, saying:<\/p>\n<pre>\ninsert into  financial.blobje(nummer,plaatje) values (3,?B);\n<\/pre>\n<p>.<br \/>\nThis started a dialogue where a reference was made to a control file that contained the name of the picture. The controlfile was stored in the same directory as the picture and it only contained the name of the picture.<br \/>\nThis is not trivial. Nor could it be found in Teradata manual (at least I couldn&#8217;t). The information was retrieved from &#8220;http:\/\/developer.teradata.com\/applications\/articles\/large-objects-part-1-loading&#8221;. <\/p>\n<p>In this website, I found another solution that uses the BTEQWIN client. Let us first create a table:<\/p>\n<pre>\ncreate table financial.blobje(\n    id varchar (80),\n    binary_lob binary large object\n)\nunique primary index (id);\n<\/pre>\n<p>Then store several pictures in a directory (say in D:\\tmaanen\\Desktop\\). Say we store &#8220;thumpsup.gif&#8221; and &#8220;thumbsdown.gif&#8221; in tht directory.<br \/>\nAlso create an import.ctl file that contains:<\/p>\n<pre>\nD:\\tmaanen\\Desktop\\thumbsup.gif,xxxx\nD:\\tmaanen\\Desktop\\thumbsdown.gif,yyyy\n<\/pre>\n<p>In BTEQWIN, we create a loop that reads the lines in this import.ctl file. This can be achieved by:<\/p>\n<p>.import vartext &#8216;,&#8217; LOBCOLS=1 file=&#8217;D:\\tmaanen\\Desktop\\import.ctl&#8217;;<\/p>\n<p>followed by:<\/p>\n<pre>\n.repeat *\nusing (lobFile blob as deferred,id varchar(40))\ninsert into financial.blobje (id, binary_lob)\nvalues (:id, :lobFile);\n<\/pre>\n<p>It is not really easy, but it can be admittingly done.<\/p>\n<p>Uoef:<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/62.131.51.129\/teradata.jpg\" alt=\"teradata\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>For some reason, I always overlooked the possibility to include pictures in a database. I decided to overcome this flaw and started experimenting with it. The first step was to create a table in Access. In Access, we have the possibility to use the datatype &#8220;OLE Object&#8221;. A field with this datatype can be used [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-138","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=138"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/138\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=138"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}