Re: Using parameters with ADONetAppender and FireBird

[Frode Breimo]

Thank you, thank you, thank you :)

Your first suggestion of putting the SQL in a conversionPattern did it.

Thanks again, you've made my day!
Frode

Ron Grabowski wrote:

Are you able to get this style of INSERT statement working:

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
<connectionString value="System.Data.Odbc.OdbcConnection, System.Data,
version=1.0.3300.0, publicKeyToken=b77a5c561934e089, culture=neutral"
/>
<layout type="log4net.Layout.PatternLayout">
 <conversionPattern value="INSERT INTO TESTTABLE (TESTSTRING) VALUES
('%message')" />
</layout>

</appender>

Yes, I know that is open to sql injection.

Have you tried using the Firebird provider?

http://firebird.sourceforge.net/index.php?op=devel&sub=netprovider

According to this page:

http://tinyurl.com/bjrfg
http://firebird.sourceforge.net/index.php?op=devel&sub=netprovider&id=examples

it looks like Firebird may be expecting the placeholders to use an @
sign as their prefix:

INSERT INTO TESTTABLE (TESTSTRING) VALUES (@logText)

Your parameter would look something like this:

<parameter>
 <parameterName value="@logText" />
 <dbType value="String" />
 <size value="4000" />
 <layout type="log4net.Layout.PatternLayout">
  <conversionPattern value="%message" />
 </layout>
</parameter>

Let us know if you're able to get it to work.

--- Frode Breimo <[EMAIL PROTECTED]> wrote:

Hi

I''ve been working for two days now trying to send log information to

my FireBird database using SQL parameters, but I've been unable to find any helpful examples or articles and could really use some help.

For testing purposes I'm just using a very simple table called

TESTTABLE with a single column called TESTSTRING, of type VarChar. I've tried several things but here's what the appender definition in my config file look like now:

   <appender name="myODBCAppender"
type="log4net.Appender.ADONetAppender" >
       <bufferSize value="1"/>
       <param name="Threshold" value="ERROR" />

<param name="ConnectionType" value="System.Data.Odbc.OdbcConnection, System.Data, version=1.0.3300.0, publicKeyToken=b77a5c561934e089, culture=neutral" /> <param name="ConnectionString" value="DRIVER=Firebird/Interbase(r)

driver;UID=sysdba;PWD=masterkey;DBNAME=127.0.0.1:c:\\mbos\\mtbs1604v.gdb"

/>

<param name="CommandText" value="INSERT INTO TESTTABLE (TESTSTRING) VALUES (:logText)" />

       <param name="Parameter">
           <param name="ParameterName" value="logText" />
           <param name="DbType" value="String" />
           <param name="Layout" type="log4net.Layout.PatternLayout">
               <param name="ConversionPattern" value="%P{logText}"
/>
           </param>
       </param>
   </appender>

Using this config I get the following error message when I declare my

logger:

   log4net:ERROR [ADONetAppender] Could not prepare database command

[INSERT INTO TESTTABLE    (TESTSTRING) VALUES (:logText)]
   System.Data.Odbc.OdbcException: ERROR [HY000] Dynamic SQL Error
   SQL error code = -206
   Column unknown
   LOGTEXT
   At line 1, column 45.

If I change the CommandText and use (@logText) instead of (:logText)

I get the following error:

   SQL error code = -104
   Token unknown - line 1, char 44
   @

If I remove the parameter and replace it with a set value everything works fine, so for instance

   <param name="CommandText" value="INSERT INTO TESTTABLE

(TESTSTRING) VALUES ('TestValue')" />

will insert the value "TestValue" into the database, so there's

nothing wrong with the connection.

This should be possible just using the config file, shouldn't it? I don't want to have to create everything programmatically.

I'm totally lost here, and any help would be much appreciated. I'm

using log4net 1.2.0 Beta 8 and FireBird 1.5, the project is a .Net 1.1 console application (for testing purposes, log4net will eventually be used in

our asp.net solutions)


Thank you,
Frode