|
[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]
|
Re: [PLUG] Perl/MySQL conundrum
|
On Mon, 26 Jan 2004 beldon@speakeasy.net wrote:
> Here is the generates SQL.
>
> INSERT INTO main (msgtime, servername, logtype, msgcomp, msgid, msgtype, msgsrc, msgstr) VALUES ('Thu Jan 22 15:37:06 2004', 'EGLDAT02', 'Application', 'EGLDAT02', '0', 'ERROR', 'IWRcer2\:IWReqMan', " EGLDAT02\, 4540\(688\)\, 2004\/01\/22 15\:37\:06\.284\; Error\: 4294967255\, Generic\; Process ID\: 4540\; Thread\: 2184\; Host\: EGLDAT02\; Text\: AS\_ValidateTicket2\: Invalid ticket\.\; BackTraceInfo\: 1\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\authsession\.cpp\; Line\#\:1289\; Desc\.\:AS\_ValidateTicket2\: Invalid ticket\.\; 2\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\authsession\.cpp\; Line\#\:1384\; Desc\.\:validateTicket2\; 3\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\accmansession\.cpp\; Line\#\:48\; Desc\.\:Failed to create AuthSession object\.\; 4\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\sessionmanager\.cpp\; Line\#\:346\; Des
> c\.\:Unable to get auth session\.\; 5\. File Name\: Y\:\\src\\iwr\\IWR\\RequestManager\\RqMgrServices\\imcgiauthenticator\.cpp\; Line\#\:86\; Desc\.\:IMCGIAuthenticator\:\:authenticateUser\; ");,
>
> > -----Original Message-----
> > From: Walt Mankowski [mailto:waltman@pobox.com]
> > Sent: Monday, January 26, 2004 07:54 PM
> > To: plug@lists.phillylinux.org
> > Subject: Re: [PLUG] Perl/MySQL conundrum
> >
> > On Mon, Jan 26, 2004 at 07:45:03PM +0000, beldon@speakeasy.net wrote:
> > > I am working on a Perl script which takes Windows event logs, parses them, and puts the information into a MySQL database. The Perl script runs on Windows and the MySQL database is on a SuSE Linux box, and I'm using an ODBC connection to the MySQL database. I've defined all of the fields I need and have evrything working...except one thing.
> > >
> > > One of the fields is a Text field in MySQL. I keep getting an error because of the odd characters in the free-form text. I have tried everything I could personally think of (like going through each line one character at a time to scrub out the offending characters), as well as the Perl cookbook solution which goes like this:
> > >
> > > $msgstr =~ s/([\'\"])/\\$1/g;
> > >
> > > All to no avail. What I get is:
> > >
> > > Error: [1064] [2] [0] "[TCX][MyODBC]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' SWID=20040124230026169EGLDAT02PID3984TID4820 - EGLDAT02, 1936"
> > >
> > > For the record, here is what I'm trying to insert into the Text field specific to the above error, but it could be just about anything:
> > >
> > > SWID=20040124230026169EGLDAT02PID3984TID4820 - EGLDAT02, 1936(c4c), 2004/01/24 23:00:46.293; Error: 0, Generic; Process ID: 1936; Thread: 3148; Host: EGLDAT02; Text: Unknown error while executing report d:\program files\cognos\cer2\published reports\ACSC Reports - Architect\apps\Master Gaming Report (Summary by Game).imr.; BackTrace Info: 1. File Name: Y:\src\iwr\IWR\Dispatchers\irsdispatchers\irsinstance.cpp; Line#:1180; Desc.:Unknown error while executing report d:\program files\cognos\cer2\published reports\ACSC Reports - Architect\apps\Master Gaming Report (Summary by Game).imr.;
> > >
> > > Any help would be much appreciated.
> >
> > Since it says you have an SQL syntax error, could you show us the code
> > you're using so we can make sure it isn't? :)
> >
> > Walt
> >
Hi Beldon,
Firstly, you should avoid using double quotas in SQL. Mysql is
particularly forgiving in this case, but other RDMS's are not. Generally,
SQL requires single quotes around character data.
Given that the string contained in the error message doesn't appear in the
query you are trying to insert above, and without actually seeing your
perl code, I get the feeling that what you think you are trying to insert
and what you are actually trying to insert are two different things.
I noticed an extra trailing comma in there, so I think something like this
is happening:
INSERT INTO main (msgtime, servername, logtype, msgcomp, msgid, msgtype, msgsrc, msgstr) VALUES ('Thu Jan 22 15:37:06 2004', 'EGLDAT02', 'Application', 'EGLDAT02', '0', 'ERROR', 'IWRcer2\:IWReqMan', " EGLDAT02\, 4540\(688\)\, 2004\/01\/22 15\:37\:06\.284\; Error\: 4294967255\, Generic\; Process ID\: 4540\; Thread\: 2184\; Host\: EGLDAT02\; Text\: AS\_ValidateTicket2\: Invalid ticket\.\; BackTraceInfo\: 1\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\authsession\.cpp\; Line\#\:1289\; Desc\.\:AS\_ValidateTicket2\: Invalid ticket\.\; 2\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\authsession\.cpp\; Line\#\:1384\; Desc\.\:validateTicket2\; 3\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\accmansession\.cpp\; Line\#\:48\; Desc\.\:Failed to create AuthSession object\.\; 4\. File Name\: Y\:\\src\\iwr\\IWR\\SessionManager\\sessionmanager\.cpp\; Line\#\:346\; Des
c\.\:Unable to get auth session\.\; 5\. File Name\: Y\:\\src\\iwr\\IWR\\RequestManager\\RqMgrServices\\imcgiauthenticator\.cpp\; Line\#\:86\; Desc\.\:IMCGIAuthenticator\:\:authenticateUser\; ");,
' SWID=20040124230026169EGLDAT02PID3984TID4820 - EGLDAT02, 1936(c4c), 2004/01/24 23:00:46.293;';
You also probably only need to use the following pattern to escape special
characters:
$msgstr =~ s/([\'\"\\])/\\$1/g;
HTH,
Phil
___________________________________________________________________________
Philadelphia Linux Users Group -- http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion -- http://lists.phillylinux.org/mailman/listinfo/plug
|
|