Database: Search in binary type

[J-M]

I just began with the utilization of database and I created a small database (with the database toolkit)  for technical papers and patents.  To store the abstract I use a binary type.  I want to know if it is possible to make a search in this field (i.e. SELECT * FROM Documents WHERE Résumé LIKE '%alumina%' AND Provenance = 'journal'; )?

Jean-Marc

[falkpl]

Have you tried this by executing the querry?  This should return the records as an array of variants if the querry is supported by your database.  I think Like is supported in binary fields but I usually just use strings not binary types.

[J-M]

Yes, but nothing is found (there no error):  I am supposed to have more than 25 hits.  I used the binary field only because I am limited with 255 characters if I take the string type.

If I made a query in others fields, everything is fine and I retrieve all the data.

Jean-Marc

[J-M]

I don't remember where I took the value of 255 characters, this is true?

Jean-Marc

[smercurio_fc]

---

J-M wrote:

I don't remember where I took the value of 255 characters, this is true?

Jean-Marc

---

I think you may be confusing the amount of characters with the value of the characters. In a string-type column/field the values can take on 1-255 (i.e., one-byte ASCII values). The length depends on which database you're actually using. For instance, in SQL Server a varchar column/field can be up to 8000 characters.

[falkpl]

What database are you communicating with (sql server, access ...)?  Can you verify that the sql command works outside of labview, there are slightly different sql syntax depending on the database.

[J-M]

I utilize Access.  I solved my problem with the use of "memo type" instead of binary type.  I was able to create a table with "memo type" by modifying DBToolsCreateTable.vi (it is not elegant but it is working).  To insert a new record in the table, I use "DB Tools Execute Query.vi" instead of "DB Tools Insert Data.vi".
 
I still have a problem.  For some town with a French name like "Saint Martin D'arc" with an apostrophe:  I have to write "Saint Martin D arc".                     
 
insert into Documents2 values ('US20040168931.pdf','brevet','Choisir','','2004','','','0','17','','Bonnardel, Oliver (Saint Martin D arc, FR), Vanvoren, Claude (Saint Jean De Maurienne, FR)','Method for regulating an electrolysis cell','Bonnardel, Oliver (Saint Martin D arc, FR), Vanvoren, Claude (Saint Jean De Maurienne, FR)','','')
 
There is a way to write data with an apostrophe?

 

Jean-Marc

[J-M]

With a small research on google I found:  replace ' by ''.

JM