dbaccessdemo and the blob columns in catalog table

Hi Folks. I seem to be about to get into an Informix gig again so I'm active here again. In the stores_demo database, the only table with blob columns is catalog, with columns cat_descr (text) and cat_picture (binary). I wanted to test a function involving the lengths of the blob values so I copied the table into a new table named catalog_new. In this one I have set the blobs columns in a separate blobspace. Unfortunately, when I ran the dbaccessdemo script I got values only in cat_descr and these blob columns are all much shorter than the size of a blob page (which I set up as 6K.) There were apparently no pictures loaded into the blob columns of the catalog table. This is proven by the following query: select catalog_num, length(cat_descr) descr_len, length(cat_picture) pic_len from catalog order by 1 All the values for cat_picture are null. This kinda makes my test cases rather difficult to generate. Question: Is there an option to get those pictures loaded with the demo? For that matter, is there a .unl file containing these blob values? I was unable to find such columns the two catalog.unl files: o $INFORMIXDIR/demo/dbaccess/demo_ud/catalog.des o $INFORMIXDIR/demo/dbaccess/demo_ud/catalog.unl My other option is a VERY tedious google picture search for pictures of the various items named in the catalog, save them in local files, manually associate each saved picture with a catalog number and do a looping update of the cat_picture column. Like: update catalog set cat_picture = filetoblob(?, 'client') where catalog_num = ? The first ? would be the path of a file. VERY TEDIOUS! If I were to do this I don't know if I could post it anyway. I don't know if the software repository has been repaired to allow new items. Anyone done this yet? Anyone know of a dbaccessdemo option to get those pictures loaded? Thanks much! Jacob S.

Following up on my option, I did download a few relevant pictures and saved them in a subdirectory of where I am playing around with this. I tried this update statement: update catalog set cat_picture = filetoblob('catalog-pics/10001.BaseballGlove-01.jpg', 'client') where catalog_num = 10001 I got this error: 617: A blob data type must be supplied within this context. I also tried it with the optional extra parameters: update catalog set cat_picture = filetoblob('catalog-pics/10001.BaseballGlove-01.jpg', 'client', 'catalog', 'cat_picture') -- These are optional where catalog_num = 10001 I also tried ./catalog-pics/10001.BaseballGlove-01.jpg but that was clutching at a straw already and, of course, barfed just as eloquantly. Duh, howzabout you tell me what kind of context it requires? So now I have another question on the syntax and semantics of using filetoblob(). and I got the same error. I don't see any examples of the update command using filetoblob(). Clues anyone? Thanks! -- Jacob S.

The stores_demo database uses a BYTE column in the catalog table. If you want the BLOB/CLOB version, you need to create the superstores_demo database. For the superstores_demo to work properly, you probably need to setup a default sbspace (SBSPACENAME parameter). https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.dba.doc/ids _dba_015.htm The gif files used in the superstores_demo are in "$INFORMIXDIR/demo/dbaccess/demo_ud/" (the dbaccessdemo_ud script that creates the superstores_demo copies the gif file to the /tmp/). Luis Marques -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACOB SALOMON Sent: terça-feira, 12 de dezembro de 2017 05:20 To: ids@iiug.org Subject: Re: dbaccessdemo and the blob columns in catalog t [40363] Following up on my option, I did download a few relevant pictures and saved them in a subdirectory of where I am playing around with this. I tried this update statement: update catalog set cat_picture = filetoblob('catalog-pics/10001.BaseballGlove-01.jpg', 'client') where catalog_num = 10001 I got this error: 617: A blob data type must be supplied within this context. I also tried it with the optional extra parameters: update catalog set cat_picture = filetoblob('catalog-pics/10001.BaseballGlove-01.jpg', 'client', 'catalog', 'cat_picture') -- These are optional where catalog_num = 10001 I also tried ./catalog-pics/10001.BaseballGlove-01.jpg but that was clutching at a straw already and, of course, barfed just as eloquantly. Duh, howzabout you tell me what kind of context it requires? So now I have another question on the syntax and semantics of using filetoblob(). and I got the same error. I don't see any examples of the update command using filetoblob(). Clues anyone? Thanks! -- Jacob S. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The bottom line of this post is a request for help on the FILETOBLOB function and how I would avoid the errors in the explicit update of a blob column for a file. But first, I need to thank Luis for pointing me to the the alternative script: Luis, Thanks. I had never heard of the dbaccessdemo_ud before. I already have plenty of DBspaces do draw from: DBspace DNum Type k/pg Chunks NumPages FreePgs %-Full rootdbs 1 ---M 2 1 500000 487303 2.54 plog_dbs 2 ---N 2 1 400000 24947 93.76 llog_dbs 3 ---N 2 1 250100 47 99.98 tmpdbs01 4 T--N 8 1 25000 24947 0.21 tmpdbs02 5 T--N 8 1 25000 24947 0.21 data_dbs 6 ---M 16 4 43750 41089 6.08 index_dbs 7 ---M 8 1 50000 49923 0.15 blobs_dbs 8 -B-N 6 1 50000 49859 0.28 sbdbs01 9 --SN 2 1 12500 644 94.85 syssbdbs01 10 --SN 2 1 12500 644 94.85 Unfortunately, the above will get is spaces squeezed out of it so that you can't see how neat the columns are. But I will have no problem specifying a DBspace for the data. Now if I could only manipulate the script to create the indexes in index_dbs. But I digress.. My attempts to update the byte-blob column of the first row in catalog has opened my eyes to an area whence I have never ventured: The explicit setting of a blob column in a SQL statement in dbaccess. As all could see, my use of the filetoblob() SQL function was failing. Can anyone point me to the correct syntax to accomplish that? I thought that I might gain some insight from the .unl file for catalog. Here's the first line: 0|1|HRO|case|ROW(/tmp/cn_1001.gif,"Your First Season's Baseball Glove")|0,62,/tmp/catalog.des| (Please widen your window if that wrapped) Interesting syntax in that last column: |0,62,/tmp/catalog.des| Where is this documented? I think I have all of the 12.10 manuals in PDF so it's a matter where where to look in that haystack. But the .des file is all text so I don't follow how that relates to a blob. And the meaning of that 0,62? My ignorance is on display here. HELP! Can someone point me to working example of this? As I mentioned, I find only insert statements with FILETOBLOB calls, no updates. I have yet to start playing with the inserts to see if I can *those* working. Thanks much for advice here. -- Jacob S.

