Strange errors with OracleLoader

[varunb]

Hi,

The OraclelLoader class is giving me some very strange errors. The error message is completely misleading. I believe the problem is somewhere else in the driver while the error reported is wrong.

In my case i have the followng lines of code (cut down version)

//loader.SetValue("GEOIP_CITY_NAME", entry.Geoip.NullSafeGet(a => a.CityName));
loader.SetValue("GEOIP_COUNTRY_CODE2", entry.Geoip.NullSafeGet(a => a.CountryCode2));
loader.SetValue("GEOIP_COUNTRY_NAME", entry.Geoip.NullSafeGet(a => a.CountryName));
loader.SetValue("GEOIP_LATITUDE", entry.Geoip.NullSafeGet(a => a.Latitude));
loader.SetValue("GEOIP_LOCATION", null);
loader.SetValue("GEOIP_LONGITUDE", entry.Geoip.NullSafeGet(a => a.Longitude));
//loader.SetValue("GEOIP_REAL_REGION_NAME", entry.Geoip.NullSafeGet(a => a.RealRegionName));

If I add back the commented out code, it breaks. If I leave it commented it works.

Error I get

Additional information: ORA-01461: can bind a LONG value only for insert into a LONG column

The GEOIP_CITY_NAME is 64 byte varchar2 and GEOIP_REAL_REGION_NAME is 128 byte varchar2. The values I am passing is no more then 20 characters. Please help! There is a definite bug in your code

[varunb]

Ok, I have further details. The problem happen in the following function when both
QUERY_NETWORKIDLIST and QUERY_RESULT are null. The columns that are inserted get shifted to right by 1 column place and i start seeing errors.

Code: Select all

public void WriteToStorage(IList<ApacheLog> logEntries)
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                connection.Open();
                using (var loader = new OracleLoader(string.Format("{0}.EVENT", _trackingSchemaName), connection))
                {
                    loader.Error += new OracleLoaderErrorEventHandler(loader_Error);
                    loader.CreateColumns();
                    loader.Open();

                    foreach (var entry in logEntries)
                    {
                        loader.SetValue("ID", entry.Id);

                        loader.SetValue("USER_AGENT", entry.UseragentString.TruncateLongString(500));
                        loader.SetValue("CLIENTIP", entry.Clientip);
                        loader.SetValue("TRUECLIENTIP", entry.Trueclientip);
                        loader.SetValue("TRACKING_SERVER", entry.Host);

                        loader.SetValue("ITEMSYSTEM", entry.ItemSystem);
                        loader.SetValue("ITEMTYPE", entry.Itemtype);
                        loader.SetValue("LOGFILE_PATH", entry.Path);

                        loader.SetValue("DATE_CREATED", entry.TimestampUTC.ToLocalTime());
                        if (entry.Query != null)
                        {
                            loader.SetValue("ITEMNETWORKID", entry.Query.NetworkId);
                            loader.SetValue("QUERY_DEVICETYPE", entry.Query.DeviceType);
                            loader.SetValue("QUERY_DOMAINNAME", entry.Query.DomainName);

                            if (entry.Query.EventTypes != null)
                            {
                                if (entry.Query.EventTypes.Count == 1)
                                {
                                    loader.SetValue("QUERY_EVENTTYPE", entry.Query.EventTypes[0]);
                                }
                                else
                                {
                                }
                            }

                            loader.SetValue("QUERY_ITEM", entry.Query.Item);
                            loader.SetValue("QUERY_MEDIATYPE", entry.Query.MediaType);
                            loader.SetValue("QUERY_NETWORKIDLIST", entry.Query.NetworkIdList);
                            loader.SetValue("QUERY_RESULT", entry.Query.Result);

                            loader.SetValue("QUERY_SEARCHITEMSPERPAGE", entry.Query.SearchItemsPerPage);
                            loader.SetValue("QUERY_SEARCHRESULTCOUNT", entry.Query.SearchResultCount);

                            if (!string.IsNullOrEmpty(entry.Query.SearchTS))
                            {
                                var timestamp = SafeParseTimestamp(entry.Query.SearchTS);
                                if (timestamp.HasValue)
                                {
                                    loader.SetValue("QUERY_SEARCHTS", timestamp);
                                }
                                else
                                {
                                    Console.WriteLine("Count not parse timestamp");
                                }
                            }
                            else
                            {
                                loader.SetValue("QUERY_SEARCHTS", null);
                            }
                            loader.SetValue("QUERY_SEQ", entry.Query.Seq);

                            loader.SetValue("QUERY_SERVERNAME", entry.Query.ServerName);
                            loader.SetValue("QUERY_SESSIONID", entry.Query.SessionId);
                            loader.SetValue("QUERY_USERID", entry.Query.UserId);
                            if (!string.IsNullOrEmpty(entry.Query.SaleId))
                            {
                                loader.SetValue("QUERY_SALE_ID", entry.Query.SaleId.TruncateLongString(40));
                            }
                            loader.SetValue("QUERY_VISITORID", entry.Query.VisitorId);
                        }

                        loader.SetValue("REFERRER_DOMAIN", entry.ReferrerDomain);
                        loader.SetValue("RESPONSE", entry.Response);
                        loader.SetValue("EVENT_TS", entry.TimestampUTC.ToLocalTime());

                        if (entry.Geoip != null)
                        {
                            loader.SetValue("GEOIP_CITY_NAME", entry.Geoip.CityName.TruncateLongString(8));
                            //loader.SetValue("GEOIP_COUNTRY_CODE2", entry.Geoip.CountryCode2);
                            loader.SetValue("GEOIP_COUNTRY_NAME", entry.Geoip.CountryName);
                            loader.SetValue("GEOIP_LATITUDE", entry.Geoip.Latitude);
                            //loader.SetValue("GEOIP_LOCATION", entry.Geoip.Location); // This is null
                            loader.SetValue("GEOIP_LONGITUDE", entry.Geoip.Longitude);
                            //loader.SetValue("GEOIP_REAL_REGION_NAME", entry.Geoip.RealRegionName.TruncateLongString(3));
                        }

                        loader.SetValue("VERB", entry.Verb.TruncateLongString(3));
                        

                        //loader.SetValue("SPOT_ID", entry.TimestampUTC); // This should be null
                        //loader.SetValue("REDBOOK_CODE_LEGACY") = null);  // This should be null
                        loader.SetValue("REDBOOK_CODE", entry.RedbookCode);

                        if (entry.Useragent != null)
                        {
                            loader.SetValue("UA_NAME", entry.Useragent.Name);
                            loader.SetValue("UA_VER_MINOR", entry.Useragent.Minor);
                            loader.SetValue("UA_VER_MAJOR", entry.Useragent.Major);
                            loader.SetValue("UA_PATCH", entry.Useragent.Patch);
                            loader.SetValue("UA_BUILD", entry.Useragent.Build);
                            loader.SetValue("UA_OS", entry.Useragent.Os);
                            loader.SetValue("UA_OS_NAME", entry.Useragent.OsName);
                            loader.SetValue("UA_OS_VER_MAJOR", entry.Useragent.OsMajor);
                            loader.SetValue("UA_OS_VER_MINOR", entry.Useragent.OsMinor);
                            loader.SetValue("UA_DEVICE", entry.Useragent.Device.NullSafeGet(a => a.TruncateLongString(100)));

                        }

                        if (entry.ReferrerMap != null)
                        {
                            loader.SetValue("REFERRER_DRIVER", entry.ReferrerMap.WebTrendsDriverParam);
                        }

                        loader.NextRow();
                    }

                    loader.Close();
                    connection.Close();
                }
                
            }

        }

