SQL Programming
Interpreting wwSQL issues
Gravatar is a globally recognized avatar based on your email address. Interpreting wwSQL issues
  Michael Hogan (Ideate Hosting)
  All
  Dec 2, 2021 @ 02:06pm

I'm using wwSQL to insert some records into SQL server, and for A FEW records I'm getting the error:

Unclosed quotation mark after the character string ''. [1526:105]

The errors are captured from the OSQL.CERRORMSG property... but if I grab the INSERT statement in the error message and execute it unchanged in SSMS, it succeeds.

Any trick to diagnosing the issue? I'm having trouble finding a version number for wwSQL -

A typical error message in full is: Unclosed quotation mark after the character string ''. [1526:105]

INSERT DBPACSSD ([ACCTCODE],[ALPHA],[AMAZON_DESC],[BACKTOLINE],[BATCHNO1],[BATCHNO2],[BATCHNO3],[BATCHQTY1],[BATCHQTY2],[BATCHQTY3],[BRAND],[CCOMMPCT],[CITY],[CLASS],[COMMITTED],[COST],[COUNTY],[CUSTNO],[DCOMMPCT],[DISCOUNT1],[DISCOUNT2],[DISCOUNT3],[DISCPCT],[DISTMAN],[DPRICE],[DROPINVNO],[DROPSHIP],[DROPSHIPIN],[DROPSHIPOR],[ENTBY],[ENTDATE],[EOP],[GRIDNO],[INVNO],[ITEMDESC],[ITEMDESC2],[ITEMNO],[ITEMNO_I],[ITEMSHIP],[MCUSTNO],[MSDSFIRST],[OCOMMPCT],[OPRICE],[ORDERPROG],[ORDLN],[ORDNO],[OSOURCE],[OVERRIDE],[OVERRIDEPR],[PICKBATCH],[PIECES],[POINTS],[POSITION],[PRICE],[PRICELEVEL],[PRICETYPE],[QTYBACK],[QTYCANCEL],[QTYCM],[QTYORD],[QTYTOSHIP],[RESTOCKFEE],[RESTOCKPCT],[RETDISP],[RETWHY],[SHIPDATE],[SHIPTIME],[SHIPTONO],[SHIPWT],[STATE],[STATUS],[UM],[UNITFRT],[UPC],[UPS],[WAREHOUSE]) VALUES (0,'PAULA JE','',0,'','','',0,0,0,'',0,'JUNO BEACH','*',1,3,'',452634,0,0,0,0,0,0,20.10,'',0,0,0,'steve','2019-12-05 00:00:00',0,0,'1',' 9 WATTS 120 VOLTS BR30 650 LUMENS 300','','LED9BR30/830/4PK','','',0,1,0,0,'',3,7720281,'',0,0,0,0,0,'',20.10,0,'',0,0,8,8,8,0,0,'','','2019-12-09 00:00:00','',1,0,'FL','S','',0,'',0,100)

Is it possible a CR in the memo field is getting in the way?

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Rick Strahl
  Michael Hogan (Ideate Hosting)
  Dec 2, 2021 @ 02:55pm

If you have CRs in your literal strings then yes that will be a problem as literal strings cannot contain line breaks.

How is that SQL statement generated? If you're doing it in code then you should be using named parameters (?privateVar) to avoid encoding issues.

If it's generated from SqlBuildInsertStatement it should take care of the encoding of line breaks and quotes etc.

Something like this:

losql = CREATEOBJECT("wwSql")

loData = CREATEOBJECT("EMPTY")
ADDPROPERTY(loData, "PK", "_12312")
ADDPROPERTY(loData, "Name","Jim Beam")
ADDPROPERTY(loData, "StreetAddress","234 North End" + CHR(13) + "#21312")
lcSQL = loSql.SqlBuildUpdateStatementFromObject(loData, "Person", "PK", null, null, .T.)
? lcSql

THe output from this is:

UPDATE Person SET  [NAME]=?__NAME, [PK]=?__PK, [STREETADDRESS]=?__STREETADDRESS WHERE PK='_12312'

which refers the the internal parameters that were created. That command should work.

If you run that same code with .F. in the last parameter, you'll get an invalid sql string whihch breaks on the linebreak.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Michael Hogan (Ideate Hosting)
  Rick Strahl
  Dec 2, 2021 @ 03:37pm

I'm just using InsertDataFromCursor... I suppose I shoulda mentioned that!

This is what I am doing:

