Link to home
Start Free TrialLog in
Avatar of Mike Jacobs
Mike JacobsFlag for United Kingdom of Great Britain and Northern Ireland

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?

ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Jacobs

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.




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

Open in new window

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.

excellent. When I'm done with the current project, I shall return to that example and study what it teaches me. 


Thanks