[varunb]

Some more info. By process of elimination i know that if I change the following line of code
from

Code: Select all

loader.SetValue("QUERY_RESULT", entry.Query.Result);

to

Code: Select all

loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");

it all works fine. Very very strange bug. Please help for a proper solution as I cannot deploy this hack

[Pinturiccio]

Please create and send us a complete test sample with corresponding DDL/DML scripts for reproducing the issue.

[varunb]

Done, please get back ASAP

[Pinturiccio]

Thank you for the test project. However, we could not reproduce the issue in our environment. Your sample runs successfully even if we replace:

Code: Select all

loader.SetValue("QUERY_RESULT", entry.Query.Result ?? "-");

with

Code: Select all

loader.SetValue("QUERY_RESULT", entry.Query.Result);

Please tell us what should we change in your sample to reproduce the issue. Please also tell us the version of your Oracle server.

[varunb]

Do you get a null in the database for the QUERY_RESULT field? This is quite strange as I can reproduce the error on my end

Thanks
Varun

[Pinturiccio]

Do you get a null in the database for the QUERY_RESULT field?

Yes, we have null values in the QUERY_RESULT column.

Please provide us Oracle server version and dotConnect for Oracle version.

[varunb]

Devart version 5.0.1256.0
Oracle version: 11g

[varunb]

HI Guys,

Any update on this?

Thanks

[Pinturiccio]

Sorry for the delay. We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[varunb]

Guys,

Any update? It's been open for a while now and we would really like to get it resolved

Thanks

[Pinturiccio]

We are still investigating the issue. We will post here when we get any results, but we can't tell any timeframe at the moment.

[Pinturiccio]

We have fixed the bug with inserting null values via OracleLoader in the Direct mode to a UTF8 server. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

New build of dotConnect for Oracle 9.2.205 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34950