Newline character in SQL for MS SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Mince
Forum Commoner
Posts: 25
Joined: Mon Aug 03, 2009 9:36 am

Newline character in SQL for MS SQL

Post by Mince »

Hi All

I'm struggling a bit with a script i created which generates SQL. Basically, I need to know what newline character to use when using that SQL in MS SQL server 2005.

Code: Select all


$sql = "USE [ampbase_stock] \r
			GO \r
/****** Object:  Table [dbo].[" . $tables[$i]["name"] . "_TEMPORARY]    Script Date: 09/07/2011 17:14:30 ******/ \r
SET ANSI_NULLS ON \r
GO \r
SET QUOTED_IDENTIFIER ON \r
GO \r
SET ANSI_PADDING ON \r
GO \r
CREATE TABLE [dbo].[" . $tables[$i]["name"] . "_TEMPORARY]( \r";

			for($j=0;$j<$columns[0]["numrows"];$j++)
			{
				$col_list .= "," . $columns[$j]["COLUMN_NAME"];
				// checks id column
				if($columns[$j]["COLUMN_NAME"] == "id")
				{
					$sql .= "[id] [bigint] IDENTITY(1,1) NOT NULL, \r";
				} // if($columns[$j]["COLUMN_NAME"] == "id")
				else
				{
					if($columns[$j]["TYPE_NAME"] == "varchar" or $columns[$j]["TYPE_NAME"] == "nvarchar" )
					{
						$type = "[varchar](" . $columns[$j]["PRECISION"] . ") COLLATE SQL_Latin1_General_CP1_CI_AS";
					}
					else
					{
						$type = "[" . $columns[$j]["TYPE_NAME"] . "]";
					}
					
					if($columns[$j]["NULLABLE"] == "0")
					{
						$null = "NOT NULL";
					}
					else
					{
						$null = "NULL";
					}
					
					if($columns[$j]["COLUMN_DEF"] <> NULL)
					{
						$default = $columns[$j]["COLUMN_DEF"];
					}
					
					$sql .= "[" . $columns[$j]["COLUMN_NAME"] . "] $type $null $default, \r";
				}
			} // for($j=0;$j<$columns[0]["numrows"];$j++)

$sql .= "PRIMARY KEY CLUSTERED \r
( \r
	[id] ASC \r
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] \r
) ON [PRIMARY] \r
\r
GO \r
SET ANSI_PADDING OFF";
			$sql = str_replace("\r","\n",$sql);
			$evolution -> quickSql($sql);

This outputs something like this when echo'd:

[text]USE [ampbase_stock]

GO

/****** Object: Table [dbo].[dms_batch_no_TEMPORARY] Script Date: 09/07/2011 17:14:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[dms_batch_no_TEMPORARY](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[created_date] [datetime] NULL ,
[modified_date] [datetime] NULL ,
[disabled_date] [datetime] NULL ,
[module_group_id] [int] NULL ,
[module_id] [int] NULL ,
[cluster_id] [int] NULL ,
[workgroup_id] [int] NULL ,
[location_id] [int] NULL ,
[owner_id] [int] NULL ,
[description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dms_doc_type_id] [int] NULL ,
[record_status_id] [int] NULL ,
[sort_order] [int] NULL ,
PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



GO

SET ANSI_PADDING OFF[/text]

If i copy paste this into SQL SERVER Management Studio, it works no problem. However, if i use this in php i get the following error:

[text]
SQL Error Thrown: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'.,

SQL Query: USE [ampbase_stock] GO /****** Object: Table [dbo].[dms_batch_no_TEMPORARY] Script Date: 09/07/2011 17:14:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[dms_batch_no_TEMPORARY]( [id] [bigint] IDENTITY(1,1) NOT NULL, [created_date] [datetime] NULL , [modified_date] [datetime] NULL , [disabled_date] [datetime] NULL , [module_group_id] [int] NULL , [module_id] [int] NULL , [cluster_id] [int] NULL , [workgroup_id] [int] NULL , [location_id] [int] NULL , [owner_id] [int] NULL , [description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [dms_doc_type_id] [int] NULL , [record_status_id] [int] NULL , [sort_order] [int] NULL , PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
[/text]

As you can see it doesn't break it into different lines. I've tried \r \n ; char(10) char(13) etc and no joy.

Any help would be appreciated!
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Re: Newline character in SQL for MS SQL

Post by ok »

Which PHP function do you use to execute the query?
Post Reply