> I ran into something where the newest > ODBC driver > on a varchar field was padding out the characters to the full > field length.
That is NOT a bug, the target value is padded to match the length of the field in the target cursor. I ran into this about 8 or 9 years ago when I was writing a value from a VFP table into an ODBC connected Access table. The Access field in question was being used to populate a taxtbox in an Access form that was Right Justified. When the VFP field value was passed, even as an ALLTRIM(cFieldn) with a maximum length of 10 characters, it would up being over 20 characters long - which was the field width of the target Access table field in question. That caused most or all of the field value to not display in the Access form's one textbox. I told the developers, after researching the hell out of this "bug" that there nothing I could do about the problem, and they needed to trim all leading & trailing spaces from the Access field value before rpesenting it in their form. They bitched, goroused and complained. But finally they were convinced that I was correct, and I was not about to rewrite my code in Access (the VFP code processed far faster than the access code, I proved that in front of their on-site programmer/installer - blew his mind). Once they corrected their form behavior with trimming all textbox values things went well. As I understand it the character fields are padded within ODBC to allow for faster internal data handling with "fixed length" character fields, as opposed to variable length character fields. Makes sense to me, but it is a shame the behavior can't be altered to get a trimmed result. I never did try to run a PADL() or PADR() with widened VFP fields to help overcome the "problem". I thought of that a while after having the one development company alter their form <g>... They should have been trimming their field values anyway! heh-heh... Of course, the field I was dealing with was used in a relational link with other VFP table, so I would have had to alter several of my VFP tables. It was easier for me to have those folks change their form, and with the attitude they had copped from the outset when they heard I was going to use VFP and ODBC to write to their precious Access tables they just deserved to be screwed with a little anyway <g>. Turns out when their field programmer/installer saw how fast I could run a conversion (less than 2 minutes) on an Access table through ODBC, as opposed to how long it took him (over 40 minutes), he was a believer. Oh, my 2 minutes included the time to set up my code AND run it. In his case he already has his code set up as a utility! We were both running the conversion over the same LAN from the same client PC, and the process involved replacing every "-" in a PartNumber field with "*", except if the last non-space character is a "-". In that case the trailing "-" was to be stripped. Also all nested ["] characters were to be converted to "inch" to prevent CSV output ambiguities. Real simple stuff that xBase does real well. I remember this guy's reaction to the blazing VFP processing speed as though it were yesterday. Anyway, my two cents. Perhaps someone knows of a newer ODBC flavor that allows a person to determine the leading or trailing position of any ODBC padding done. Otherwise you could try altering the Source table field(s) to PADR() or PADL() to better manage the padding behavior. Gil > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Brian Erickson > Sent: Thursday, August 09, 2007 11:53 PM > To: [EMAIL PROTECTED] > Subject: RE: Help confirm a MySQL ODBC bug > > > That is not unbelievable. I ran into something where the newest > ODBC driver > on a varchar field was padding out the characters to the full > field length. > I will look into this in the morning. > > Brian Erickson > mailto:[EMAIL PROTECTED] > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Steve Ellenoff > Sent: Thursday, August 09, 2007 7:28 PM > To: [EMAIL PROTECTED] > Subject: Help confirm a MySQL ODBC bug > > Can some of you who have access to MySQL run the following code > (below) and post back your results, along with your version of the > ODBC driver (including sub revision #) & MySQL Engine. > > This program should demonstrate a bug I've been seeing. I'm looking > to collect more data on this problem so I've put together this test > program to make a more scientific examination of the problem, since > other people here have reported not having the issue. > > Please remember to change the parameters at the top of prg to match > your MySQL installation. > > Thanks for your help in advance. > Steve > > ****************************************************************** > ********** > ****************************************************************** > ********** > **************************** > > ** MySQL ODBC Bug with VFP? > ** > ** Run this program once you've changed the setup parameters below > appropriately. The proper results should show that > ** all 3 text fields t,mt,lt of type text, mediumtext, and longtext > respectively are reported by VFP as MEMO fields. > ** The bug I'm seeing with newer versions of the 3.51 ODBC Driver is > that LONGTEXT are coming back as C(255) fields. > ** The program will cleanup the test table it creates (provided your > login has security rights for create & drop table). > > CLEAR > CLOSE ALL > LOCAL loSPT, lcExec, lcDriver, lcServer, lcUserId, lcPassword, > lcCStr, lcDatabase, lcTable, lcDTString > > ****************************************** > ** Change these to meet your situation ** > > lcDriver = "MySQL ODBC 3.51 Driver" > lcServer = "127.0.0.1" > lcUserId = "root" > lcPassword = "rootpw" > lcDatabase = "test" > lcTable = "mysqlbug" > > ****************************************** > > lcCStr = > "DRIVER="+lcDriver+";SERVER="+lcServer+";DATABASE=test;UID="+lcUse > rID+";PWD= > "+lcPassword > ?lcCStr > lcDTString = lcDatabase + "." + lcTable > ?lcDTString > ? > > *Create class to handle SPT > loSPT = CREATEOBJECT("SPTClass",lcCStr) > IF VARTYPE(loSPT) = "O" > *Create Table > TEXT TO lcExec NOSHOW TEXTMERGE > CREATE TABLE `<<lcDatabase>>`.`<<lcTable>>` ( > `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, > `t` TEXT NOT NULL , > `mt` MEDIUMTEXT NOT NULL, > `lt` LONGTEXT NOT NULL , > PRIMARY KEY(`id`) > ) > ENGINE = InnoDB; > ENDTEXT > ?lcExec > IF loSPT.Execute(lcExec) > 0 > *Return columns > IF SQLCOLUMNS(loSPT.nHandle,lcDTString) > 0 > BROWSE > ENDIF > *Drop the table > lcExec = "DROP TABLE " + lcDTString > =loSPT.Execute(lcExec) > ENDIF > ENDIF > > > DEFINE CLASS SPTClass AS Custom > nHandle = -1 > cConnStr = "" > nExecTimeSecs = 0 > > FUNCTION Init > LPARAMETERS tcConnStr,tlNoAutoConnect > THIS.cConnStr = tcConnStr > IF !tlNoAutoConnect > RETURN THIS.Connect() > ENDIF > ENDFUNC > > FUNCTION Destroy > THIS.Disconnect() > ENDFUNC > > FUNCTION Connect > IF !EMPTY(THIS.cConnStr) > THIS.nHandle = SQLSTRINGCONNECT(THIS.cConnStr) > IF THIS.nHandle < 0 > THIS.LogError(THIS.nHandle) > ENDIF > ENDIF > ENDFUNC > > FUNCTION Disconnect > LOCAL lnResult > IF THIS.nHandle > 0 > lnResult=SQLDISCONNECT(THIS.nHandle) > IF lnResult < 0 > THIS.LogError(lnResult) > ENDIF > ENDIF > ENDFUNC > > FUNCTION GetLastExecTime > RETURN THIS.nExecTimeSecs > ENDFUNC > > FUNCTION Execute > LPARAMETERS tcCmd, tcAlias > LOCAL lnSecs, lnResult > lnSecs = 0 > IF THIS.nHandle > 0 AND !EMPTY(tcCmd) > lnSecs = SECONDS() > IF VARTYPE(tcAlias)="C" > lnResult = > SQLEXEC(THIS.nHandle,tcCmd,tcAlias) > ELSE > lnResult = SQLEXEC(THIS.nHandle,tcCmd) > ENDIF > lnSecs = SECONDS() - lnSecs > THIS.nExecTimeSecs = lnSecs > IF lnResult < 0 > THIS.LogError(lnResult) > ENDIF > ENDIF > RETURN lnResult > ENDFUNC > > FUNCTION LogError > LPARAMETERS tnResult > LOCAL ARRAY laError[1] > AERROR(laError) > MESSAGEBOX(laError[2],16,"Error " + TRANSFORM(laError[5])) > ENDFUNC > ENDDEFINE********************************* > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
