Hi All, Can you help me, when I 2

[ghiehotamares]

Hi All,

Can you help me, when I run my program from visual foxpro and updating the data records from browse windows unable to send update to sql table(backend). my requirements should be automatic update the records to sql table/backend.
below is my program. can you assist me what I missed the commands/scripts.

hConn=("Driver=SQL Server;DSN=GHIESOURCE;Server=DESKTOP-KAH179Q;UID=;PWD=;DATABASE=MASTER")
nHandle=SQLSTRINGCONNECT(hConn)
?nHandle
SQLEXEC(nHandle,"SELECT * FROM DBO.MSreplication_options","tblMScursor")
set multilocks on
BROWSE && from this Im doing update/editing data records that should be sent to sql table backend.
=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.MSreplication_options","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
=CURSORSETPROP('UpdateFieldList', 'optname, major_version, minor_version, revision, install_failures, Id', 'tblMScursor')
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version","minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
=CURSORSETPROP("sendupdates",.t., "tblMScursor")
SELECT tblMScursor
TABLEUPDATE(2,.T.,"tblMScursor")
BROWSE
CLOSE ALL

 

[Chris Miller]

Mike is right,

but let me try to understand what you're saying here:

I encountered "command contains unrecognized phrase/keyword" for the remarks field

When you remove the asterisks you get:

=CURSORSETPROP("UpdateNameList",;
"Id replications_opt.Id,;
optname replications_opt.optname,;
value replications_opt.value,;
major_version replications_opt.major_version,;
minor_version replications_opt.minor_version,;
revision replications_opt.revision,;
install replications_opt.install,;
remarks replications_opt.remarks","curReplications")

And then you get the error "command contains unrecognized phrase/keyword"
And you conclude this is for the remarks field being in the list?

Your string becomes too long, that's your problem, it's 289 characters, string literals can only be 255 characters long.

If I try to print this string on the commandline I get the last line highlighted:

? "Id replications_opt.Id,;
optname replications_opt.optname,;
value replications_opt.value,;
major_version replications_opt.major_version,;
minor_version replications_opt.minor_version,;
revision replications_opt.revision,;
install replications_opt.install,;
remarks replications_opt.remarks","curReplications"

But that doesn't mean the error is in that part. Error messages don't highlight the portion of code that errors, in multiline commands the highlighted line will always be the last and the LINENO() you may pass to an error handler will be that line number, the last line of a command, no matter if that line has the offending part of the command that renders it wrong, or not. So you can't conclude that it's the remark field causing problems. You always will have to look at all lines of a multiline command about possible problems.

Your string is too long, that's all.

Chriss

 

[Chris Miller]

Just some more tips on error analysis:
It's surely not obvious, if you don't know about this limitation you'll never find it, but there are also other ways in finding out:
If you put remarks higher in the list and swap it with some other field and still get the error, you'd be able to conclude that it's not about the remark field.

Your idea of excluding some field by a remark is a good idea, too, but it fails for other reasons, you can't have a remark in a multiline command and expect further lines are not commented. If you use syntax coloring you'd see a remark within a command that has multi-line continuation will mean the continuation makes all further lines the green remark color. It becomes more apparent if I start the comment in an earlier line:

Of course, that does not compile. Let alone the closing bracket at the end becomes part of the comment and is missing from the command.

So use syntax highlighting, test command variations, test string length by simply trying ? that string, that removes it out of the context of whatever else could be wrong. ? does not care about the text, doesn't interpret names as fields within the string, for example, and still has a problem with it, so it's something else like the length.

I don't know what pills the VFP programmers took or what they drank to not add a simple to understand "string literal is too long" error. When compiling, the parsing step surely will recognize " as string start delimiter and interpret everything after that and until the next " as a string. If a string literal gets too long that means the parse would recognize string length is overflowing, even if it only uses an unsigned byte as a length counter that would include 256, and not encountering a second " should not cause the parsing to go into an undefined mode, like overlooking the first " and interpreting the string as part of a command or such a silly thing. That's really a weakness of the VFP compiler, but we won't get a better one.

There are more such misleading behaviors and messages, unfortunately. So always take anything with a grain of salt. But also don't think any error message is misleading. Most of them are all right.

Chriss

 

[Chris Miller]

And by the way, there is a possibility to have comments within a multiline command, but only using &&, not *, and you use && after the semicolon:

Pay attention I put a semicolon at the start of the commented line. This will again work, as it shortens the string to below 256 characters. Not because the install field is problematic when adding it to the updatenamelist.

Chriss

 

[Mike Lewis]

Keep in mind too that the 255-character limit applies to literal character strings, not necessarily character variables. With that in mind, you could avoid the problem simply by splitting the string in two and storing it in a varaible:

lcList = ;
[highlight #FCE94F]"[/highlight]Id replications_opt.Id,;
optname replications_opt.optname,;
value replications_opt.value,;
major_version replications_opt.major_version,;
minor_version replications_opt.minor_version,[highlight #FCE94F]"[/highlight] + ;
[highlight #FCE94F]"[/highlight] revision replications_opt.revision,;
install replications_opt.install,;
remarks replications_opt.remarks[highlight #FCE94F]"[/highlight]

CURSORSETPROP("UpdateNameList", [highlight #FCE94F]lcList[/highlight];
"curReplications")

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Thanks Mike,

after all I said it wasn't explicitly telling how to overcome the problem. Another maybe also generally elegant looking way is to use textmerge (TEXT TO):

#Define CRLF CHR(13)+CHR(10)

Text To lcUpdateNamelist NoShow
Id             replications_opt.Id
optname        replications_opt.optname
value          replications_opt.value
major_version  replications_opt.major_version
minor_version  replications_opt.minor_version
revision       replications_opt.revision
install        replications_opt.install
remarks        replications_opt.remarks
EndText

lcUpdateNamelist = STRTRAN(lcUpdateNamelist,CRLF,",")

Chriss

 

[Chris Miller]

Well,

and the simplest form of getting the updatenamelist is generating it:

lcTable = "replications_opt"
lcUpdateNameList = ""
SQLColumns(hConn,lcTable ,"FOXPRO","cColumns")
Select cColumns
Scan
Text To lcUpdateNameList Additive NOSHOW  Textmerge
,<<cColumns.Field_name>> <<lcTable>>.<<cColumns.Field_name>>
EndText
EndScan
lcUpdateNameList = Substr(lcUpdateNameList,2)
? lcUpdateNameList

Chriss