Example 2:

Let's make the previous example a little more interesting by associating some meaningful text with the vectors. In this case there is no direct connection between the text and the associated vector, but we'll get to that.

-- Create a traditional table in the same database database examples;
create table ifx_sample_tab2 (
id bigint,
embedding lvector_embedding, metadata lvarchar,
unused datetime year to second, primary key (id)
);
-- Insert some rows into this table insert into ifx_sample_tab2 values
(
1,
'[1,2,3,4,5,6,7,8]',
'This vector is somehow related to squirrels', '2026-01-26 18:43:20'
);
insert into ifx_sample_tab2 values (
2,
'[8,7,6,5,4,3,2,1]',
'This vector is somehow related to car racing', '2026-02-26 18:43:20'
);
insert into ifx_sample_tab2 values (
3,
'[1,1,2,2,3,3,4,4]',
'This vector is somehow related to architecture', '2026-03-26 18:43:20'
);
insert into ifx_sample_tab2 values (
4,
'[5,5,6,6,7,7,8,8]',
'This vector is somehow related to wrestling', '2026-04-26 18:43:20'
);
insert into ifx_sample_tab2 values (
5,
'[3,3,3,3,3,3,3,3]',
'This vector is somehow related to sound', '2026-05-26 18:43:20'
);
-- Now let's create a lance table to help us search on these vectors
EXECUTE PROCEDURE lvector_create_table('sample_tab2', 8);
-- Copy the id, vector, and metadata columns into the lance table.
-- Note that the name of the metadata column must be 'metadata'. Also
-- note that we are not copying the datetime data.
EXECUTE PROCEDURE lvector_bulk_sync('ifx_sample_tab2', 'sample_tab2', 'id', 'embedding');
-- Now let's find the metadata string associated with the vector that
-- is most similar to [9,8,7,6,5,4,3,2]: SELECT metadata
FROM ifx_sample_tab2 WHERE id IN
(SELECT * FROM TABLE(lvector_id_search('sample_tab2', lvector_in('[9,8,7,6,5,4,3,2]'), 1)));

According to our vector comparison, the given vector is most similar to the vector associated with car racing. Since this association was completely contrived in this case, these results aren't particularly useful. But we will improve upon that in the next example, which uses OpenAI to create actual embeddings from text. In order to run this SQL you will need an active OpenAI key, and you will need to provide your key to Informix one of two ways:

1. Set your IFX_LVECTOR_OPENAI_KEY configuration parameter to your key and bounce the instance, or
2. Execute a procedure to set the key on the fly, as in: database examples;

EXECUTE PROCEDURE lvector_set_embedding_api_key('sk-proj--9_V4YfRXV3xxxxxxbcqdKRkA');

Once this key is set you can test it with the following statement: EXECUTE FUNCTION lvector_embed('Hello world');

If that function returns an array of 1536 floating point numbers, your OpenAI key is working beautifully. However, the following error indicates that the setting is unfortunately not yet working:

(U0001) - Failed to generate embedding Error in line 1

Near character position 45

The most likely reasons for this error:

1. Your IFX_LVECTOR_OPENAI_KEY configuration parameter is not set correctly in your $INFORMIXDIR/etc/$ONCONFIG file. The line should look something like this:

   IFX_LVECTOR_OPENAI_KEY sk-proj--xxxxxxxxxxxxxxxxx9_V4YfRXV3aWOEw9Al0fviZg_D0GbcqdKRkX

2. You did not bounce (shut down and restart) your instance after setting IFX_LVECTOR_OPENAI_KEY in your config file
3. Instead of setting the IFX_LVECTOR_OPENAI_KEY configuration parameter you chose to set the key on the fly using the lvector_set_embedding_api_key() routine, but since doing so your current database has changed.
4. The key you have provided to Informix is not an active OpenAI key.
5. The machine on which you are running your Informix instance does not have access to the internet.

Assuming your OpenAI key is set properly and you are able to successfully execute the lvector_embed() routine, the following example will be of interest.