SET CLASSLIB TO 'classes/wwSQL' ADDITIVE
DO classes/wwUtils 
LOCAL OSQL AS WWSQL
OSQL = CREATEOBJECT("wwSQL")
lcConnString = 'driver=SQL Server;server=testing... etc'
OSQL.CONNECT(lcConnString)

  SELECT (lcCurrentTable)
    OSQL.INSERTDATAFROMCURSOR()
    REPLACE T_2_Xfer WITH SECONDS() - ltTableStart, ;
      Last_Xfer WITH DATETIME(), xfer_errs WITH .F. IN Tables2Copy
    IF  .NOT. EMPTY(OSQL.CERRORMSG)
       REPLACE xfer_errs WITH .T. IN Tables2Copy
       lcLogFile = [SQLErr_] + lcCurrentTable + [.log]
       WAIT WINDOW [Error! Writing errors to: ] + lcLogFile NOWAIT
       SET SAFETY OFF
       STRTOFILE(OSQL.CERRORMSG, lcLogFile, 0)
       SET SAFETY ON
    ENDIF
    WAIT WINDOW [Success Updating: ] + lcCurrentTable NOWAIT
    USE IN (lcCurrentTable)

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Rick Strahl
  Michael Hogan (Ideate Hosting)
  Dec 2, 2021 @ 04:04pm

Can you go into the wwSql::InsertDataFromCursor() method and change the following line to:

lcSQL = THIS.sqlbuildinsertstatementfromobject(loData,lcTable,loSQLRecord, null, .T.)

Fascinating that this line break issue never came up before... I know these methods are not heavily used, but still I imagine at some point this would have come up.

I'm starting to wonder if the two build functions should just default to use .T. for the parameter always, with an option to create literals. Besides the line break issues, it's also a potential SQL Injection Attack issue.

Reason I think i never caught this is because I tend to use wwBusiness and it uses the named parameter versions.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Rick Strahl
  Michael Hogan (Ideate Hosting)
  Dec 2, 2021 @ 04:23pm

Actually I take that back!

Looking at how literals get generated in that SQL generation routine, they do work if they have linebreaks. The literals are illegal in FoxPro but in SQL the linebreaks work fine.

This generated SQL is valid for execution in SQL Server:

INSERT Test ([ADDRESS],[BILLRATE],[COMPANY]) VALUES ('431 E. Street
#4441',             200.0000,'East Wind')

I'm testing this with if you want a simple scenario we can both work from:

CLEAR

DO wwsql
LOCAL loSql as wwSql
losql = CREATEOBJECT("wwSql")

? loSql.Connect("database=Test")

CREATE CURSOR TQuery (Company C(50), BillRate Y, Address M)  
INSERT INTO TQuery VALUES ("West Wind", 200, "123 N. Street" + CHR(13) + CHR(10) + "#2132")
INSERT INTO TQuery VALUES("East Wind", 200, "431 E. Street" + CHR(13) + CHR(10) + "#4441")

loSql.Insertdatafromcursor("Test")


lnResult = loSql.Execute("Select * from Test" ,"TSqlCursor")
? lnResult
BROWSE noWait

? loSql.ExecuteNonQuery("delete from Test")

But... that said, I still think that the insert method should definitely use named parameters to aviod the potential for SQL Injection using the change I posted in the last message.

I think I'm going to change the default of the Insert and Update SQL generation routines to use named parameters, as that is just a safer way to go. The parameter defaults will default to .T. but can still be explicitly set to .F. (which is non-conventionaly, but the only I can make this not break existing beheavior). Switching between the two modes should make no difference for execution, except for cases where somebody mucks with the inserted SQL values.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Michael Hogan (Ideate Hosting)
  Rick Strahl
  Dec 3, 2021 @ 08:08am

Thanks, Rick - I'll do more testing (when I can) to see where the problem is. I generally transfer thousands of records but just a couple dozen of the records fail. A few were easy to diagnose (single quote in memo field, ***'s in numeric field, empty dates) but the rest are head scratchers.

I'll report back if I have any suggestions.

Gravatar is a globally recognized avatar based on your email address. re: Interpreting wwSQL issues
  Rick Strahl
  Michael Hogan (Ideate Hosting)
  Dec 3, 2021 @ 10:37pm

Single quotes should be fixed up by the routine and escaped as double single quotes so that's likely not it.

It sounds like a mismatched quote.

+++ Rick --

© 1996-2024