asked on
Is there a string length limit in VFP SQL code? Can we bypass it?
The following sql code successfully assigns SQL code to a handle called ADDLINK, which inserts a line from my VFP source into a MariaDB table:
IF NOT PREPSQL('ADDLINK',"Insert into HASH_CHAIN (HASH_SOURCE,HASH_LINKED,HASH_SALTED,HASH_PREVIOUS,BLOCK_NUM,BLOCK_PREV,BLOCK_NEXT,CODEL_TIME) VALUES; (?M.HASH_SOURCE,?M.HASH_LINKED,?M.HASH_SALTED,?M.HASH_PREVIOUS,M.BLOCK_NUM,?M.BLOCK_PREV,?M.BLOCK_NEXT,?M.CODEL_TIME)","HASH_CHAIN")
stop('PREPSQL FAIL on ADDLINK')
ENDIF
I needed to add a new field to the code (HASH_CONFIRMED_BY) and thus created this version:
IF NOT PREPSQL('ADDLINK',"Insert into HASH_CHAIN (HASH_SOURCE,HASH_LINKED,HASH_SALTED,HASH_PREVIOUS,HASH_CONFIRMED_BY,BLOCK_NUM,BLOCK_PREV,BLOCK_NEXT,CODEL_TIME) VALUES; (?M.HASH_SOURCE,?M.HASH_LINKED,?M.HASH_SALTED,?M.HASH_PREVIOUS,?M.HASH_CONFIRMED_BY,?M.BLOCK_NUM,?M.BLOCK_PREV,?M.BLOCK_NEXT,?M.CODEL_TIME)","HASH_CHAIN")
*this adds a new row to the SQL table
stop('PREPSQL FAIL on ADDLINK')
ENDIF
and it failed with "Command Unrecognised" nonsense
initially thought I must have mistyped something. Eventually ruled that out.
Then I thought there might be some limitation caused by the length of the new field, but realised that I was using the same field in other SQL code (for retrieving data, not inserting it) so it couldn't be that.
so then, just to see what happens, I left the new field in but deleted the first two fields, to produce:
IF NOT PREPSQL('ADDLINK',"Insert into HASH_CHAIN (HASH_SALTED,HASH_PREVIOUS,HASH_CONFIRMED_BY,BLOCK_NUM,BLOCK_PREV,BLOCK_NEXT,CODEL_TIME) VALUES; (?M.HASH_SALTED,?M.HASH_PREVIOUS,?M.HASH_CONFIRMED_BY,?M.BLOCK_NUM,?M.BLOCK_PREV,?M.BLOCK_NEXT,?M.CODEL_TIME)","HASH_CHAIN")
*this adds a new row to the SQL table
stop('PREPSQL FAIL on ADDLINK')
ENDIF
and this time, it liked it.
From which I can only assume that the version I actually need is restricted purely by the length of the field list, plus the keyword "VALUES" because the new version list length (adding the field names and the memory variables) increases from 213 in the original version to 258 in the new. And I've bumped up against that (255 byte) string length limit in various scenarios in VFP before now.
Usually it's pretty simple to create a workaround to bypass that limitation but I can't see an obvious option in this case.
Anyone help me out?
TEXT - ENDTEXT example
SCAN SomeTable and retrieve data for each value from the numeric column xxx multiplied by 10:
LOCAL lcSQL
SELECT SomeTable
SCAN ALL
TEXT TO lcSQL NOSHOW TEXTMERGE
SELECT t1.ID, t2.*
FROM dbo.SQLtable t1
JOIN dbo.SomeOtherSQLtable t2 ON t2.ID = t1.ID
WHERE t1.SomeNumericColumn = <<xxx*10>>
ENDTEXT
lnResult = SQLEXEC(m.lnHandle, m.lcSQL, "cResult")
* process errors/results here
ENDSCAN
The advantage of the SQL command created inside TEXT - ENDTEXT is the readable formatting and the possibility to simply insert expressions using the textmerge option.
ASKER
excellent. When I'm done with the current project, I shall return to that example and study what it teaches me.
Thanks
ASKER
Once again, Pavel to the rescue. the lcSQL version worked a treat.
but while I obviously, therefore, don't need the TEXT - ENDTEXT option, I'm intrigued by it. I've never quite wrapped my head around what we can do with that. If it isn't too laborious, I'd welcome an illustration of how it would handle the same problem. Meanwhile I'm marking your response as the solution without further ado.
Thanks.
Oh, and seasonal greetings (to all at EE)
I made the mistake, last year end, of declaring that 2022 couldn't be any worse than the previous two. Not a mistake I'm ever likely to make again. Let's just hope that we can get through the next 12 months mostly intact.