Well, it looks to my eyes that the wpdb->insert() method should take care of my concern that maybe the values were being passed in the wrong order as compared to the field list. Unfortunately, that means I don't have any brilliant solution, either. 😕

If the error message didn't change, that means that the column definition wasn't affected or you are changing something in a different database or table.

There is a potential issue with this database code. It is using emulated prepared queries. This means that the PDO driver is actually building the complete sql query statement, with the values in it, and sending it to the database server upon the ->execute() call. Any string values, which are all the values, since they are being supplied as an array to the ->execute() call, are being escaped using whatever character set php is using. If this character set isn't the same as the database table(s) character set, a character conversion could cause an error like this one. It would be interesting to see what $dsn contains concerning the charset=... parameter and what the database table character encoding is set to.

pbismad
@pbismad , can you tell me how to find that? Or is it in this code. But, this is a good learning experience for me. I really appreciate you looking at the code like that.

:aside: and not necessarily important to the topic...
I'm thinking: whoever wrote this

  1. clearly knows at least something about code.

  2. They don't like to write HTML, so they use that Geolib thing, which is basically the long and short of that... like a PHP as Bootstrap CSS that no one ... knew existed. But,

  3. why they didn't just use the $wpdb, moreover,

  4. why even put an online, remote DB into the mix at all?

    I mean, they took me on a Mr Rogers type tour of the factory (via Zoom). It's a big deal! but I don't get this part at all (from an outsider's perspective). It's something to do, so I'm going with it.

    The PC I remote in to is their Office PC. That's where they have this archaic old, Win 3.1 or whatever DOS-like app, which clearly a database programmer designed, but it's a huge database and the sort of thing that works for this operation. They're able to make the barcode scan labels and put the data into that .mdb (it has ~ 2000k updates in the query to be executed, as this system has been down for months). And I worked on that WP plugin, trying to fix this apparently but i wasn't aware of it at the time. So I know how rather useless that part is. I guess it gives her a better summary view than what she's getting in that old desktop app interface. That has to be it. . And she can do it on her tablet. I guess it makes sense to have it online.

      If you are asking about the $dsn contents, near the top of the database class, in the __construct() method, there's a $dsn parameter. Just echo/print_r/var_dump that to see what it is.

        Thank you so much for your feedback. Sorry for my rambling last night.

        $dsn = 'mysql:dbname=pallets;host=domain.com';

        that's all that is. are you suggesting it should have charset data in there somewhere? i dont' understand.
        I've got myself all confused here. that $dsn is in my local file which i thought i shared here. in haste. i have to reexamine. thanks again.

          If the charset=... value is not set to match your database tables character set, a character conversion can take place over the connection between php and the database server. Also, since this code is using emulated prepared queries, sql injection is possible if these are not set the same.

          A typical setting would be -

          $dsn = 'mysql:dbname=pallets;host=domain.com;charset=utf8mb4';

          Where the utf8mb4 value would be the character set of your database tables.

          Of greater concern is if changing the column to a text or character type actually occurred and if it did, what is the current error.

          @pbismad got it. I upgraded their xampp to php7. of course i opened a whole new can of worms, but that must be done IMO. perhaps the upgrade will ultimately help to smoothe this out (id expect at least)

          (btw, i thought that's what you meant about the charset, i wasn't sure where to put it in the $dsn string. i'll try that as well)

          Edit:
          UGH! that's not going to work because, i think they have some kind of custom MDB driver. i think i'm just getting started. #@$%^&!!!!!!

          Error connecting to database: SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

          this is pretty unknown territory for me. i have a bit of .MDB / driver experience from back in the ColdFusion days, before i knew of MySQL.

            Any suggestions as far as PHP version goes, regarding the use of the ODBC / .mdb database? i did enable

            extension=pdo_odbc

            (that's what i need, correct?)

            I don't know if it ever even worked, w/ php5. so i don't know if it's worth it to try to stick w/ what they've got, considering it's not working.

            On this, i could really use some stern advice. I know you guys have been at it longer and more in depth than i have.
            i appreciate it.

            Edit: I'm connected, but now i'm getting a new error:

            Fatal error: Uncaught Error: Call to a member function execute() on null in C:\xampp\htdocs\includes\php\classes\DbConnection.php on line 154

            that code hasn't changed, so i dont know yet why i'm getting this new error. i suspect, the php ver diff (5 vs 7).

            i feel like im making progress at least. thank you again!

              The last error means that the ->prepare() call failed and returned a false value. However, because this code is using emulated prepared queries, the existing error handling that would have told you why it failed, isn't being executed.

              What is the actual database type? MySQL or MSSQL. If it is MySQL, you can set emulated prepared queries to false in the connection code, i.e. run real prepared queries, in which case you will get a pdoexception if the ->prepare() call fails. If you are using a MSSQL server, with the odbc driver, I'm pretty sure it doesn't support true prepared queries. In this case, you will need to temporarily add some debugging code.

              At the point in the first posted code in this thread, where you added the - /** THIS IS WHERE THE ERROR happens ... comment and debugging statements, right after the ->prepare() call, add the following to get the pdo error information -

              var_dump($this->errorInfo());

              Hi there. Thanks again for your help here!

              This is what I'm getting from my var_dump's (there's probably more there now than when I first posted):

              C:\xampp\htdocs\includes\php\classes\DbConnection.php (at line: 145)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:146:string 'SELECT count(*) FROM Pallet_Production WHERE ID > 165062 AND ID < 167057;' (length=73)
(this->sql ln: 146)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:148:
array (size=3)
  0 => string '00000' (length=5)
  1 => null
  2 => null
(this->errorInfo: 148)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:152:
object(PDOStatement)[4]
  public 'queryString' => string 'SELECT count(*) FROM Pallet_Production WHERE ID > 165062 AND ID < 167057;' (length=73)
(vdtemp ln: 152)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:153:
object(PDOStatement)[4]
  public 'queryString' => string 'SELECT count(*) FROM Pallet_Production WHERE ID > 165062 AND ID < 167057;' (length=73)
(pdostmt ln: 153)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:154:
array (size=0)
  empty
(this->bind ln: 154)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:157:
array (size=0)
  empty
(this->bind ln: 157)

              I added your recommended var_dump where it reads (this->errorInfo: 148)

              Note: I did get this working when I set it up on my local development server. The update query is successful. Same DB's, etc (basically, aside from being truncated). That was using xampp (Windows 11). One database is MS Access .mdb, and the other is MySQL. I can't see what's different here, aside from that their MySQL DB is "remote" on a Linux server, and mine is local under Xampp. but, the php code is the same (sans a few var_dump and comments, etc).

              EDIT: i see there's more/ different output after I submit the REQUEST to update the table:

              (this->bind ln: 173)
C:\xampp\htdocs\includes\php\classes\DbConnection.php (at line: 145)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:146:string 'DESCRIBE Pallet_Production;' (length=27)
(this->sql ln: 146)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:148:
array (size=3)
  0 => string '00000' (length=5)
  1 => null
  2 => null
(this->errorInfo: 148)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:152:
object(PDOStatement)[4]
  public 'queryString' => string 'DESCRIBE Pallet_Production;' (length=27)
(vdtemp ln: 152)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:153:
object(PDOStatement)[4]
  public 'queryString' => string 'DESCRIBE Pallet_Production;' (length=27)
(pdostmt ln: 153)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:154:
array (size=0)
  empty
(this->bind ln: 154)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:157:
array (size=0)
  empty
(this->bind ln: 157)
C:\xampp\htdocs\includes\php\classes\DbConnection.php (at line: 145)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:146:string 'INSERT INTO Pallet_Production (ID, Pallet_Tag_Number, Date_Printed, Time_Printed, Pack_Date, PackDate_Code, Quantity, Commodity, Pack_Location, Storage_Location, Storage_Room_No, Variety, Lid_Label, Size, Grade, Ship_Status, Date_Shipped, Time_Shipped, Qty_Shipped) VALUES (:ID, :Pallet_Tag_Number, :Date_Printed, :Time_Printed, :Pack_Date, :PackDate_Code, :Quantity, :Commodity, :Pack_Location, :Storage_Location, :Storage_Room_No, :Variety, :Lid_Label, :Size, :Grade, :Ship_Status, :Date_Shipped, :Time_Shipped'... (length=528)
(this->sql ln: 146)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:148:
array (size=3)
  0 => string '00000' (length=5)
  1 => null
  2 => null
(this->errorInfo: 148)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:152:
object(PDOStatement)[4]
  public 'queryString' => string 'INSERT INTO Pallet_Production (ID, Pallet_Tag_Number, Date_Printed, Time_Printed, Pack_Date, PackDate_Code, Quantity, Commodity, Pack_Location, Storage_Location, Storage_Room_No, Variety, Lid_Label, Size, Grade, Ship_Status, Date_Shipped, Time_Shipped, Qty_Shipped) VALUES (:ID, :Pallet_Tag_Number, :Date_Printed, :Time_Printed, :Pack_Date, :PackDate_Code, :Quantity, :Commodity, :Pack_Location, :Storage_Location, :Storage_Room_No, :Variety, :Lid_Label, :Size, :Grade, :Ship_Status, :Date_Shipped, :Time_Shipped'... (length=528)
(vdtemp ln: 152)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:153:
object(PDOStatement)[4]
  public 'queryString' => string 'INSERT INTO Pallet_Production (ID, Pallet_Tag_Number, Date_Printed, Time_Printed, Pack_Date, PackDate_Code, Quantity, Commodity, Pack_Location, Storage_Location, Storage_Room_No, Variety, Lid_Label, Size, Grade, Ship_Status, Date_Shipped, Time_Shipped, Qty_Shipped) VALUES (:ID, :Pallet_Tag_Number, :Date_Printed, :Time_Printed, :Pack_Date, :PackDate_Code, :Quantity, :Commodity, :Pack_Location, :Storage_Location, :Storage_Room_No, :Variety, :Lid_Label, :Size, :Grade, :Ship_Status, :Date_Shipped, :Time_Shipped'... (length=528)
(pdostmt ln: 153)
C:\xampp\htdocs\includes\php\classes\DbConnection.php:154:
array (size=19)
  ':ID' => string '165357' (length=6)
  ':Pallet_Tag_Number' => string '22020015' (length=8)
  ':Date_Printed' => string '2022-07-21 00:00:00' (length=19)
  ':Time_Printed' => string '09:09:33' (length=8)
  ':Pack_Date' => string '2022-07-21 00:00:00' (length=19)
  ':PackDate_Code' => string '202-01' (length=6)
  ':Quantity' => string '42' (length=2)
  ':Commodity' => string 'APPLES' (length=6)
  ':Pack_Location' => string '_redacted_ Cold Storage' (length=19)
  ':Storage_Location' => string 'PCS HIGHLAND REF. ROOMS' (length=23)
  ':Storage_Room_No' => string 'R01' (length=3)
  ':Variety' => string 'DELICIOUS RED' (length=13)
  ':Lid_Label' => string 'S/R  B&B' (length=8)
  ':Size' => string '3 lb' (length=4)
  ':Grade' => string 'US EXTRA FANCY' (length=14)
  ':Ship_Status' => string 'N' (length=1)
  ':Date_Shipped' => null
  ':Time_Shipped' => null
  ':Qty_Shipped' => string '0' (length=1)
(this->bind ln: 154)
( ! ) Warning: PDOStatement::execute(): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'R01' for column `_redacted_`.`Pallet_Production`.`Storage_Room_No` at row 1 in C:\xampp\htdocs\includes\php\classes\DbConnection.php on line 156
 
C:\xampp\htdocs\includes\php\classes\DbConnection.php:173:
array (size=19)
  ':ID' => string '165357' (length=6)
  ':Pallet_Tag_Number' => string '22020015' (length=8)
  ':Date_Printed' => string '2022-07-21 00:00:00' (length=19)
  ':Time_Printed' => string '09:09:33' (length=8)
  ':Pack_Date' => string '2022-07-21 00:00:00' (length=19)
  ':PackDate_Code' => string '202-01' (length=6)
  ':Quantity' => string '42' (length=2)
  ':Commodity' => string 'APPLES' (length=6)
  ':Pack_Location' => string '_redacted_ Cold Storage' (length=19)
  ':Storage_Location' => string 'PCS HIGHLAND REF. ROOMS' (length=23)
  ':Storage_Room_No' => string 'R01' (length=3)
  ':Variety' => string 'DELICIOUS RED' (length=13)
  ':Lid_Label' => string 'S/R  B&B' (length=8)
  ':Size' => string '3 lb' (length=4)
  ':Grade' => string 'US EXTRA FANCY' (length=14)
  ':Ship_Status' => string 'N' (length=1)
  ':Date_Shipped' => null
  ':Time_Shipped' => null
  ':Qty_Shipped' => string '0' (length=1)
(this->bind ln: 173)

              Looks like the (this->errorInfo: 148) is now related to the DESCRIBE statement? or, is it the this->sql? i don't normally debug like this. am I correct that the line 148 var_dump(_this->error) is RE: the array (size=3)
              0 => string '00000' (length=5)               line, and NOT below at C:\xampp\htdocs\includes\php\classes\DbConnection.php:152:
              object(PDOStatement)[4]
              public 'queryString' => string 'INSERT INTO Pallet_ ...              ?


              EDIT: I see they're running php 5 on their remote development server, so that's one major difference:
              PHP Version 5.6.40-60+0~20220627.67+debian10~1.gbp1f7ffd

              ajaxStardust EDIT: i see there's more/ different output after I submit the REQUEST to update the table:

              That was the whole point of the debugging that is going on. You were getting an error when you performed that operation.

              You are now getting the initial error about the - Incorrect integer value: 'R01' for column _redacted_.Pallet_Production.Storage_Room_No ...,, which means that the definition for that column is for an integer datatype, not for a string datatype.

                pbismad

                Thank you SOOOO much for helping me through that. It was very kind, and thoughtful of you, and very much appreciated.

                screenshot

                It was that column field all along-- that simple change from Int to varchar is all it required. Everything moves slowly w/ that remote connection, and I did get confused about the development / production server database names. I did something wrong at some point regarding that, but...
                i'm super happy now! I actually worked on that thing since May! (albeit, not since July, but it came back to haunt me! lol)

                Thanks again. phpbuilder.com rocks!
                😃

                  Write a Reply...