Tag Archives: write

How to write text to a file with SQL Server

SQL Server, , , , , , , , , , , , , , , ,

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

 
Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

 
Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

 

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

 

How to save game scores in Corona SDK by writing to a file.

Corona SDK, Game Development, Lua, , , , , , , , , , , , ,

The scope of this tutorial will be to record and store the highest score achieved by a player in a game. This is done by:

  • Creating a file with a score of zero where the file does not exists.
  • When a player dies:
    • The previous score, i.e. value within the file, is assigned to a variable.
    • The player’s new score is assigned to a variable.
  • The two variables are compared.
  • If the previous score is higher nothing is changed.
  • If the previous score is lower the new score will be written to the file in its place.

Note:

No UI is provided as part of the tutorial as corona sdk often changes the manner in which objects are displayed, meaning the UI could break with future corona sdk versions. We will be working from the simulator output window alone.

For security reasons, you are not allowed to write files in the system.ResourceDirectory (the directory where the application is stored). You must specify either system.DocumentsDirectory, system.TemporaryDirectory, or system.CachesDirectory in the system.pathForFile() function when opening the file for writing. Read move about this here.

Below is a table describing when and where each directory should be used.

systemDirectoriesTo use this tutorial create a folder containing a main.lua file.
Paste the code below into the file and save.
Open the file with Corona SDK and the score.txt file will be created and populated with a score of zero.

Play around with the Player Score variable:
newScore = 99

Enter a higher score and it will overwrite what currently exists in the file.

Enter a lower score and nothing will be changed.

-- main.lua

local path = system.pathForFile( "score.txt", system.DocumentsDirectory )

deleteFile = function()
 local result, reason = os.remove(path) 
	if result then
		print( "File removed" )
	else
		print( "File does not exist", reason )  --> score.txt: No such file or directory
	end
end

--[[ 
Uncomment below to remove file
--]]

--deleteFile()

-- Player Score

newScore = 99

-- io.open opens a file at path. returns nil if no file found
-- fh short for file handle
-- "r" is the read instruction
local fh, reason = io.open( path, "r" )

if fh then
    -- Read all contents of file into a variable oldScore
	-- This will be the previous score
	-- To read file content as number use "*number"
	-- To read file content as text use "*a"
	-- "\n" new line
    local oldScore = fh:read( "*number" )
    print( "Old contents of " .. path .. "\n" .. oldScore )
	
	if oldScore < newScore then
		-- re-opening the file in "w+" mode will erase all previous data stored in the file
		-- in the comments below is a table listing all the file mode types
		fh = io.open( path, "w+" )
		-- Set the score to the player's new score.
		fh:write( newScore )
		print( "New contents of " .. path .. "\n" .. newScore )
	end	
else
	-- Error logic
    print( "Reason open failed: " .. reason )  -- display failure message in terminal

    -- create file because it doesn't exist yet
    fh = io.open( path, "w" )

    if fh then
        print( "Created file" )
    else
        print( "Create file failed!" )
    end
	
	-- Set the score to zero.
    fh:write( 0 )

end

io.close( fh )

--[[
The various file modes are listed in the following table:

"r"	Read-only mode and is the default mode where an existing file is opened.
"w"	Write enabled mode that overwites existing file or creates a new file.
"a"	Append mode that opens an existing file or a creates a new file for appending.
"r+" Read and write mode for an existing file.
"w+" All existing data is removed if file exists or new file is created with read write permissions.
"a+" Append mode with read mode enabled that opens an existing file or creates a new file.

]]--

See the lua online book’s I/O library section for more information on working with files.