string conversion

[jack1971]

I am using JDBC to insert data into MS Access. Now I have to deal with length like 1'2"3/16. What I did was to escape ' and " by preceding them with \, but got syntax error.

Anybody can help?

By the way, Thank you very much, Burgess, for your suggestion. It did work.

 

[Steve101]

Jack,

The problem occurs when you try to create a string which includes one or more instances of the string delimiter character (which is usually a double quote character).

Access recognises a delimiter character in a string, if the next character following it is also a delimiter character. Thus the name O'Connor could be entered in Access as:

O''Connor if the delimiter character was single quote, or just as O'Connor if the delimiter is a double quote symbol.

I'm unsure of the exact mechanism that you're using to populate your Access tables, but suggest you determine what the delimiter character is (ie. single or double quote). When you've established this, you may then need to do a pre-pass over the data, doubling up on internal quote delimiter characters.

Its kinda hard exactly understanding your question, so I hope that this helps anyway,
Cheers,
Steve

 

[jack1971]

Steve,

Thanks for the help. Well, let me put my question in the following way:
1'2"3/16 is the length (US system), which means one foot two inches and three sixteenth. there are one single quote and one double quote involved.
My SQL would be like:
INSERT INTO table VALUES ('string',...), since I don't know how to represent the length by number or decimal, so I try to represent it by text.

Hope this could make the question kinda clearer.

Thanks again.

Jack

 

[Steve101]

Jack,

OK. I understand. Short answer is that its time you guys went metric, and avoided these problems in the first place!

At a more pragmatic level, and using the example you provided, you would need to convert each instance of a single quote in the string, to two single quotes (do not confuse this with one double quote).

Your example would then look something like this

INSERT INTO table VALUES ('1''2"3/16',...)

I still need further clarification.

(a)I use ODBC but not JDBC. Does JDBC allow you to link tables to the Access database

(b)Are you intending to use Access queries directly to laod the data, or are you thinking of doing it via code.

I'll be able to help further when you provide a bit more info; Perhaps the example above is enough,

Cheers,
Steve