Loadingclub484

• Sqlcmd Run Sql File
• Vbscript To Run .sql File L File In Psql
• Vbscript To Run .sql File Format

‘Make sure your database is online and ready to use

The cat Command. The following code demonstrates the first option for running an SQL script from. I have written plenty of VBA in Access applications. For this, I am just trying to set up a SQL script as a VBS script, so the users don't have to go into SSMS to run it. They can just double-click the VBS script, specify the server and database when prompted, and the quick script will run for them.

‘To create a DSN, just follow this link

The Script

Dim Connection, Recordset, SQL, Server, field, strAllFields

‘Declare the SQL statement that will query the database
SQL = “SELECT * FROM dbo.authors”

”’other sql statements
”’SQL = “SELECT DISTINCT * FROM dbo.authors ORDER BY au_lname DESC”

‘Create an instance of the ADO connection and recordset objects
Set Connection = CreateObject(“ADODB.Connection”)
Set Recordset = CreateObject(“ADODB.Recordset”)

‘Open the connection to the database
Connection.Open
“DSN=test;UID=ENTER_USERNAME;PWD=ENTER_PASSWORD;Database=pubs”

‘Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

‘Determine whether there are any records
If Recordset.EOF Then
wscript.echo “There are no records to retrieve; Check that you have the correct SQL query.”
Else
‘if there are records then loop through the fields
Do While NOT Recordset.Eof

‘What I want to return
field = Recordset(“au_lname”)

”’return more than one column
”’field = Recordset(“au_lname”) & “, ” & Recordset(“au_fname”)

‘Store all returned values into a string
if field <> “” then
strAllFields = chr(13) & field + strAllFields
end if

Recordset.MoveNext
Loop
End If

‘Display all values
msgbox “Author Names ” & chr(13) & chr(13) & strAllFields

‘Close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

For my reference

When working with Microsoft Access databases (or any other databases, for that matter) there are 4 types of SQL statements (or queries) that can be used to work with the information in the database, and these SQL statements are:

• select – obtain information from the database
• insert – add a new record into the database
• delete – remove records from the database
• update – change the details contained in the database

And all of these SQL statements can be used with VBScript – however, the first step is to connect to the database.
Connecting to a Microsoft Access Database
A programmer can easily use VBScript to make a connection to a Microsoft Access database by making use of one of Microsoft’s Active Data Objects:

dim connection_string : connection_string = _
“provider=microsoft.jet.oledb.4.0;” _
& “data source=c:customer.mdb”
dim conn : set conn = createobject(“adodb.connection”)
conn.open connection_string

In this example a connection is made to the database using the Microsoft Jet engine and accesses a Microsoft .mdb file. Then, with the connection in place, the programmer can then use VBScript to run queries and work with the contents of the database

The Select Query

The select query is perhaps the most commonly used query – this extracts information from the database and, in the case of VBScript, any results from the query are loaded into a recordset – in this example the contents of the id field from the name table are loaded into the recordset:

dim sql : sql = “select id from name”
dim rs : set rs = createobject(“adodb.recordset”)
rs.cursorlocation = 3 ‘Use a client-side cursor
rs.open sql, conn, 3, 3
‘A static copy of a set of records, locked only when updating

The contents of the recordset can then be used in VBScript – in this case to obtain a new id number for use in the database’s name table:

rs.movelast
dim id
if not rs.eof then
id = rs.Fields(0) + 1
else
id = 1
end if
msgbox id

This new id can be used when a new record is added to the table.

The Insert Statement
The next statement – the insert statement – adds a new record to a table:

dim surname : surname = “jones”
dim firstname : firstname = “john”
sql = _
“insert into name (id, surname,firstname)” _
& ” values (” & id & “,'” & surname & “‘,'” & firstname & “‘)”
conn.Execute sql

The insert statement is different from the select statement in that there is no result returned to VBScript – another select statement would have to be used in order to examine the new contents of the table.

The Delete Statement

The insert statement adds a record to a table and so, of course, the delete statement removes a record:
sql = _
“delete from name” _
& ” where surname = ‘” & surname & “‘” _
& ” and firstname = ‘” & firstname & “‘”
conn.Execute sql

As with the insert statement no result is returned.

The Update Query

The final statement – update – modifies a record in a database table, and so this next example inserts and then updates a record:
surname = “smith”
firstname = “jane”
sql = _
“insert into name (id, surname,firstname)” _
& ” values (” & id & “,'” & surname & “‘,'” & firstname & “‘)”
conn.Execute sql
dim new_surname : new_surname = “smyth”
sql = _
“update name” _
& ” set surname = ‘” & new_surname & “‘” _
& ” where surname = ‘” & surname & “‘” _
& ” and firstname = ‘” & firstname & “‘”
conn.Execute sql

And again no result would be returned and a select statement would be needed for the updated contents to be viewed.

Summary

The 4 types of SQL statements (or queries) used with databases, such as Microsoft Access, are:

• select
• insert
• delete
• update

Each can be used with VBScript by making use of Microsoft’s Active Data Objects which make data manipulation very easy:

• use the ADO to connect to the database
• use the ADO to create a recordset from the select statement.
• use the ADO to change the contents of the database using insert, delete and update statements

Giving the VBScript programmer full control of any database.

Introduction to Running an SQL File in Postgres

This tutorial will explain how to run an SQL file in Postgres, using the .sql extension, in a terminal or command prompt window. This can be accomplished in one of two ways, from inside of the PSQL command-line interface itself or from outside of PSQL. The article will also provide an overview of the options for the commands used to run a SQL file in Postgres.

Prerequisites to Run an SQL file in Postgres

• PostgreSQL must be properly installed and running. Execute the service postgresql status command to confirm the status of PostgreSQL is active. Press the CTRL + C keys to exit.

• The interactive PSQL command-line for PostgreSQL must be properly installed and working. Execute the psql -V command to confirm the status. The results should resemble the following:

Create a SQL File that will Execute with PostgreSQL

Navigate to the project folder or directory by executing the cd (change directory) command in a terminal or command-prompt window.

Create an SQL file using a text editor or terminal-based editor

Create the SQL file with a text editor or a terminal-based editor like nano, gedit or vim. Execute the following bash command to enter the .sql file in the current directory using nano:

Once inside, enter some SQL commands into the SQL file for later execution. Following is an example of a command used to return all of the record rows in the PostgreSQL table named some_table:

The results should resemble the following:

NOTE: Just press CTRL+O to output any changes to the file in nano. However, be aware this will overwrite any changes to the file, or it will create a new file called test.sql if a file does not already exist.

Run a SQL file in Postgres using the ‘psql’ command

Now execute the SQL file in the terminal window. Confirm there is a database created with some table data that can be used to test the psql command. A role or user for that database will also be required.

Options for the ‘psql’ command to run a SQL file in Postgres

Following is a brief explanation of each of the options, or flags, used for the psql command that will execute the SQL script file:

psql -U USER_NAME_HERE — The -U flag is used to specify the user role that will execute the script. This option can be omited if this option’s username is the first parameter. The default username is the system’s current username, if one has not been explicitly specified.

Sqlcmd Run Sql File

psql -h 127.0.0.1 — The -h flag is for the remote host or domain IP address where the PostgreSQL server is running. Use 127.0.0.1 for a localhost server.

psql -d some_database — The -d option is used for the database name.

psql -a — The -a or --echo-all flags will print all of the lines in the SQL file that conatin any content.

psql -f /some/path/my_script_name.sql — The -f option will instruct psql to execute the file. This is arguably the most critical of all the options.

Execute the SQL file in PostgreSQL from the terminal

The final step in this process is to execute the file using the psql command while connecting with an username, host and database. Note that this example uses the objectrocket role.

The following command can be used to access the test.sql file created earlier:

Use the ‘-a’ option to print everything while executing a SQL file in Postgres

The -a option, also referred to as the --echo-all option, is used to echo, or print, all of the input lines that contain any content. This includes the SQL comments and the original SQL commands or statements being executed.

The following is an example of the --echo-all option:

Vbscript To Run .sql File L File In Psql

The results should resemble the following:

NOTE: Be aware that the -a option will print everything contained in the file, including commands and the original SQL statement.

Connect to PostgreSQL and then run a SQL file using ‘psql’

The i command can aslo be used to execute the SQL file from within the psql interface.

Thepsql must be entered though a database and a specified user or by connecting to a database once inside psql, using a ROLE that has access to the database, with the c command.

Vbscript To Run .sql File Format

Connect to PostgreSQL without a database connection

The following example demonstrates how to connect to PostgreSQL with a specified ROLE, but without specifying a particular database:

The following example uses the c command, followed by the database name, to connect to a database before executing the SQL script:

Execute the following i command to run the SQL file:

NOTE: If no path is specified, then PSQL will use the directory that was last accessed before connecting to PostgreSQL in order to execute the SQL file.

The following screenshot provides an overview:

Conclusion to Executing a SQL File in Postgres

This tutorial explained two ways to run an SQL file in Postgres, specifically from inside the PSQL command-line interface or from outside of PSQL using the -f option. The article covered how to create an SQL file using a text editor or terminal-based editor that will execute with PostgreSQL, how to execute the SQL file from the terminal, connect to PostgreSQL and run a SQL file. The tutorial also covered how to run a SQL file in Postgres using the PSQL’ command and the options for the commands along with an explanation of each option. Remember that if no path is specified when connecting to PostgreSQL without a database connection, PSQL will use the directory that was last accessed prior connecting to PostgreSQL to execute the SQL file.