Backslashes in HTML code

evren · Post by **evren** » Sat 12 Nov 2011 13:51

Hi there,

We are using PostgreSQL Pro 5.50 and linq2SQL in our project. We are trying to import (with a console application using devart's linq2sql) html data from MSSQL Server. However, when importing to postgre, html data have backslashes in it. I have 2 samples at below. So what is the reason? and how can i fix this?

Those backslashes are always at end of the line + they are at the begining as well like you are using tab or space.

MSSQL

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head>
<title>BLABLABLA</title>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">

$(document).ready(function () {	
	$('#nav li').hover(
		function () {
			$('ul', this).slideDown(100);
		}, 
		function () {
			$('ul', this).slideUp(100);			
		}
	);
   $('.slideshow').cycle({
		fx: 'fade' 
	});
});
</script>

POSTGRE

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">\<html xmlns="http://www.w3.org/1999/xhtml"><head>\<title>BLABLABLA</title>\<meta content="text/html; charset=utf-8" http-equiv="Content-Type">\
$(document).ready(function () {\\\$('#nav li').hover(\\\function () {\\\\$('ul', this).slideDown(100);\\\}, \\\function () {\\\\$('ul', this).slideUp(100);\\\\\\}\\);\   $('.slideshow').cycle({\\\fx: 'fade' \\});\});\</script>\

StanislavK · Post by **StanislavK** » Tue 15 Nov 2011 17:47

Could you please specify the following, so that we are able to analyze the issue in more details:
- the types of the columns you are storing the HTML data in;
- the versions of the PostgreSQL and SQL Server servers you are working with;
- the exact way you get data from SQL Server and save them in the PostgreSQL database;
- the exact version (including the build number) of dotConnect for PostgreSQL you are using.

If possible, please send us a small test project. At the moment, we couldn't reproduce the issue in our environment.

evren · Post by **evren** » Wed 16 Nov 2011 16:58

Thank you very much for response.

We found the problem. It is not related with MSSQL at all.

We are using dotConnect for PostgreSQL Pro 5.50.244 and we are using postgre 9.1. We are using Devart Linq (lqml) as well. Db has UTF8 collation. Lqml connects to database with LATIN5 character encoding. TemplateWebsiteLive column is "text".

If we insert single entity, there is no problem with backslashes at all. However, we insert multiple entities with single submitchange, provider puts backslashes for every object. On the other hand if we submitchange in every insert there is no problem at all as well. I give code example and db monitor logs at below.

By the way, we have no problem on single update or multiple updates at all.

I guess multiple inserts with single submitchange should use parameters on insert or dotConnect should generate insert commands with "E''".

NO BACKSLASHES

Code: Select all

            using (var devartModelPostgreSql = new DevartModel())
            {
                devartModelPostgreSql.DeferredLoadingEnabled = false;
                devartModelPostgreSql.PageTemplateMlcs.InsertOnSubmit(new PageTemplateMlc
                                                                          {
                                                                              TemplateId = 1,
                                                                              LanguageId = 1,
                                                                              TemplateWebsiteLive = @"
	
		
	
	
	
"
                                                                          });

                devartModelPostgreSql.SubmitChanges();
            }

LOG

Code: Select all

  16.11.2011 18:43:40 0,000 Execute: SET autocommit=true Complete
  16.11.2011 18:43:40 0,016 Execute: SET datestyle=ISO ; SET client_encoding='LATIN5' Complete
  16.11.2011 18:43:40 0,000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  16.11.2011 18:43:40 0,000 Execute: SET search_path TO public; Complete
  16.11.2011 18:43:40 0,000 Prepare: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:43:40 0,000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:43:40 0,000 Prepare: BEGIN Complete
  16.11.2011 18:43:40 0,015 Execute: BEGIN Complete
  16.11.2011 18:43:40 0,000 Prepare: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:43:40 0,000 Execute: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:43:40 0,000 Commit Complete
  16.11.2011 18:43:40 0,000 Prepare: COMMIT Complete
  16.11.2011 18:43:40 0,000 Execute: COMMIT Complete
  16.11.2011 18:43:40 0,000 Close connection Complete
  16.11.2011 18:43:40 0,000 Connection is returned to pool. Pool has 1 connection(s). Complete

BACKSLASHES

Code: Select all

            using (var devartModelPostgreSql = new DevartModel())
            {
                devartModelPostgreSql.DeferredLoadingEnabled = false;
                devartModelPostgreSql.PageTemplateMlcs.InsertOnSubmit(new PageTemplateMlc
                                                                          {
                                                                              TemplateId = 1,
                                                                              LanguageId = 1,
                                                                              TemplateWebsiteLive = @"
	
		
	
	
	
"
                                                                          });

                devartModelPostgreSql.PageTemplateMlcs.InsertOnSubmit(new PageTemplateMlc
                {
                    TemplateId = 2,
                    LanguageId = 2,
                    TemplateWebsiteLive = @"
	
		
	
	
	
"
                });

                devartModelPostgreSql.SubmitChanges();
            }

LOG

Code: Select all


  16.11.2011 18:42:39 0,000 Execute: SET autocommit=true Complete
  16.11.2011 18:42:39 0,000 Execute: SET datestyle=ISO ; SET client_encoding='LATIN5' Complete
  16.11.2011 18:42:39 0,000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  16.11.2011 18:42:39 0,000 Execute: SET search_path TO public; Complete
  16.11.2011 18:42:39 0,000 Prepare: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:42:39 0,016 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:42:39 0,000 Prepare: BEGIN Complete
  16.11.2011 18:42:39 0,000 Execute: BEGIN Complete
  16.11.2011 18:42:39 0,016 Execute:  INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES (1, 1, '\\\\\\\\\\\\\\\\\\', NULL, NULL, NULL) ; INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES (1, 2, '\\\\\\\\\\\\\\\\\\', NULL, NULL, NULL) ;   Complete
  16.11.2011 18:42:39 0,000 Commit Complete
  16.11.2011 18:42:39 0,000 Prepare: COMMIT Complete
  16.11.2011 18:42:39 0,000 Execute: COMMIT Complete
  16.11.2011 18:42:39 0,015 Close connection Complete
  16.11.2011 18:42:39 0,015 Connection is returned to pool. Pool has 1 connection(s). Complete

NO BACKSLASHES

Code: Select all

            using (var devartModelPostgreSql = new DevartModel())
            {
                devartModelPostgreSql.DeferredLoadingEnabled = false;
                devartModelPostgreSql.PageTemplateMlcs.InsertOnSubmit(new PageTemplateMlc
                                                                          {
                                                                              TemplateId = 1,
                                                                              LanguageId = 1,
                                                                              TemplateWebsiteLive = @"
	
		
	
	
	
"
                                                                          });

                devartModelPostgreSql.SubmitChanges();

                devartModelPostgreSql.PageTemplateMlcs.InsertOnSubmit(new PageTemplateMlc
                {
                    TemplateId = 2,
                    LanguageId = 2,
                    TemplateWebsiteLive = @"
	
		
	
	
	
"
                });

                devartModelPostgreSql.SubmitChanges();
            }

LOG

Code: Select all


  16.11.2011 18:46:53 n/a dotConnect for PostgreSQL monitoring is started Complete
  16.11.2011 18:46:53 0,000 Creating pool manager Complete
  16.11.2011 18:46:53 0,000 Creating pool with connections string:  Complete
  16.11.2011 18:46:53 0,000 Creating object Complete
  16.11.2011 18:46:53 0,093 Open connection:  Complete
  16.11.2011 18:46:53 0,000 Connect:  Complete
  16.11.2011 18:46:53 0,016 Execute: SELECT version() Complete
  16.11.2011 18:46:53 0,000 Execute: show integer_datetimes Complete
  16.11.2011 18:46:53 0,000 Execute: show bytea_output Complete
  16.11.2011 18:46:53 0,000 Execute: SET autocommit=true Complete
  16.11.2011 18:46:53 0,015 Execute: SET datestyle=ISO ; SET client_encoding='LATIN5' Complete
  16.11.2011 18:46:53 0,000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  16.11.2011 18:46:53 0,000 Execute: SET search_path TO public; Complete
  16.11.2011 18:46:53 0,000 Prepare: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:46:53 0,000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:46:53 0,000 Prepare: BEGIN Complete
  16.11.2011 18:46:53 0,016 Execute: BEGIN Complete
  16.11.2011 18:46:54 0,000 Prepare: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:46:54 0,000 Execute: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:46:54 0,000 Commit Complete
  16.11.2011 18:46:54 0,000 Prepare: COMMIT Complete
  16.11.2011 18:46:54 0,000 Execute: COMMIT Complete
  16.11.2011 18:46:54 0,000 Close connection Complete
  16.11.2011 18:46:54 0,000 Connection is returned to pool. Pool has 1 connection(s). Complete
  16.11.2011 18:46:54 0,000 Open connection:  Complete
  16.11.2011 18:46:54 0,000 Taking connection from connection pool: 
Complete
  16.11.2011 18:46:54 0,000 Connection is taken from pool. Pool has 1 connection(s). Complete
  16.11.2011 18:46:54 0,000 Execute: SET search_path TO public; Complete
  16.11.2011 18:46:54 0,015 Prepare: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:46:54 0,000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  16.11.2011 18:46:54 0,000 Prepare: BEGIN Complete
  16.11.2011 18:46:54 0,000 Execute: BEGIN Complete
  16.11.2011 18:46:54 0,000 Prepare: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:46:54 0,000 Execute: INSERT INTO public.page_template_mlc (template_id, language_id, template_website_live, template_website_beta, template_mobile_live, template_mobile_beta) VALUES ($1, $2, $3, $4, $5, $6)  Complete
  16.11.2011 18:46:54 0,000 Commit Complete
  16.11.2011 18:46:54 0,000 Prepare: COMMIT Complete
  16.11.2011 18:46:54 0,000 Execute: COMMIT Complete
  16.11.2011 18:46:54 0,000 Close connection Complete
  16.11.2011 18:46:54 0,000 Connection is returned to pool. Pool has 1 connection(s). Complete

StanislavK · Post by **StanislavK** » Fri 18 Nov 2011 15:32

Thank you for the report, we have reproduced the issue. We will analyze it and inform you about the results as soon as possible.

As a workaround, you can disable batch command execution when saving texts. To do so, please set the MaxBatchSize property of your DataContext instance to 1. Please tell us if this helps.

evren · Post by **evren** » Sat 19 Nov 2011 13:30

Hi Stanislavk,

Thank you for response and explanation in the ticket as well. Setting MaxBatchSize = 1 in DataContext made insert commands to use parameters and i do not have backslash problem at all. Thank you for this as well.

So is this the exact solution or temporary solution, so i can set up different constructor of my DataContext for this.

Thanks in advance.

StanislavK · Post by **StanislavK** » Wed 23 Nov 2011 15:43

The issue was caused by a problem in using text parameters for commands executed without preparation (LinqConnect performs batch inserts via unprepared commands). The problem is fixed, and the fix will be available in the nearest build.

StanislavK · Post by **StanislavK** » Fri 09 Dec 2011 16:59

We have released the new 5.60.258 build of dotConnect for PostgreSQL which includes the fix for this issue. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only).

For the detailed information about the fixes and improvements available in dotConnect for PostgreSQL 5.60.258, please refer to
http://www.devart.com/forums/viewtopic.php?t=22838