Variable value in query

[TariqMehmod]

Sir I have these codes

[pre]
db1='xyz'
file_name='E:\HOURLY\at20\20190901_1159'

if m.con1>0
TEXT TO cmd noshow
backup database <<db1>> to disk='<<file_name\db1..bak>>'
endtext

if sqlexec(m.con1,cmd)<0
aerror(laerror)
messagebox(laerror[1,2])
return.f.
ENDIF[/pre]


It says:
Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '&'.

Please help me to sort out the error message

 

[Olaf Doschke]

Look at cmd in the debugger:

db1='xyz'
file_name='E:\HOURLY\at20\20190901_1159'
TEXT TO cmd noshow
  backup database <<db1>> to disk='<<file_name\db1..bak>>'
ENDTEXT

set step on

If you want textmerge functionality in TEXT TO, you have to add the option TEXTMERGE:

TEXT TO cmd noshow [highlight #FCE94F]TEXTMERGE[/highlight]

Also mixing macro substitution and textmerging doesn't work. As the error message mentions a "&" sign, I assume you're doing that, though you don't post that. Here's a demo of trying to mix textmerging and macro substitution:

lcText ="xyz"
Clear


Text to lcTest noshow TEXTMERGE
<<"This is &lcText.">>
EndText
? lcTest

Text to lcTest noshow TEXTMERGE
This is <<&lcText.>>
EndText
? lcTest

Text to lcTest noshow TEXTMERGE
This is <<lcText>>
EndText
? lcTest

The expression <<file_name\db1..bak>> will not work, neither exctly like that, as it combines several variable names in one string, nor with substition <<&file_name\&db1..bak>>, what you need is <<file_name>>\<<db1>>.bak

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Fro a simple SQL command like this, you might prefer to do:

cmd = [backup database ] + db1 + [ to disk =' ] + file_name + [\db1..bak']

Often, a TEXT / ENDTEXT construction is useful when building complex SQL commands, but if you unsure how to do it, the above might be easier to understand

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads