Yes. Actually that's why I classify building whole string as the worst case. When you do that you have to be very sure of what you're sending. With ? approach VFP handles correct conversion for you. SQL server would understand this:
('2005/10/31 07:40:27',...
It's canonical ODBC format. If not using that style you would be supplying some extra info what you're meaning. ie:
('01/02/2005 07:40:27',...
Is it Jan 2 or Feb 1? Is it 7 AM or PM? With ODBC canonical format it's 7 AM (24hrs format). Also plain string sending is not always 'myName' if you build on your own. For example this would fail:
values ('O'hara',
this would succeed:
values ('0''hara',
myName = "O'Hara"
values (?m.myName,
is better for this reason IMHO (or if the string were very long such as a memo char or binary).
Cetin
>Thanks again for your help.
>
>I'm curious though..
>
>When I used:
>
>
>insert into syserrors (datetime, error, line, message, codeline, program, user, appname,
>memory, status, callstack, notified)
>values
>(10/31/2005 07:40:27,1000,5000,'TEST MESSAGE' ,'TEST CODE LINE' ,'TEST PROGRAM' ,'KMAROIS'
>,'TEST' ,'' ,'' ,'TEST STACK' ,0)
>
>
>SQL errored with "invalid syntax near '07'.
>
>It didn't like the date/time portion.
>
>Any idea what was wrong?
>
>
>
>
>
>
>>OK forget it for the time being. Here is another simple way then:
>>
>>Create cursor myCursor (tDateTime t, ...)
>>insert into myCursor values (...)
>>text to m.sSQLCode noshow
>>insert into ...
>> values
>> (?myCursor.tDateTime, ...)
>>endtext
>>.oClass.SQLExecutorProc(m.sSQLCode)
>>
Cetin
>>
>>>Not sure what you mean by
>>>"You might create an updatable SPT to fill in the fields and update."
>>>
>>>Can you elaborate on this a bit more please?
>>>
>>>
>>>
>>>
>>>>Yep I noticed that after I replied and already updated my previous reply.
>>>>1) Execute SQLExec where you have those variables (in procedure you sent).
>>>>2) Pass the variables to the procedure which executes the code. Since you'd need multiple parameters probably you'd want to create a single object and pass it instead. ie:
>>>>
>>>>oParms = createobject('Empty')
>>>>Addproperty(oParms,'tDateTime',datetime())
>>>>...
>>>>
>>>>* And change your string to read
>>>>... values (?oParms.tDateTime, ...)
>>>>
>>>>3) You might create an updatable SPT to fill in the fields and update.
>>>>4) Worst case build the string with values.
>>>>Cetin
>>>>
>>>>
>>>>
>>>>>Ok, I see what is happening.
>>>>>
>>>>>As Aergey pointed out, the parameter values are dropping out of scope
>>>>>when the call to ._oSQLProcs.SQLCommand(sSqlCommand) happens.
>>>>>
>>>>>I'm open to suggesstions on the best way to do this.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>See my previous reply (and previous of previous ...). Include the alias prefix:
>>>>>>
>>>>>>(?m.tDateTime
>>>>>>not
>>>>>>(?tDateTime
>>>>>>
>>>>>>PS: Wait a min. You're calling another procedure to execute the command. Do the varables are still in scope there???
>>>>>>Cetin
>>>>>>
>>>>>>>Here is the code for the entire function. All paramter variables being passed
>>>>>>>to SQL contained values when I ran it.
>>>>>>>
>>>>>>>
>>>>>>>PROCEDURE HandleError(sAppName AS String, iErrNo AS Integer,;
>>>>>>> sErrMessage AS String, sLineOfCode AS String,;
>>>>>>> sProgram AS String, iLineNo AS int, sStack AS String)
>>>>>>>
>>>>>>> LOCAL iRetVal, sDateTime, sErrNo, sLineNo, sUserId, sMemory, sStatus
>>>>>>> LOCAL sSqlCommand
>>>>>>>
>>>>>>> iRetVal = TRUE
>>>>>>>
>>>>>>> WITH This
>>>>>>>
>>>>>>> ** Setup the connection
>>>>>>> IF .Setup()
>>>>>>>
>>>>>>> tDateTime = DATETIME()
>>>>>>> sUserId = UPPER(ALLTRIM(SUBSTR(SYS(0), AT("#", SYS(0))+1)))
>>>>>>>
>>>>>>> TEXT TO sSqlCommand NOSHOW
>>>>>>>
>>>>>>> insert into syserrors
>>>>>>> (datetime, error, line, message, codeline, program,
>>>>>>> [user], appname)
>>>>>>> values
>>>>>>> (?tDateTime, ?iErrNo , ?iLineNo, ?sErrMessage, ?sLineOfCode,
>>>>>>> ?sProgram, ?sUserId, ?sAppName)
>>>>>>>
>>>>>>> ENDTEXT
>>>>>>>
>>>>>>> ** Run the insert command
>>>>>>> ._oSQLProcs.SQLCommand(sSqlCommand)
>>>>>>>
>>>>>>> ** Check for and handle any errors that occured
>>>>>>> ._CheckForSQLError()
>>>>>>>
>>>>>>> ENDIF
>>>>>>>
>>>>>>> ENDWITH
>>>>>>>
>>>>>>>RETURN
>>>>>>>
>>>>>>>ENDPROC
>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Are you sure? I didn't see all in code. It asks for those missing only. Please do not forget to prefix with m. or alias,object name (if from a cursor, object).
>>>>>>>>Cetin
>>>>>>>>
>>>>>>>>>All variables are filled in already. I'm getting prompted for
>>>>>>>>>parameter values.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>Great they already are coming as parameters:)
>>>>>>>>>>Fill in those that aren't passed as parameters before call to SQLExec().
>>>>>>>>>>
>>>>>>>>>>PROCEDURE HandleError(sAppName AS String, iErrNo AS Integer,;
>>>>>>>>>> sErrMessage AS String, sLineOfCode AS String,;
>>>>>>>>>> sProgram AS String, iLineNo AS int, sStack AS String)
>>>>>>>>>>TEXT TO sSqlCommand NOSHOW
>>>>>>>>>> insert into syserrors
>>>>>>>>>> (datetime, error, line, message, codeline, program,
>>>>>>>>>> [user], appname, memory, status, callstack)
>>>>>>>>>> values
>>>>>>>>>> (?m.tDateTime, ?m.iErrNo , ?m.iLineNo, ?m.sErrMessage, ?m.sLineOfCode, ?m.sProgram,
>>>>>>>>>> ?m.sUserId, ?m.sAppName, ?sMemory, ?sStatus, ?sStack)
>>>>>>>>>>ENDTEXT
>>>>>>>>>>tDateTime = datetime()
>>>>>>>>>>sUserId = 'whoever'
>>>>>>>>>>sMemory = 'whatever'
>>>>>>>>>>sStatus = 'yourstatus'
>>>>>>>>>>SQLExec(...)
>>>>>>>>>>
Cetin
