[ACFUG Discuss] javacast to convert string to int?
Trying to get a numerical value for a string, store into an int(10) signed MySQL 4.1.13 MyISAM table. cfset myInt = JavaCast(int, cnn.com) Simply returns cnn.com Obviously, this is out of my range. And obviously, this is not this simple. Can anyone point me in the right direction? _ Derrick Peavy derr...@derrickpeavy.com 404-786-5036 “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 12:53 PM, Teddy R. Payne wrote: Derrick, What I found related has to do deal with storage sizes. In this manual note: http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html I am not sure your storage configuration, but it has a caveat with NDB and number storage. BigInt works because it is twice the size of integers by default. Integer being 4 byte storage versus 8 byte storage of bigint. Does this apply? Data truncation is when storage sizes are mismatched, which is an obvious statement. The data engine is telling you data size is greater than storage availability in the column. Beyond that, I would have to defer to the rest of the community. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy derr...@derrickpeavy.com wrote: Teddy: Banging my head here. Can't get CAST to work in any form, even with the database itself (using phpmyadmin). Here is the actual data and info. QUERY 1 (does not have to be two queries, but this allows me to show you the exact values I am trying to insert) SELECT crc32('') AS referer_checksum, crc32('ccstageread') AS domain_checksum, crc32('127.0.0.1') AS resource_checksum Those values are then supplied to the next query... QUERY 2 INSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College', '', 0) The fields referer_checksum, domain_checksum, and resource_checksum are MySQL 4.1.13 data type int(10) The length of the crc32 value is 10 as seen in the second query above. If I change the field type to BIGINT and leave it as length 10, it works just fine. No error. But as an INT(10) I get the following error, which is caused by the second crc32 value of 2846130217 (the domain_checksum), not the first crc32 value, which in this case is 0. But if that first value is not 0, then the error is on that value, ergo each/any first crc32 value. The problem with changing the data type to BIGINT is, apparently, the PHP stuff that is reading this data and generating reports doesn't seem to work correctly if the data type is changed. Type Database Query Error Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 Native Error Code 0 SQL State 01004 SQL INSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College College', '', 0) _ Derrick Peavy derr...@derrickpeavy.com “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote: Derrick, Data truncation usually brings to mind java data conversion. Have you tried casting the results of the crc32 function?: CAST(crc32('127.0.0.1') AS int) Alternatively, what version of ColdFusion are you using? I have looked at the MySQL driver in CF8 recently. In the past, I had good success using JConnector to replace the JDBC MySQL default driver that came with CF. Here is the 4.1 manual referene to JConnector: http://dev.mysql.com/doc/refman/4.1/en/connector-j.html And here is the JDBC driver configurations instructions: http://kb2.adobe.com/cps/025/6ef0253.html If you are already using JConnector, ignore the recommendation. =) Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy derr...@derrickpeavy.com wrote: Trying to remove a PHP script from a cold fusion site by replacing it with a simple CF query. Current PHP file puts a crc32 value into an int(10) field in MySQL 4.1.13 Trying to do this with CF, nothing fancy. Basic SQL statement. Example: INSERT INTO exampleTable (domain_checksum) VALUES (crc32('127.0.0.1')) I then get this error: Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 But the data looks correct. For example, the value for 127.0.0.1 is generated as
Re: [ACFUG Discuss] javacast to convert string to int?
Derrick, You can want to convert cnn.com to a number? You can get the ASCII value for each character, but what you do with that afterwords could be anything goes. Do you plan to add the numbers together? Concatenate them together? Concatenation could outside the boundaries of 10 numerical digits and would have to program for leading zeroes. The ASCII value for any one of those characters is Asc() and analagously Chr() to covert back to characters from numbers. You would loop over each character in the string basically at the most simple example of conversion. There are probably some other more brainiac solutions out there as well in the realm of hashing techniques or encryption algorithms. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Tue, Dec 1, 2009 at 11:03 AM, Derrick Peavy derr...@derrickpeavy.comwrote: Trying to get a numerical value for a string, store into an int(10) signed MySQL 4.1.13 MyISAM table. cfset myInt = JavaCast(int, cnn.com) Simply returns cnn.com Obviously, this is out of my range. And obviously, this is not this simple. Can anyone point me in the right direction? * _* *Derrick Peavy* derr...@derrickpeavy.com 404-786-5036 * * *“Innovation distinguishes between a leader and a follower.” -Steve Jobs* *_* On Nov 25, 2009, at 12:53 PM, Teddy R. Payne wrote: Derrick, What I found related has to do deal with storage sizes. In this manual note: http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html I am not sure your storage configuration, but it has a caveat with NDB and number storage. BigInt works because it is twice the size of integers by default. Integer being 4 byte storage versus 8 byte storage of bigint. Does this apply? Data truncation is when storage sizes are mismatched, which is an obvious statement. The data engine is telling you data size is greater than storage availability in the column. Beyond that, I would have to defer to the rest of the community. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy derr...@derrickpeavy.comwrote: Teddy: Banging my head here. Can't get CAST to work in any form, even with the database itself (using phpmyadmin). Here is the actual data and info. * * *QUERY 1 (does not have to be two queries, but this allows me to show you the exact values I am trying to insert)* SELECT crc32('') AS *referer_checksum*, crc32('ccstageread') AS *domain_checksum*, crc32('127.0.0.1') AS *resource_checksum* Those values are then supplied to the next query... *QUERY 2* INSERT INTO my_table (dt, readableDT, referer, *referer_checksum*, *domain_checksum*, referer_is_local, resource, *resource_checksum*, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', *0*, *2846130217*, -1, '127.0.0.1', *3619153832*, 'CollegeClassifieds.com - Allegheny College', '', 0) The fields referer_checksum, domain_checksum, and resource_checksum are MySQL 4.1.13 data type int(10) The length of the crc32 value is 10 as seen in the second query above. If I change the field type to BIGINT and leave it as length 10, it works just fine. No error. But as an INT(10) I get the following error, which is caused by the second crc32 value of 2846130217 (the domain_checksum), not the first crc32 value, which in this case is 0. But if that first value is not 0, then the error is on that value, ergo each/any first crc32 value. The problem with changing the data type to BIGINT is, apparently, the PHP stuff that is reading this data and generating reports doesn't seem to work correctly if the data type is changed. Type DatabaseQuery Error Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 Native Error Code0 SQL State01004 SQLINSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College College', '', 0) * _* *Derrick Peavy* derr...@derrickpeavy.com * * *“Innovation distinguishes between a leader and a follower.” -Steve Jobs* *_* On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote: Derrick, Data truncation usually brings to mind java data conversion. Have you tried casting the results of the crc32 function?: CAST(crc32('127.0.0.1') AS int) Alternatively, what version of ColdFusion are you using? I have looked at the MySQL driver in CF8 recently. In the past, I had good success using JConnector to replace the JDBC MySQL default driver that came with CF. Here is the 4.1 manual referene to JConnector: http://dev.mysql.com/doc/refman/4.1/en/connector-j.html And here
Re: [ACFUG Discuss] javacast to convert string to int?
Why can't you simply store this as a string? John ma...@fusionlink.com Derrick Peavy wrote: Trying to get a numerical value for a string, store into an int(10) signed MySQL 4.1.13 MyISAM table. cfset myInt = JavaCast(int, cnn.com) Simply returns cnn.com Obviously, this is out of my range. And obviously, this is not this simple. Can anyone point me in the right direction? _ Derrick Peavy derr...@derrickpeavy.com 404-786-5036 “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 12:53 PM, Teddy R. Payne wrote: Derrick, What I found related has to do deal with storage sizes. In this manual note: http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html I am not sure your storage configuration, but it has a caveat with NDB and number storage. BigInt works because it is twice the size of integers by default. Integer being 4 byte storage versus 8 byte storage of bigint. Does this apply? Data truncation is when storage sizes are mismatched, which is an obvious statement. The data engine is telling you data size is greater than storage availability in the column. Beyond that, I would have to defer to the rest of the community. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy derr...@derrickpeavy.com wrote: Teddy: Banging my head here. Can't get CAST to work in any form, even with the database itself (using phpmyadmin). Here is the actual data and info. QUERY 1 (does not have to be two queries, but this allows me to show you the exact values I am trying to insert) SELECT crc32('') AS referer_checksum, crc32('ccstageread') AS domain_checksum, crc32('127.0.0.1') AS resource_checksum Those values are then supplied to the next query... QUERY 2 INSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College', '', 0) The fields referer_checksum, domain_checksum, and resource_checksum are MySQL 4.1.13 data type int(10) The length of the crc32 value is 10 as seen in the second query above. If I change the field type to BIGINT and leave it as length 10, it works just fine. No error. But as an INT(10) I get the following error, which is caused by the second crc32 value of 2846130217 (the domain_checksum), not the first crc32 value, which in this case is 0. But if that first value is not 0, then the error is on that value, ergo each/any first crc32 value. The problem with changing the data type to BIGINT is, apparently, the PHP stuff that is reading this data and generating reports doesn't seem to work correctly if the data type is changed. Type Database Query Error Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 Native Error Code0 SQL State01004 SQLINSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College College', '', 0) _ Derrick Peavy derr...@derrickpeavy.com “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote: Derrick, Data truncation usually brings to mind java data conversion. Have you tried casting the results of the crc32 function?: CAST(crc32('127.0.0.1') AS int) Alternatively, what version of ColdFusion are you using? I have looked at the MySQL driver in CF8 recently. In the past, I had good success using JConnector to replace the JDBC MySQL default driver that came with CF. Here is the 4.1 manual referene to JConnector: http://dev.mysql.com/doc/refman/4.1/en/connector-j.html And here is the JDBC driver configurations instructions: http://kb2.adobe.com/cps/025/6ef0253.html If you are already using JConnector, ignore the recommendation. =) Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy derr...@derrickpeavy.com wrote: Trying to remove a PHP script from a cold fusion site by replacing it with a simple CF query. Current PHP file puts a crc32 value into an int(10) field in MySQL 4.1.13 Trying to do this with CF, nothing fancy. Basic SQL statement. Example: INSERT INTO exampleTable (domain_checksum) VALUES (crc32('127.0.0.1')) I then get this error: Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 But the data looks correct. For example, the value for
Re: [ACFUG Discuss] javacast to convert string to int?
John, Teddy, I can store it as a string. I would prefer. I think it's silly! The problem is in replacing a PHP script that does this so that the data is the same in the DB. The PHP app is taking a referer (for example) such as CNN and storing that as int(10) using crc32(). Then, the PHP app uses the int as a way to do fast search/compare. Since the app is not going to be replaced, merely the recording of the data, I am trying to replicate the recording of the data. So, I've tried using crc32() as Teddy knows from last week but that only works if I change the DB field to bigint, which again, I have no problem with, but apparently, that still harms the f'n PHP app. At this point, I've decided to not spend any more time on this and just replicate the reporting features in CF. Faster, easier, better. Done. _ Derrick Peavy derr...@derrickpeavy.com 404-786-5036 “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Dec 1, 2009, at 12:12 PM, John Mason wrote: Why can't you simply store this as a string? John ma...@fusionlink.com Derrick Peavy wrote: Trying to get a numerical value for a string, store into an int(10) signed MySQL 4.1.13 MyISAM table. cfset myInt = JavaCast(int, cnn.com) Simply returns cnn.com Obviously, this is out of my range. And obviously, this is not this simple. Can anyone point me in the right direction? _ Derrick Peavy derr...@derrickpeavy.com 404-786-5036 “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 12:53 PM, Teddy R. Payne wrote: Derrick, What I found related has to do deal with storage sizes. In this manual note: http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html I am not sure your storage configuration, but it has a caveat with NDB and number storage. BigInt works because it is twice the size of integers by default. Integer being 4 byte storage versus 8 byte storage of bigint. Does this apply? Data truncation is when storage sizes are mismatched, which is an obvious statement. The data engine is telling you data size is greater than storage availability in the column. Beyond that, I would have to defer to the rest of the community. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy derr...@derrickpeavy.com wrote: Teddy: Banging my head here. Can't get CAST to work in any form, even with the database itself (using phpmyadmin). Here is the actual data and info. QUERY 1 (does not have to be two queries, but this allows me to show you the exact values I am trying to insert) SELECT crc32('') AS referer_checksum, crc32('ccstageread') AS domain_checksum, crc32('127.0.0.1') AS resource_checksum Those values are then supplied to the next query... QUERY 2 INSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College', '', 0) The fields referer_checksum, domain_checksum, and resource_checksum are MySQL 4.1.13 data type int(10) The length of the crc32 value is 10 as seen in the second query above. If I change the field type to BIGINT and leave it as length 10, it works just fine. No error. But as an INT(10) I get the following error, which is caused by the second crc32 value of 2846130217 (the domain_checksum), not the first crc32 value, which in this case is 0. But if that first value is not 0, then the error is on that value, ergo each/any first crc32 value. The problem with changing the data type to BIGINT is, apparently, the PHP stuff that is reading this data and generating reports doesn't seem to work correctly if the data type is changed. Type Database Query Error Data truncation: Out of range value adjusted for column 'domain_checksum' at row 1 Native Error Code0 SQL State01004 SQLINSERT INTO my_table (dt, readableDT, referer, referer_checksum, domain_checksum, referer_is_local, resource, resource_checksum, resource_title, search_terms, img_search_found) VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College College', '', 0) _ Derrick Peavy derr...@derrickpeavy.com “Innovation distinguishes between a leader and a follower.” -Steve Jobs _ On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote: Derrick, Data truncation usually brings to mind java data conversion. Have you tried casting the results of the crc32 function?: CAST(crc32('127.0.0.1') AS int) Alternatively, what version of ColdFusion are