Обсуждение: Parser: parse error - please help...

All,

I am having a problem with an INSERT onto a table I have created. First
off, here is the table:

---

CREATE TABLE reg ("customer number" SERIAL PRIMARY KEY, "company name"
VARCHAR(50) NULL, address VARCHAR(100) NULL, city VARCHAR(50) NULL,
state VARCHAR(2) NULL, zip VARCHAR(50) NULL, phone VARCHAR(50) NULL,
alt_phone VARCHAR(50) NULL, fax VARCHAR(50) NULL, alt_fax VARCHAR(50)
NULL, iaccess BOOL NULL DEFAULT FALSE, browser VARCHAR(50) NULL,
provider VARCHAR(50) NULL, software VARCHAR(50) NULL, sversion
VARCHAR(10) NULL, ynintegrated BOOL NULL DEFAULT FALSE, ynnewtest BOOL
NULL DEFAULT FALSE, login VARCHAR(50) NULL, password VARCHAR(50) NULL,
txtnetid VARCHAR(11) NULL, alias_1 VARCHAR(50) NULL, alias_2 VARCHAR(50)
NULL, url VARCHAR(255) NULL, approval_password VARCHAR(20) NULL,
ynauthalert BOOL NULL DEFAULT FALSE, loi_date VARCHAR(50) NULL,
contract_eff VARCHAR(50) NULL, contract_term VARCHAR(50) NULL, term_code
VARCHAR(255) NULL, main_eff VARCHAR(50) NULL, main_term VARCHAR(50)
NULL, live_eff VARCHAR(50) NULL, renewal_eff VARCHAR(50) NULL, cs_rep
VARCHAR(50) NULL, txtactmanager VARCHAR(50) NULL, txtsalesrep
VARCHAR(50) NULL, txtprojmanager VARCHAR(50) NULL, modem_number
VARCHAR(50) NULL, dbc_users VARCHAR(50) NULL, intbillableusers INT4
NULL, country VARCHAR(50) NULL, mmowarning TEXT NULL, mmositefileflags
TEXT NULL, verified VARCHAR(50) NULL, buildtimedate VARCHAR(50) NULL,
ynhipaaba BOOL NULL DEFAULT FALSE);

---

I have a process which loops and inserts multiple records into this
table. After processing approximately 200 records, it attempts to do the
following insert:

---

INSERT INTO reg("customer number", "company name", address, city, state,
zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider,
software, sversion, ynintegrated, ynnewtest, login, password, txtnetid,
alias_1, alias_2, url, approval_password, ynauthalert, loi_date,
contract_eff, contract_term, term_code, main_eff, main_term, live_eff,
renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager,
modem_number, dbc_users, intbillableusers, country, mmowarning,
mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855',
'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602)
123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true,
'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855',
DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT,
false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT,
DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246',
'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
  '0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);

---

This generates the following error (from within VB):

Error -2147467259 - ERROR:  parser: parse error at or near "09" at
character 847

---

When I try the above insert in psql, I get a prompt like:

dbname'>

Here, if I input ' followed by RETURN, the prompt changes to:

dbname(>

Input ), the prompt changes to:

dbname->

Input ;, the error occurs:

ERROR:  parser: parse error at or near "09" at character 847

---

Something is obviously wrong - but what it is, I cannot tell. I have
tried to simplify the problem by comparing prior inserts to this one, to
see what the differences are, to no avail. It is like it is missing a
paren or a quote, or has an extra - but that doesn't seem to be the case.

Am I missing something obvious, can somebody help?

Thank you,

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

On Mon, Dec 22, 2003 at 04:24:52PM -0700, Andrew Ayers wrote:

> INSERT INTO reg("customer number", "company name", address, city, state,
> zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider,
> software, sversion, ynintegrated, ynnewtest, login, password, txtnetid,
> alias_1, alias_2, url, approval_password, ynauthalert, loi_date,
> contract_eff, contract_term, term_code, main_eff, main_term, live_eff,
> renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager,
> modem_number, dbc_users, intbillableusers, country, mmowarning,
> mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855',
> 'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602)
> 123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true,
> 'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855',
> DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT,

Note here:                                                    ^^

> false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT,
> DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246',
> 'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
>   '0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);

You have an escaped ', which will make the string literal continue till
the next '.  You should really be escaping your \ and ' when they are
embedded in data.  Consider something like "Joh\n"; it will insert Joh
followed by a newline.

Also, an URL with \ is quite weird ... I think they like / best.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

On Mon, Dec 22, 2003 at 04:24:52PM -0700, Andrew Ayers wrote:

> DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT,
> false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT,

The \ at the end off the URL in the first line here is escaping the '
after it, so the string which should consist of just the URL is being
taken to include everything up to the next ' , the one at the beginning
of the date. Then the date itself appears to fall outside quotes, hence
Pg tries to parse it, hence parse error at 09.

The \ in the URL should be / - this goes for all \ in all URL.

Richard