SQL Programming
Execute an SQL script from VFP
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
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)
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.
Remove all the GO
commands - those are interactive.
Something like this:
lcSql = STRTRAN(lcSql,"GO" + CHR(13),"")
loSql.Execute(lcSql)
+++ Rick ---