Hi,
Please go easy on me, I am new to this and still learning.
I use a VB program (I think) to generate output files for mysql queries. The program processes a .txt file with 2 columns - Filename and Sql. Example below:
FileName Sql
Apps_AgentOutcomes_Daily select distinct a.stampedtime, a.account_ref, a.name, c.tel_num_1, c.email_addr, c.call_data1 as 'Contact_JobRole', c.call_data2 as 'Company Name', a.call_result, b.activity_name as 'Outcome', c.call_data3 as 'What are the roles', c.call_data4 as 'Experience of Apprenticeships', c.call_data5 as 'LBMdescription', c.call_data6 as 'Recruit 16-18 year olds', c.call_data7 as 'Information about the Apprenticeships process', c.call_data8 as 'Anyone in mind', c.call_data9 as 'Information about frameworks', c.call_data10 as 'Information about how Apprenticeships will work for them', c.call_data11 as 'Age restrictions for the role', c.call_data12 as 'Industry sector', c.call_data13 as 'Age bracket that they are in', c.call_data14 as 'EmpBand', c.call_data15 as 'How many people in mind', c.sms_number as 'Interested in Newsletter', c.sex as 'Number of employees', d.text_1 as 'Experience Notes', d.text_2 as 'Notes', d.text_3 as 'Aware of appropriate training for the roles', d.text_4 as 'Frameworks notes', d.text_5 as 'Not Interested notes', d.text_6 as 'What type of job role are they in', d.text_7 as 'Are they in any formal training', d.text_8 as 'When are you looking to take an Apprentice on', a.user_id, a.campaign_desc as 'Campaign', a.list from dlr_calls_hist a left outer join activity_hist b on b.call_reference=a.call_ref inner join dlr_accounts_hist c on c.account_id=a.account_id left join configure.dlr_accounts_ext_1 d on d.account_id=a.account_id where a.campaign_desc = 'AppsLondon' and a.call_ref = (select max(call_ref) from dlr_calls_hist b where a.account_id=b.account_id) and a.stampeddate = DATE_SUB(curdate(), INTERVAL 1 DAY) and b.activity_code = 131;
The resulting output should be 1 row for each record and it always has been until I started reporting on a notes field which can have LOADS of charachters. What this does now is that if there are a lot of charachters in the notes field it starts to put the values for the one record on more than one row - making my whole output file useless. When I run the select on its owm in mysql query browser and save results it is all fine so I think it's this VB application that needs amending somehow.
Anyone have any tips on how to manage a large number of characters in the output file? I need to get back to having one record on one row.
If I have missed any information out that you might need to offer assistance please let me know. I am very new to this so would appreciate some pointers in how to resolve and formulate my requests better.
Many thanks in advance,
Ben
Config file for application is:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
</configSections>
<connectionStrings>
<add name="RostrvmConnectionString" connectionString="Dsn=XXXXXX;uid=XXXXXX" providerName="System.Data.Odbc" />
</connectionStrings>
<appSettings>
<add key="FilePath" value="\\bridata01\Common\Rostrvm\AutomatedSql\"/>
<add key="OutputFilePath" value="\\bridata01\Common\Rostrvm\Output\"/>
</appSettings>
<!-- log4Net settings-->
<log4net>
<!-- This appender will log to a rolling log file - when it reaches 10 files of 1MB each the oldest file will be deleted -->
<appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="Log\\Application.log" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="1MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%-5level] - %message [%class.%method - %line]%newline" />
</layout>
</appender>
<appender name="BeehiveLogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="Log\\BeehiveApplication.log" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="1MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%-5level] - %message [%class.%method - %line]%newline" />
</layout>
</appender>
<appender name="BeehiveAppender" type="log4net.Appender.AdoNetAppender">
<!-- Make sure it writes immediately -->
<bufferSize value="0" />
<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<connectionString value="data source=LOCALHOST;initial catalog=BeehiveClient;integrated security=false;persist security info=True;User ID=BeehiveClientUser;Password=ii01q£Hhq;" />
<commandText value="INSERT INTO CLIENTLOG (MachineName, ApplicationName, Datetime, Logger, LogLevel, Message, Exception, ExecutableName, Thread, SourceClass, SourceMethod, SourceLine) VALUES (@MachineName, @ApplicationName, @Datetime, @Logger, @LogLevel, @Message, @Exception, @ExecutableName, @Thread, @SourceClass, @SourceMethod, @SourceLine)" />
<parameter>
<parameterName value="@MachineName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{log4net:HostName}" />
</layout>
</parameter>
<parameter>
<parameterName value="@ApplicationName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="Sky.ImportFulfilmentFiles_Dev" />
</layout>
</parameter>
<parameter>
<parameterName value="@Datetime" />
<dbType value="DateTime" />
<layout type="log4net.Layout.RawTimeStampLayout" />
</parameter>
<parameter>
<parameterName value="@Logger" />
<dbType value="String" />
<size value="100" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%logger" />
</layout>
</parameter>
<parameter>
<parameterName value="@LogLevel" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%level" />
</layout>
</parameter>
<parameter>
<parameterName value="@Message" />
<dbType value="String" />
<size value="2048" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%message" />
</layout>
</parameter>
<parameter>
<parameterName value="@Exception" />
<dbType value="String" />
<size value="4000" />
<layout type="log4net.Layout.ExceptionLayout" />
</parameter>
<parameter>
<parameterName value="@ExecutableName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%appdomain" />
</layout>
</parameter>
<parameter>
<parameterName value="@Thread" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%thread" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceClass" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%class" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceMethod" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%method" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceLine" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%line" />
</layout>
</parameter>
</appender>
<appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<!-- Name a specific Logger though and that information will only be logged by the one specific instance that was created in your code-->
<logger name="BeehiveLogger">
<level value="INFO" />
<!--<appender-ref ref="BeehiveAppender" />-->
<appender-ref ref="RollingLogFileAppender" />
<appender-ref ref="BeehiveLogFileAppender" />
<appender-ref ref="ConsoleAppender" />
</logger>
<logger name="DevLogger">
<level value="ALL" />
<!--<appender-ref ref="BeehiveAppender" />-->
<appender-ref ref="RollingLogFileAppender" />
<appender-ref ref="ConsoleAppender" />
</logger>
</log4net>
</configuration>
Please go easy on me, I am new to this and still learning.
I use a VB program (I think) to generate output files for mysql queries. The program processes a .txt file with 2 columns - Filename and Sql. Example below:
FileName Sql
Apps_AgentOutcomes_Daily select distinct a.stampedtime, a.account_ref, a.name, c.tel_num_1, c.email_addr, c.call_data1 as 'Contact_JobRole', c.call_data2 as 'Company Name', a.call_result, b.activity_name as 'Outcome', c.call_data3 as 'What are the roles', c.call_data4 as 'Experience of Apprenticeships', c.call_data5 as 'LBMdescription', c.call_data6 as 'Recruit 16-18 year olds', c.call_data7 as 'Information about the Apprenticeships process', c.call_data8 as 'Anyone in mind', c.call_data9 as 'Information about frameworks', c.call_data10 as 'Information about how Apprenticeships will work for them', c.call_data11 as 'Age restrictions for the role', c.call_data12 as 'Industry sector', c.call_data13 as 'Age bracket that they are in', c.call_data14 as 'EmpBand', c.call_data15 as 'How many people in mind', c.sms_number as 'Interested in Newsletter', c.sex as 'Number of employees', d.text_1 as 'Experience Notes', d.text_2 as 'Notes', d.text_3 as 'Aware of appropriate training for the roles', d.text_4 as 'Frameworks notes', d.text_5 as 'Not Interested notes', d.text_6 as 'What type of job role are they in', d.text_7 as 'Are they in any formal training', d.text_8 as 'When are you looking to take an Apprentice on', a.user_id, a.campaign_desc as 'Campaign', a.list from dlr_calls_hist a left outer join activity_hist b on b.call_reference=a.call_ref inner join dlr_accounts_hist c on c.account_id=a.account_id left join configure.dlr_accounts_ext_1 d on d.account_id=a.account_id where a.campaign_desc = 'AppsLondon' and a.call_ref = (select max(call_ref) from dlr_calls_hist b where a.account_id=b.account_id) and a.stampeddate = DATE_SUB(curdate(), INTERVAL 1 DAY) and b.activity_code = 131;
The resulting output should be 1 row for each record and it always has been until I started reporting on a notes field which can have LOADS of charachters. What this does now is that if there are a lot of charachters in the notes field it starts to put the values for the one record on more than one row - making my whole output file useless. When I run the select on its owm in mysql query browser and save results it is all fine so I think it's this VB application that needs amending somehow.
Anyone have any tips on how to manage a large number of characters in the output file? I need to get back to having one record on one row.
If I have missed any information out that you might need to offer assistance please let me know. I am very new to this so would appreciate some pointers in how to resolve and formulate my requests better.
Many thanks in advance,
Ben
Config file for application is:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
</configSections>
<connectionStrings>
<add name="RostrvmConnectionString" connectionString="Dsn=XXXXXX;uid=XXXXXX" providerName="System.Data.Odbc" />
</connectionStrings>
<appSettings>
<add key="FilePath" value="\\bridata01\Common\Rostrvm\AutomatedSql\"/>
<add key="OutputFilePath" value="\\bridata01\Common\Rostrvm\Output\"/>
</appSettings>
<!-- log4Net settings-->
<log4net>
<!-- This appender will log to a rolling log file - when it reaches 10 files of 1MB each the oldest file will be deleted -->
<appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="Log\\Application.log" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="1MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%-5level] - %message [%class.%method - %line]%newline" />
</layout>
</appender>
<appender name="BeehiveLogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="Log\\BeehiveApplication.log" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="1MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%-5level] - %message [%class.%method - %line]%newline" />
</layout>
</appender>
<appender name="BeehiveAppender" type="log4net.Appender.AdoNetAppender">
<!-- Make sure it writes immediately -->
<bufferSize value="0" />
<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<connectionString value="data source=LOCALHOST;initial catalog=BeehiveClient;integrated security=false;persist security info=True;User ID=BeehiveClientUser;Password=ii01q£Hhq;" />
<commandText value="INSERT INTO CLIENTLOG (MachineName, ApplicationName, Datetime, Logger, LogLevel, Message, Exception, ExecutableName, Thread, SourceClass, SourceMethod, SourceLine) VALUES (@MachineName, @ApplicationName, @Datetime, @Logger, @LogLevel, @Message, @Exception, @ExecutableName, @Thread, @SourceClass, @SourceMethod, @SourceLine)" />
<parameter>
<parameterName value="@MachineName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%property{log4net:HostName}" />
</layout>
</parameter>
<parameter>
<parameterName value="@ApplicationName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="Sky.ImportFulfilmentFiles_Dev" />
</layout>
</parameter>
<parameter>
<parameterName value="@Datetime" />
<dbType value="DateTime" />
<layout type="log4net.Layout.RawTimeStampLayout" />
</parameter>
<parameter>
<parameterName value="@Logger" />
<dbType value="String" />
<size value="100" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%logger" />
</layout>
</parameter>
<parameter>
<parameterName value="@LogLevel" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%level" />
</layout>
</parameter>
<parameter>
<parameterName value="@Message" />
<dbType value="String" />
<size value="2048" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%message" />
</layout>
</parameter>
<parameter>
<parameterName value="@Exception" />
<dbType value="String" />
<size value="4000" />
<layout type="log4net.Layout.ExceptionLayout" />
</parameter>
<parameter>
<parameterName value="@ExecutableName" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%appdomain" />
</layout>
</parameter>
<parameter>
<parameterName value="@Thread" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%thread" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceClass" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%class" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceMethod" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%method" />
</layout>
</parameter>
<parameter>
<parameterName value="@SourceLine" />
<dbType value="String" />
<size value="10" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%line" />
</layout>
</parameter>
</appender>
<appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<!-- Name a specific Logger though and that information will only be logged by the one specific instance that was created in your code-->
<logger name="BeehiveLogger">
<level value="INFO" />
<!--<appender-ref ref="BeehiveAppender" />-->
<appender-ref ref="RollingLogFileAppender" />
<appender-ref ref="BeehiveLogFileAppender" />
<appender-ref ref="ConsoleAppender" />
</logger>
<logger name="DevLogger">
<level value="ALL" />
<!--<appender-ref ref="BeehiveAppender" />-->
<appender-ref ref="RollingLogFileAppender" />
<appender-ref ref="ConsoleAppender" />
</logger>
</log4net>
</configuration>