Hi Jacob, just quickly, and I might be missing something, maybe even your point: Afaik, filetoblob() is for BLOB/CLOB sblobs, won't work for TEXT/BYTE blobs. The syntax you're referring to: 0,62,/tmp/catalog.des This is very dbaccess LOAD specific, it means: take 62 bytes from offset 0 into file /tmp/catalog.des and put them into this row/field in the database. Have a look at $INFORMIXDIR/demo/esqlc/blobload.ec for how blob inserting/updating is done internally. Not many more ways, besides SQL INSERT/UPDATE. For some testing, the blobload utility can be quite handy. HTH, Andreas From: "JACOB SALOMON" <jakesalomon@yahoo.com> To: ids@iiug.org Date: 12/13/2017 02:06 AM Subject: Re: dbaccessdemo and the blob columns in catalog t [40365] Sent by: ids-bounces@iiug.org The bottom line of this post is a request for help on the FILETOBLOB function and how I would avoid the errors in the explicit update of a blob column for a file. But first, I need to thank Luis for pointing me to the the alternative script: Luis, Thanks. I had never heard of the dbaccessdemo_ud before. I already have plenty of DBspaces do draw from: DBspace DNum Type k/pg Chunks NumPages FreePgs %-Full rootdbs 1 ---M 2 1 500000 487303 2.54 plog_dbs 2 ---N 2 1 400000 24947 93.76 llog_dbs 3 ---N 2 1 250100 47 99.98 tmpdbs01 4 T--N 8 1 25000 24947 0.21 tmpdbs02 5 T--N 8 1 25000 24947 0.21 data_dbs 6 ---M 16 4 43750 41089 6.08 index_dbs 7 ---M 8 1 50000 49923 0.15 blobs_dbs 8 -B-N 6 1 50000 49859 0.28 sbdbs01 9 --SN 2 1 12500 644 94.85 syssbdbs01 10 --SN 2 1 12500 644 94.85 Unfortunately, the above will get is spaces squeezed out of it so that you can't see how neat the columns are. But I will have no problem specifying a DBspace for the data. Now if I could only manipulate the script to create the indexes in index_dbs. But I digress.. My attempts to update the byte-blob column of the first row in catalog has opened my eyes to an area whence I have never ventured: The explicit setting of a blob column in a SQL statement in dbaccess. As all could see, my use of the filetoblob() SQL function was failing. Can anyone point me to the correct syntax to accomplish that? I thought that I might gain some insight from the .unl file for catalog. Here's the first line: 0|1|HRO|case|ROW(/tmp/cn_1001.gif,"Your First Season's Baseball Glove")|0,62,/tmp/catalog.des| (Please widen your window if that wrapped) Interesting syntax in that last column: |0,62,/tmp/catalog.des| Where is this documented? I think I have all of the 12.10 manuals in PDF so it's a matter where where to look in that haystack. But the .des file is all text so I don't follow how that relates to a blob. And the meaning of that 0,62? My ignorance is on display here. HELP! Can someone point me to working example of this? As I mentioned, I find only insert statements with FILETOBLOB calls, no updates. I have yet to start playing with the inserts to see if I can *those* working. Thanks much for advice here. -- Jacob S. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.