Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: quoted string not properly terminated when I try to insert chr$(0)

    Hi Guys

    I'm hoping someone might be able to help with this. I'm using the ODBC32 SQLExecDirect function to write data to a variety of DBs from a VB app. This is working find for SQLServer and ISeries but it gives me a "ORA-01756: quoted string not properly terminated" error when I try to write to Oracle.

    I'm fairly sure that the cause is that one of the fields I want to write contains, along with some normal character data, a null character chr$(0). I do want that character to be written to the data and I don't really want to have to do any substitution because this field could potentially contain just about anything so undoing the substituion won't be reliable. Is there any way I can do this.

    Thanks in advance for any help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there any way I can do this.
    Sorry I am not clear on exactly what "this" is.

    Since you think you have a problem inserting CHR(0),
    why don't you run a very,very, very simple test & try inserting a single CHR(0) character & tell us the results?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    Hi Anacedent, thanks for the reply

    I ran the following sql statement from VB code:-
    sql = "INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','test','test','',2057,'030')"
    That runs fine.

    I then ran the following (only thing change is replacing one of the fields with chr$(0).)
    Sql = "INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','" & Chr$(0) & "','test','',2057,'030')"

    That seems to indicate that the problem is inserting a chr$(0). Just to take it one stage further I tried inserting a chr$(0) in the middle of an otherwise valid value (which is closer to what I'm actually trying to achieve):-
    Sql = "INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','te" & Chr$(0) & "st','test','',2057,'030')"
    That doesn't run either.

    In both the failures the error message is 'quoted string not properly terminated'. I'm guessing that what's happening is that only the sql up to the chr$(0) is being interpreted and everything else is being dropped - so I'd lose the closing quote, any subsequent field values and the closing bracket. One of the parms for the SQLExecDirect function call is the length of the sql string and you can pass in a control value to indicate that it's a null terminated string but I'm passing in the actual length.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What we have here is a failure to communicate!

    The SQL language has a "CHR()" function.

    I have NO idea what you are doing with extra dollar sign [CHR$(0)] and ampersand characters[ & CHR$(0)]; but AFAIK they are NOT valid SQL syntax.

    Take the actual text string that you think is valid SQL & CUT from what ever environment you are developing your code & then past into SQL*Plus.

    It will then point to where the syntax is wrong.

    Simply put you are NOT constructing valid SQL & Oracle is reporting that fact.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2007
    Posts
    5
    What we have here is a failure to communicate!
    This is how he wants it, this is how he gets it. (Great movie )

    I'm building the SQL string up in VB and then issuing it to Oracle using the SQLExecDirect api call (from the ODBC32 API). The CHR$(0) is vbcode rather than Oracle SQL code but I'm concatenating it into the string so what Oracle should be seeing is something like:-
    INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','te~st','test','',2057,'030')
    where ~ is the ansii 0 null character. I assume CHR(0) has the same meaning when issued directly into Oracle but I'm a SQLServer man normally so that might be a bad assumption.

    As I said, this is working fine on SQLServer and ISeries so I don't think the problem is with the api function call but I can't be sure.

    By the way, I've found a way of getting round it for now by creating a prepared statement using the SQLPrepare API but that then fails on SQLServer. I can just detect the type of server and use the apropriate method but I'd rather use a consistent approach if possible.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As a test, can you turn the insert statement into a parameterized command object, and see if ADO's parameterization will take care of the quoting for you? You may even find that this solution works a little better performance wise, as it will cut down on the number of times the database engine (Both Oracle and SQL Server) need to parse and compile the statement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A binary zero in almost any processor indicates an end of line. What you are passing to oracle is not

    INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','<binary zero>','test','',2057,'030')

    It is

    INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test','

    Which is an invalid string. If you want to handle both types of databases, use the ansi standard for handling a null field.

    Sql = "INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test',null,'test',null,2057,'030')"

    Which will be understanded by both SqlServer and Oracle and Db2 and just about any other ansi standard database server.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Oct 2007
    Posts
    5
    Thanks for the replies, guys.

    McCrowley, that's basically what I've done with the call to SQLPrepare instead of SQLExecDirect execpt that I'm using the ODBC API instead of ADO and it does work (thought it then stops working on SQLServer ). It's part of a change to a major product which is written entirely using ODBC API's so moving it to use ADO instead would be a major undertaking but we're reaching the point where we might bite that bullet. The fact that SQLPrepare works but SQLExecDirect doesn't implies that the problems is with the API over Oracle rather than Oracle itself so it looks like we're going to have to switch.

    beilstwh, I understand what you're saying but unfortunately I have a genuine need to store a character 0 in the string data I'm storing. I don't want to store an db null, I want to store a null character as part of larger text.

    For now the problem's been put on the backburner while management decide on a way forward but thatnks for everyones input.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Actually, one way to do it might be

    Sql = "INSERT INTO TB_6PO.TBPTDZ (TDZNAM, TDZCOM, TDZCAP, TDONAM, TDWLID, TDZEXT) VALUES ('test',Chr(0),'test','',2057,'030')"

    Which would be correctly interpereted by oracle and would insert a binary zero.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Oct 2007
    Posts
    5
    Beilstwh, The data's coming in as a variable so it's not quite that simple but actually there's no reason I can't parse it and do a substitution . I'll float it as a possible approach (at this point management are still thrashing it around). Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •