SQL Programming
Execute an SQL script from VFP
Gravatar is a globally recognized avatar based on your email address. Execute an SQL script from VFP
  Bob
  All
  Mar 29, 2020 @ 07:29pm

Is there a way to call and execute an SQL script from a VFP Program? I am trying to create a table using a script file then populate it from a cursor. I am using your wwsql utility for processing the SQL commands.

The script file looks like this.

/****** Object:  Table [dbo].[Customer]    Script Date: 2/13/2020 11:44:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[OldCustNum] [nvarchar](5) NULL,
	[CustType] [nvarchar](10) NULL,
	[FirstName] [nvarchar](25) NULL,
	[LastName] [nvarchar](30) NULL,
	[Address1] [nvarchar](25) NULL,
	[Address2] [nvarchar](25) NULL,
	[City] [nvarchar](25) NULL,
	[County] [nvarchar](25) NULL,
	[State] [nvarchar](2) NULL,
	[Postal] [nvarchar](10) NULL,
	[HomePhone] [nvarchar](12) NULL,
	[CellPhone] [nvarchar](12) NULL,
	[OtherPhone] [nvarchar](12) NULL,
	[Fax] [nvarchar](12) NULL,
	[NumToCall] [int] NULL,
	[Balance] [money] NULL,
	[Email] [nvarchar](60) NULL,
	[DeclEmail] [bit] NULL,
	[NoText] [bit] NULL,
	[Status] [nvarchar](2) NULL,
	[TaxExempt] [bit] NULL,
	[PrefRemind] [nvarchar](5) NULL,
	[AcctOpen] [datetime] NULL,
	[BirthDate] [datetime] NULL,
	[LastInvc] [datetime] NULL,
	[LastPay] [datetime] NULL,
	[LastAccess] [datetime] NULL,
	[ReferredBy] [nvarchar](25) NULL,
	[ModifiedBy] [int] NULL,
	[Profile] [nvarchar](6) NULL,
	[ModifiedDate] [datetime] NULL,
	[Computer] [nvarchar](20) NULL,
	[IsTemp] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[CCSID] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


Gravatar is a globally recognized avatar based on your email address. re: Execute an SQL script from VFP
  Tore Bleken
  Bob
  Mar 29, 2020 @ 11:36pm

Simply make a string of the whole script, and pass it in one go.

Text to lcText NOSHOW PRETEXT 4
/****** Object:  Table [dbo].[Customer]    Script Date: 2/13/2020 11:44:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
   [CustomerID] [int] IDENTITY(1,1) NOT NULL,
   [OldCustNum] [nvarchar](5) NULL,
   [CustType] [nvarchar](10) NULL,
   [FirstName] [nvarchar](25) NULL,
   [LastName] [nvarchar](30) NULL,
   [Address1] [nvarchar](25) NULL,
   [Address2] [nvarchar](25) NULL,
   [City] [nvarchar](25) NULL,
   [County] [nvarchar](25) NULL,
   [State] [nvarchar](2) NULL,
   [Postal] [nvarchar](10) NULL,
   [HomePhone] [nvarchar](12) NULL,
   [CellPhone] [nvarchar](12) NULL,
   [OtherPhone] [nvarchar](12) NULL,
   [Fax] [nvarchar](12) NULL,
   [NumToCall] [int] NULL,
   [Balance] [money] NULL,
   [Email] [nvarchar](60) NULL,
   [DeclEmail] [bit] NULL,
   [NoText] [bit] NULL,
   [Status] [nvarchar](2) NULL,
   [TaxExempt] [bit] NULL,
   [PrefRemind] [nvarchar](5) NULL,
   [AcctOpen] [datetime] NULL,
   [BirthDate] [datetime] NULL,
   [LastInvc] [datetime] NULL,
   [LastPay] [datetime] NULL,
   [LastAccess] [datetime] NULL,
   [ReferredBy] [nvarchar](25) NULL,
   [ModifiedBy] [int] NULL,
   [Profile] [nvarchar](6) NULL,
   [ModifiedDate] [datetime] NULL,
   [Computer] [nvarchar](20) NULL,
   [IsTemp] [bit] NULL,
   [IsDeleted] [bit] NULL,
   [CCSID] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
   [CustomerID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EndText
lnSuccess = SQLExec(lnHandle, lcText)
 
Gravatar is a globally recognized avatar based on your email address. re: Execute an SQL script from VFP
  Bob
  Tore Bleken
  Mar 30, 2020 @ 09:39am

Thanks for the prompt response. I was finally able to get it to work, but it doesn't like stringing together multiple commands in the string. It was returning a -1 until I sent only the create table part of the string. I don't know if the other lines in the script are important or not. I guess I could execute them individually if needed.

Gravatar is a globally recognized avatar based on your email address. re: Execute an SQL script from VFP
  Rick Strahl
  Bob
  Mar 30, 2020 @ 03:02pm

Remove all the GO commands - those are interactive.

Something like this:

lcSql = STRTRAN(lcSql,"GO" + CHR(13),"")
loSql.Execute(lcSql)

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Execute an SQL script from VFP
  Bob
  Rick Strahl
  Mar 30, 2020 @ 07:10pm

Thank you both. I'm back in business. Everything is working.

© 1996-2024