escape character to insert string data into database

[ChrisReed]

If I want use my own SQL Query to insert string data into a database I would use the command:

INSERT INTO table (col1, col2,...) VALUES (expr, expr,...)  and for my actual example it would be something like:

INSERT INTO tests VALUES ('12345.mtr', '2200E4033', 'P', 'RPH')   (NOTE: I don't need to specify the column names as long as the number of values matches the number of columns).  Also note I have to use apostrophes around all values that are strings.

However how do I insert the contents of a file into one of these fields if it already has apostrophes within the file ie. is there some sort of escape character I can use?

NI's High Level DB Tools Insert Data.VI can handle the insertion of string data with any character, so I was wondering how they do it?

Chris

[Yamaeda]

Concatenate Strings in the strings group can be expanded to build a string from the parts you're after. you're basically looking at:

INSERT INTO tests VALUES ('

S1

', '

S2', '

S3

', '

S4

')

So a concatenate string with some constants and your S1-S4 wired in between will give you the query you're after.

(Or you use the Insert data-module instead of Execute query)

/Y

[ChrisReed]

Hi /Y,

Thanks for the reply, but I'm not sure how your solution will take into account the extra apostrophes that will be in the text of the file that I want to insert into say the value of S4 for instance  eg.

INSERT INTO tests VALUES ('S1', 'S2', 'S3', 'S4')  now if the value of S4= This is some text file with apostrophe's

Then you will get...... 

INSERT INTO tests VALUES ('S1', 'S2', 'S3', 'This is some text file with apostrophe's') and LabVIEW will give you an error, because you've got an extra apostrophe.

Chris

[PhillipBrooks]

The toolkit is not creating a pure SQL command to add the data; it is using the ADO library.

Your string containing apostrophes is added to an ADO Command object as a parameter and ADO takes care of the details of placing the data into the DB.

[Yamaeda]

The solution as you're describing it: (you'll need to add open and close database)

I'll add a better version.

something like that.

/Y

[ChrisReed]

To Phil and /Y,

Your second picture, using the High Level DB Insert VI, is what I am currently using at the moment and it has worked fine for a couple of weeks now, but one Insert continues to fail which I posted here http://forums.ni.com/t5/LabVIEW/ADO-Error-0x80040E57-String-or-Binary-Data-truncated-Error/td-p/1264... (no reply as yet though ) which is why I was trying to see if writing a raw SQL Insert command would fix the problem, but I seem to be stuffed in both situations.

Chris

[PhillipBrooks]

I see you solved the problem in your original thread.

Good job! At least we know it wasn't a toolkit problem.

Now you just need to add a String Subset function to your LabVIEW code to limit to the user tech name to the first 5 characters...

[Yamaeda]

---

@Chris Reed wrote:

To Phil and /Y,

Your second picture, using the High Level DB Insert VI, is what I am currently using at the moment and it has worked fine for a couple of weeks now, but one Insert continues to fail which I posted here http://forums.ni.com/t5/LabVIEW/ADO-Error-0x80040E57-String-or-Binary-Data-truncated-Error/td-p/1264... (no reply as yet though ) which is why I was trying to see if writing a raw SQL Insert command would fix the problem, but I seem to be stuffed in both situations.

Chris

---

The bold part lead me to believe just what you say in the other thread, it's a database design problem.

You can actually double click the Insert VI and continue down a step or two to get the sql command as it's executed. I often copy paste that as a query in e.g. SQL server to see if/how it works there.

/Yameda