Read data in sql from vcard

Openfire Openfire Support
1

how i can read vcard data from sql query?

I must extract the e-mail address in sql from vcard table of openfire, can you suggest me the query?

2

Hi Thomas,

try “select * from ofvcard”

3

no this give me the vcard in xml format i must extract only e-mail from this

4

what database are you using with openfire? if it’s mysql, then you can do a “desc table” command to find which columns are in the table, and then build your select query from that.

I’d recommend installing the openfire DB Access plugin if it is not already installed (Openfire admin page --> Plugins --> Available Plugins --> Install DB Access)

DB Access will give you a new option on the openfire Server tab’s page near the bottom. It will be called, “DB Access” Click it.

Now, in the SQL Statement box, you can test our your queries easily.

Do a:

DESC ofVCard;

this will print in the SQL Output box all the columns in that table. Now you can build your query to extract the data you need.

You can also do a:

SHOW TABLES;

and the output will be all the tables in the openfire database. Be careful in there, there’s lots of triggers and other things setup, so be careful before you start changing or manipulating data as to avoid breaking openfire.

5

ok but i must select only the user that have e-mail address in vcard table

6

This kind SQL based the DB you are using, for example if you use hsqldb which is embedded in openfire . below SQL you can try

select username,SUBSTRING(VCARD,(LOCATE(’’,VCARD)+8),(LOCATE(’’,VCARD )-LOCATE(’’,VCARD)-8)) from ofvcard;