Sqlexec() command line maybe too long.

Lolu

I am trying to post data from a Foxpro file to a sql sever. This has been successful, however when i try adding some more data, it fails (most of the time crashes).

So i have shortened the command line by storing the variable i want to call in "m.Sg_Subject"

I then call this in my sql exec() as shown below:

SQLEXEC(m.temp1, "INSERT INTO MsgInfo (ID, creator, created, sequenceNo, EnvCount, Status, StatusTime, Body, BodyType, Subject);
VALUES ( MsgInfo_ID, Strategic.sg_creator, &temp_time, MsgInfo_ID, '0', '3', &temp_time, Body_ID, 'ASCII', m.Sg_subject )")

As you can see i have included m.sg_subject into the values and this works fine unders the column subject, however, if i add an additional column after Subject and inserts its value, foxpro crashes, I have been told this is because the command line maybe to long for FoxPro to execute, any ideas how i can over come this ...as in add more to my command line, so as to include more fields after subject

dni

If you exclude "Subject" from command is working ...something like:

SQLEXEC(m.temp1, "INSERT INTO MsgInfo (ID, creator, created, sequenceNo, EnvCount, Status, StatusTime, Body, BodyType);
VALUES ( MsgInfo_ID, Strategic.sg_creator, &temp_time, MsgInfo_ID, '0', '3', &temp_time, Body_ID, 'ASCII' )")

Vladimir Zografski

Code Block

*** I always use this way
*** No problem with the string length
TEXT TO lcSql TEXTMERGE NOSHOW
INSERT INTO MsgInfo
(ID, creator, created, sequenceNo, EnvCount, Status, StatusTime, Body, BodyType, Subject)
VALUES ( MsgInfo_ID, Strategic.sg_creator, &temp_time, MsgInfo_ID, '0', '3', &temp_time, Body_ID, 'ASCII', m.Sg_subject )
ENDTEXT

lnRetVal = SQLEXEC(m.temp1, m.lcSql)
IF lnRetVal < 0
AERROR(arr)
MessageBox(arr[1,2])
ENDIF

CetinBasoz

A literal string in VFP cannot exceed 255 in length. Either use multiple literals concatenated with + or text...endtext. Text..endtext is easier because you can write just as you would do in query analyzer or new query windows.

Code Block

lcSQL = ;

"INSERT INTO MsgInfo"+;

" (ID, creator, created, sequenceNo, EnvCount,"+;

" Status, StatusTime, Body, BodyType, Subject)"+;

" VALUES"+;

" ( MsgInfo_ID, Strategic.sg_creator,"+;

" m.temp_time, MsgInfo_ID, '0', '3',"+;

" m.temp_time, Body_ID, 'ASCII', m.Sg_subject )")

Code Block

text to m.lcSQL noshow

INSERT INTO MsgInfo

(ID, creator, created, sequenceNo, EnvCount,

Status, StatusTime, Body, BodyType, Subject);
VALUES ( MsgInfo_ID, Strategic.sg_creator, m.temp_time,

MsgInfo_ID, '0', '3', m.temp_time, Body_ID, 'ASCII', m.Sg_subject )

endtext

Would both work. Please note that I replaced &temp_time with m.temp_time. Using & is unnecessary.

Lolu

Hello,

Yes it does work if exclude subject, it does work perfectly, the problem is I need to include subject and some more fields after subject, its just that i beleive when i enter the subject field, by then i would have reached my limits.

Thank you

CetinBasoz

Yes you're exceeding the 255 limit for a string literal. & is the culprit there hiding that you have a long string. I guess temp_time is something like:

temp_time = "'"+ttoc(datetime())+"'"

which would cause the whole string go above limits. Besides causing the limit problem using a datetime is itself is a problem. If you've used m.temp_time instead you wouldn't even have a problem with this one (but still you'd be near the limit).

Passing datetime like that is another problem. It might work but with unintentional wrong values written to backend. When you pass a date or datetime like that then you need to sure it conforms a special format recognizable by the backend (culture invariant, ODBC canonical format - YYYY/MM/DD hh:mms). For example if you pass '1/1/2007' it wouldn't pose much of a problem and would be treated as Jan 1st, 2007. However if 1/2/2007 is passed and frontend/backend are using different cultures then that value might be Jan 2nd or Feb 1st.

In summary:

-Do not use & but . With usage you can do these:

temp_time = date()

temp_time = datetime()

and VFP makes the necessary conversions for you.

-Do not write it all in one long literal. Either divide into multiple literals or use text...endtext.

Lolu

Thanks for that, I am going to give it a go in a minute!

The only thing is, would this then enable me to be able to add more field after Subject

say for eaxample i extend my command line to include two additional fields RouteRule and Source;

SQLEXEC(m.temp1, "INSERT INTO MsgInfo (ID, creator, created, sequenceNo, EnvCount, Status, StatusTime, Body, BodyType, Subject, RouteRule, Source);
VALUES ( MsgInfo_ID, Strategic.sg_creator, &temp_time, MsgInfo_ID, '0', '3', &temp_time, Body_ID, 'ASCII', m.Sg_subject, m.Sg_rule, m.Sg_code'')")

off course this would be going way over the limit, but would your TextMerge idea takecare of this

Thank you

Lolu

Thanks for that, I am going to give it a go in a minute!

The only thing is, would this then enable me to be able to add more field after Subject

say for eaxample i extend my command line to include two additional fields RouteRule and Source;

SQLEXEC(m.temp1, "INSERT INTO MsgInfo (ID, creator, created, sequenceNo, EnvCount, Status, StatusTime, Body, BodyType, Subject, RouteRule, Source);
VALUES ( MsgInfo_ID, Strategic.sg_creator, &temp_time, MsgInfo_ID, '0', '3', &temp_time, Body_ID, 'ASCII', m.Sg_subject, m.Sg_rule, m.Sg_code'')")

off course this would be going way over the limit, but would your idea takecare of this

Thank you

CetinBasoz

Yes with text ... endtext approach you can write up to 16Mb.