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
