Newbie -- blob fields (mysql)

Classic Servoy
1

Hi there,

I’m brand new to Servoy – finally downloaded the demo a couple of days ago and started experimenting.

I have a hosted mysql database driving a support ticket website for my clients. Basically this is an open source setup: phphelpdesk. (I’m a Filemaker developer, and so far know almost nothing of mysql/php. I simply installed this; haven’t much clue how it works yet.)

So, the first thing I do with Servoy is to set up this hosted mysql database as a dataprovider. So far so good – after getting passwords right, all is well. I can build forms using data from the database.

The problem I see immediately, though, is that the blob columns in the database don’t display in the Servoy form. All I get is gobbledegook like “[B@b48ee1”, and even this disappears if I click into another field.

OK. So likely I’m going too fast… Do I just need to learn more about mysql field types? Am I doing something fundamentally wrong?

A quick pointer would be appreciated…

Thanks!

Steve

2

Hi Steve, welcome to Servoy

Probably you are trying to display images on a plain text field, switch the “displayType” property of the field from text to image-media.

3

Hi Enrico

The field in mysql is defined as blob, but it’s contents are simply text. I don’t know enough about sql yet to understand the difference between text and blob in any detail. But basically the field contains text entered through a website (using PHP).

I didn’t make the sql database – it was generated as part of an open source project. So I don’t know why the fields are of particular types.

Anyhow, I have the field set to display as “text area”. Changing this does not seem to improve anything…

Steve

4

Hi Steve,

First of all, welcome and thanks for your interest in Servoy.

Did you also tried setting the displayType property to IMAGE_MEDIA?

5

Hi Maarten

I did try setting the type to image_media. I get a brief flash of “Loading image”, then nothing with that setting. Which is not surprising (to me anyway) as the content of the field is simply text entered via a webpage/form using PHP…

If this field contains text (entered via PHP from the web), it displays as garbage in Servoy. If I ADD some new text after the garbage in Servoy, then nothing displays on the web page – and next time I go back to the record in Servoy it too is empty… Then looking “directly” at the database (MySqlAdmin) I see that the blob field is “null”.

My hosting service is running MySQL 4.0.22-standard.

As I say, I’m completely new to sql databases. I have some idea what a blob field is for, and I guess I can see why it would not display in MySqlAdmin. But I seem to be missing something else important here…

Steve

6

Blob fields are used to store binary data. You can put any binary data in them, like a .jpg image, a .doc word document, a .pdf file, a .exe program etc. You can load from and save to file from a blob field (Media field in servoy). If the binary corresponds to an image it will show the image in the image-media Field. I would not use them to contain plain text data.

7

The field in mysql is defined as blob, but it’s contents are simply text.

This is not very common.
Are you really sure it’s plain text and not a text file?

Try this method on one of your records.

application.writeFile("test.txt", myBlobField)

It converts the column content(binary) “myBlobField” to a text file inside your main Servoy directory.

note: the extension .txt might not be the right one.
depends if the blob column contains a file in the first place
and, if so, what kind of file it contains (can be .zip .xls .txt etc…)

8

maarten:

The field in mysql is defined as blob, but it’s contents are simply text.

This is not very common.
Are you really sure it’s plain text and not a text file?

Try this method on one of your records.

application.writeFile("test.txt", myBlobField)

It converts the column content(binary) “myBlobField” to a text file inside your main Servoy directory.

note: the extension .txt might not be the right one.
depends if the blob column contains a file in the first place
and, if so, what kind of file it contains (can be .zip .xls .txt etc…)

Hi Maarten

I can’t say I’m sure of anything in this non-Filemaker world! However, the database was created for me (see sql pasted below), and the field is set as type blob. Data is entered into the field via the web (using PHP). No file uploads or anything like that.

It looks like I should work at getting that blob field redefined as text. Could be difficult, as I’ve already got live data in the database. Any hints on that?

Thanks for the advice. I’m beginning to get the picture…

Steve

SQL

Table structure for table ‘ticket’

CREATE TABLE ticket (
t_id int(7) unsigned NOT NULL auto_increment,
t_category varchar(50) DEFAULT ‘’ NOT NULL,
t_detail blob,
t_priority smallint(1) unsigned,
t_user varchar(50) DEFAULT ‘’ NOT NULL,
t_timestamp_opened datetime,
t_timestamp_closed datetime,
t_department varchar(50) DEFAULT ‘’ NOT NULL,
t_location varchar(20) DEFAULT ‘’ NOT NULL,
t_summary varchar(60) DEFAULT ‘’ NOT NULL,
t_userfirstname varchar(30),
t_userlastname varchar(30),
t_usertelephone varchar(30),
t_useremail varchar(30),
t_computerid varchar(50),
PRIMARY KEY (t_id)
);

9

Actually, in the php / MySQL world this is very common - using blob fields to hold indeterminate type of data (often of greatly variable length).

Usually, a developer will use a blob type to hold data where he is not sure of the datatype, or wants a flexible datatype.

PHP has many built in functions to convert blob data into other formats - strings, binary for images, etc., etc .

Unfortunately, utilizing these blobs in Servoy is difficult and usually requires a workaround like using binary and string functions to mash the data into a form Servoy can use and display.

NCM
FSCI

sorry, old thread, just ran into it while wandering around on here.

10

In Servoy 3.5.x you can use the Serialize plugin for this.
It’s not a workaround but actually a standard way (using JSON) of dealing with objects like this.

Hope this helps.

11

I use JSON in web development circumstances, haven’t tried it in Servoy yet :slight_smile:

Thanks for the reminder.

NCM