Hi Martina,
The error always occurs in this method which I call GetTotalRowCount():
LOCAL lcAlias, lcSQL, lcCursor, lnRows, laError[1]
lcAlias = ALIAS()
lcCursor = SYS( 2015 )
lcSQL = "SELECT count(*) as totrecs "+;
"FROM " + IIF( EMPTY( This.FromClause ), LOWER( This.Alias ), This.FromClause )
IF This.SQLExecute( lcSQL, lcCursor )
lnRows = VAL( totrecs )
USE IN ( lcCursor )
ELSE
= AERROR( laError )
MESSAGEBOX( "[ "+ ( This.Alias ) +" ] GetTotalRowCount Error occurred: " + CHR(13) + CHR(13) + laError[ 2 ] )
ENDIF
IF NOT EMPTY( lcAlias )
SELECT ( lcAlias )
ENDIF
RETURN lnRowsWhen it is the turn of oPIHDR, whose SQL statement is:
select a.*, b.code as supcode, b.descriptor as supname, c.code as ewtaxcode, c.descriptor as ewtaxname, d.code as jtcode, d.descriptor as jtname from pihdr a left join supplier b on a.supplierid=b.id left join ewtax c on a.ewtaxid=c.id left join jthdr d on a.jthdrid=d.id
which should converted into (by GetTotalRowCount():
select count(*) from pihdr a left join supplier b on a.supplierid=b.id left join ewtax c on a.ewtaxid=c.id left join jthdr d on a.jthdrid=d.id
When this last sql statement is run, it produces that error. I ran an ODBC trace log, it shows the error but the statement is not found in the log.
Dennis
>HI Dennis,
>
>I did mean script for MySQL with CRERATE TABLE .. and INSERT INTO TABLE...
>Because the error ""The number of attributes is larger than the number of attribute values provided" depends on you MySQL tables structure and some data.
>
>MartinaJ
>
>
>>Hi Martina
>>
>>This is what came out when I used the Class Browser, then View Code.
>>
>>If you notice, when each SelectCmd is run individually, no problem occurs. When it is run as a batch, the error comes up.
>>
>>
>>**************************************************
>>*-- Class: bopi (d:\vfpxdev\metalbank\classes\metalbank.vcx)
>>*-- ParentClass: bobook (d:\vfpxdev\metalbank\classes\metalbank.vcx)
>>*-- BaseClass: dataenvironment
>>*-- Time Stamp: 07/30/22 02:04:05 PM
>>*
>>DEFINE CLASS bopi AS bobook
>>
>>
>> Width = 277
>> Height = 136
>> InitialSelectedAlias = "pihdr"
>> Name = "bopi"
>>
>>
>> ADD OBJECT opijrnl AS capijrnl WITH ;
>> Top = 84, ;
>> Left = 96, ;
>> SelectCmd = "select a.*, b.code as glcode, b.descriptor as glname, b.withsubs, b.isentity, b.entityid, c.code as slcode, c.descriptor as slname from pijrnl a inner join glacct b on a.glacctid=b.id left join slacct c on a.slacctid=c.id where a.hdrid=?pihdr.id", ;
>> userecordpagination = .F., ;
>> parentalias = "pihdr", ;
>> Name = "oPIJrnl"
>>
>>
>> ADD OBJECT opidtl AS capidtl WITH ;
>> Top = 87, ;
>> Left = 30, ;
>> SelectCmd = "select a.*, b.code as prodcode, b.descriptor as prodname, b.uom, b.unitcost1, b.unitcost2, b.unitcost3, b.unitcost4, b.unitcost5, b.unitcost6, b.unitcost7, b.unitcost8 from pidtl a inner join product b on a.productid=b.id where a.pihdrid=?pihdr.id", ;
>> userecordpagination = .F., ;
>> parentalias = "pihdr", ;
>> Name = "oPIDtl"
>>
>>
>> ADD OBJECT opihdr AS capihdr WITH ;
>> Top = 38, ;
>> Left = 69, ;
>> Name = "oPIHdr"
>>
>>
>> ADD OBJECT ojthdr AS cajthdr WITH ;
>> Top = 24, ;
>> Left = 168, ;
>> SelectCmd = "select a.*, b.txncode, b.txndesc from jthdr a inner join book b on a.bookid=b.id where bookid=?this.txncode order by txncode, code", ;
>> cursortype = 2, ;
>> txncode = "PB", ;
>> Name = "oJTHdr"
>>
>>
>> ADD OBJECT ojtdtl AS cajtdtl WITH ;
>> Top = 48, ;
>> Left = 168, ;
>> SelectCmd = "select a.*, b.code as glcode, b.descriptor as glname, b.withsubs, b.isentity, b.entityid, c.fielddesc from jtdtl a inner join glacct b on a.glacctid=b.id inner join bookdata c on a.fieldid=c.fieldid where a.jthdrid=?jthdr.id", ;
>> cursortype = 3, ;
>> Name = "oJTDtl"
>>
>>
>> ADD OBJECT osysparm AS casysparm WITH ;
>> Top = 24, ;
>> Left = 216, ;
>> cursortype = 2, ;
>> Name = "oSysparm"
>>
>>
>>ENDDEFINE
>>*
>>*-- EndDefine: bopi
>>**************************************************
>>
>>
>>
>>>Hi Dennis,
>>>
>>>It's possible upload script with tables and examples data for experiments?
>>>
>>>MartinaJ
>>>
>>>>Hi Martina,
>>>>
>>>>I use MySQL Server 8.0.30, not 8.0.29. The MySQL ODBC I installed is 8.0.30.
>>>>
>>>>driver=MySQL ODBC 8.0 Unicode Driver
>>>>server=localhost
>>>>port=1686
>>>>uid=root
>>>>pwd=p@55w0rd
>>>>database=metalbank
>>>>option=536870912
>>>>sslmode=DISABLED
>>>>
>>>>
>>>>Dennis
>>>>
>>>>
>>>>>Hi Dennis,
>>>>>
>>>>>You test MySQL 8.0.29, but latest version is 8.0.30.
>>>>>I did try MySQL 8.0.30 and ODBC connector 8.0.30 and number 4000000000 was coverts to string automaticaly (I don't set flag in connection string).
>>>>>
>>>>>MartinaJ
>>>>>
>>>>>>Hi Martina,
>>>>>>
>>>>>>Already did the changes. It is part of my base class so it is easy on my part.
>>>>>>
>>>>>>Also, I think we should really change it since using option=13684 (BIGINT) from MySQL will only convert it to VFP integer data, which we know has a limit.
>>>>>>
>>>>>>At least, using option=536870912 gives us the real values; we only need to convert using VAL().
>>>>>>
>>>>>>However, my problem still remains:
>>>>>>
>>>>>>ERROR [01000] [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.29]The number of attributes is larger than the number of attribute values provided
>>>>>>
>>>>>>
>>>>>>I shall go back to using MySQL ODBC 8.0.30 and Server 5.7.39 for now.
>>>>>>
>>>>>>Regards
>>>>>>Dennis
>>>>>>
>>>>>>
>>>>>>>Hi Dennis,
>>>>>>>
>>>>>>>Not is good way, becase you must rewrite your application, if you got datatype char instead number.
>>>>>>>
>>>>>>>IMHO, safety is using older ODBC driver.
>>>>>>>
>>>>>>>MartinaJ
>>>>>>>
>>>>>>>>Hi Martina,
>>>>>>>>
>>>>>>>>I changed option=13684 (Treat BIGINT columns as INT columns) to option=536870912 (Bind BIGINT parameters as strings).
>>>>>>>>
>>>>>>>>select 4000000000 yielded "4000000000 " -> a 20 character string.
>>>>>>>>
>>>>>>>>So I guess option=536870912 is way better?
>>>>>>>>
>>>>>>>>Dennis
>>>>>>>>
>>>>>>>>
>>>>>>>>>Hi Denis,
>>>>>>>>>
>>>>>>>>>ODBC driver converts number 4000000000 to -294967296?
>>>>>>>>>
>>>>>>>>>Hmm,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>?CAST(4000000000 as int)
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>IMHO, the ODBC driver got 4000000000 as bigint, but VFP don't know bigint datatype and convert to signed int.
>>>>>>>>>Question is why ODBC driver ignore NO_BIGINT flag?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>select CAST(4000000000 AS DECIMAL(20))
>>>>>>>>>-- select CAST(4000000000 AS INTEGER) -- converts to bigint - by documentation
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>MartinaJ
>>>>>>>>>
>>>>>>>>>>Hi Martina,
>>>>>>>>>>
>>>>>>>>>>I tried the different select's you told me to do:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>select 1
>>>>>>>>>>select 10
>>>>>>>>>>select 10000
>>>>>>>>>>select 4000000000
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>The first 3 yielded the same numbers, except for 4000000000, which yielded -294967296 !!!
>>>>>>>>>>
>>>>>>>>>>Why is this so? Pardon my ignorance.
>>>>>>>>>>
>>>>>>>>>>Regards
>>>>>>>>>>Dennis
