Re: Config or Installation Issue
1) Result of show create prodfarm;
'prodfarm', 'CREATE TABLE `prodfarm` (\n `Codigo_Prod` varchar(10) NOT NULL,\n `Nombre` varchar(45) DEFAULT NULL,\n `Prin_Activo` varchar(150) DEFAULT NULL,\n `Forma_Farma` varchar(45) DEFAULT NULL,\n `Activ_Terap` varchar(65) DEFAULT NULL,\n `No_Reg_SSA` varchar(35) DEFAULT NULL,\n `Fecha_Revision` date DEFAULT NULL,\n `Presentaciones` varchar(120) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,\n `Caducidades` varchar(30) DEFAULT NULL,\n PRIMARY KEY (`Codigo_Prod`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
2) Sample Data. I'm allowed to send you 15 of the total of source records that show trouble or issues in the data containing one or more accented words, to be inserted in the field named Presentaciones. The other accented data, to be inserted in fields prior to the field Prentaciones, such as Nombre, Prin_Activo, Forma_Farma or Activ_Terap, present NO ISSUES while CREATED. You can verify it with the records that can be created with the sample data I am enclosing below:
REG-0023, 08/03/2006, FUN-COR, F-luconazol 100 mg, Cápsulas, Antimicótico, Caja con 10 cápsulas, 418M2002 SSA IV, 08/10/2002, 24 meses
REG-0024, 08/03/2006, HIPO-SPORT, D-iclofenaco Dietilamonio 1.16 g/100g,Gel, "Antiflamatorio con acción analgécica, Antirreumatico", "Caja c/tubo c/30, 60 y 80g y Caja c/10 sobres con 5 g", 456M2003 SSA VI,13/06/2005, 24 meses
REG-0025, 18/09/2006, HI-POFEMME, Nitrato de Mi-conazol 2%, Crema Vaginal, Antimicótico de amplio espectro ,"Caja c/Tubo c/20g, 30g y 78g c/ 3 aplicadores",456M90 SSA VI,01/08/2006, 24 meses
REG-0026, 08/03/2006, HI-PODEN, HCl de L-ídocaina (0.4056 g/100 ml) y (4.056mg/1 ml),Solución, Anestesico Local, Caja c/Frasco y gotero c/10 y 20 ml (FASA),399M2004 SSA VI,07/10/2005,24 meses
REG-0027, 08/03/2006, HIPO-DEN, HCl de Lídocaina 3.40 mg, Gel, Anestesico Tópico, Caja c/ tubo con 10 g y 20g,176M2004 SSA VI,07/10/2005, 24 meses
REG-0028, 08/03/2006, INM-UNOL, Glicofosfopeptical 500mg, Cápsulas, Inmunomodulador, Caja con 15 cápsulas,085M88 SSA IV,30/08/1999, 24 meses
REG-0029, 08/03/2006, IND-AFLEX, I-ndometacina 2.50 g/100g, Crema, "Analgesico, Antiflmatorio no esteroideo","Caja c/ tubo c/40, 60 y 100 g,c/ instructivo", 575M2004 SSA IV, 07/10/2005, 24 meses
REG-0045,08/03/2006, OL-AN, La-nsoprazol 30 mg, Cápsulas, " Tratamiento de Ulcéra Peptica, Gastrica, Duodenal", "Caja.c/blister ó Fco.c/7, 14, 21 y 28 cápsulas", 313M2004 SSA IV,12/07/2004, 24 meses
REG-0046,08/03/2006, PAN-VERMIN, M-ebendazol 100 mg, Comprimidos, Antihelmético, Caja con 6 comprimidos y (Fasa), 89272 SSA VI, 20/03/2006, 36 meses
REG-0055,08/03/2006, TRA-LIC, HCl de T-ramadol 25 mg y 50 mg, Cápsulas, Analgésico, Caja c/ Blister con 10 cápsulas, 426M94 SSA IV, 20/10/2001, 60 meses
REG-0056,08/03/2006, TRA-LIC, HCl de T-ramadol 100mg/ml,
Solución, Analgésico, Caja con Frasco con 10 ml y gotero, 378M94 SSA IV,10/12/1999, 24 meses
REG-0057,08/03/2006, TRA-LIC, HCl de T-ramadol 100 mg, Supositorios, "Analgésico, narcótico", Caja con 5 y 10 Supositorios, 500M94 SSA IV, 28/08/2001, 24 meses
REG-0064,08/03/2006, Z-ORAL, O-meprazol 20 mg, Cápsulas, Tratamiento de la 'Úlcera Péptica gástrica y duodenal, "Caja c/blister ó Fco c/7,14,21 y 28 cápsulas", 319M2004 SSA IV,12/07/2004,24 meses
REG-0065,08/03/2006, Z-OTRIL, It-raconazol 100 mg, Cápsulas, Antimicótico, Caja c/ Blister / 15 ó 30 cápsulas, 492M2002 SSA IV,25/10/2002, 24 meses
REG-0066,08/03/2006, O-XAN, N-itazoxanida 200mg, Tabletas Dispersables, "Antigiardiasico, Triconomials, Antiamibiatico y Antihelmíntico", "Caja con 3, 6 y 14 tabletas", 028M2005 SSA IV, 27/01/2005, 24 meses
3) In my software, I import records from a text file (filename.cvs), by means of a process that reads all the fields of the file. Each read field is assigned to variables, which map the corresponding fields of the prodfarm table structure. The variables are arranged in a string sql sentence to prepare the Insert INTO prodfarm sequence, including fields and values syntactically correct to execute an SQL command, according to previous connection command containing parameters needed to make a successful connection to the server, schema, and table name. This is supported from our Software Platform tool which worked previously when this app used MSSQL db.
Below, I enclose some of the generated sentences, which are also written in another text file:
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0023', 'FUN-COR', 'F-luconazol 100 mg', 'Cápsulas', 'Antimicótico', '418M2002 SSA IV', '2002-10-08', 'Caja con 10 capsulas', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0024', 'HIP-OSPORT', 'D-iclofenaco Dietilamonio 1.16 g/100g', 'Gel', 'Antiflamatorio con acción analgécica, Antirreumatico', '456M2003 SSA VI', '2005-06-13', 'Caja c/tubo c/30, 60 y 80g y Caja c/10 sobres con 5 g', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0025', 'HIP-O FEMME', 'Nitrato de M-iconazol 2%', 'Crema Vaginal', 'Antimicótico de amplio espectro', '456M90 SSA VI', '2006-08-01', 'Caja c/Tubo c/20g, 30g y 78g c/ 3 aplicadores', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0026', 'HI-PODEN', 'HCl de L-ídocaina (0.4056 g/100 ml) y (4.056mg/1 ml)', 'Solución', 'Anestesico Local', '399M2004 SSA VI', '2005-10-07', 'Caja c/Frasco y gotero c/10 y 20 ml (FASA)', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0027', 'HI-PODEN', 'HCl de Lí-docaina 3.40 mg', 'Gel', 'Anestesico Tópico', '176M2004 SSA VI', '2005-10-07', 'Caja c/ tubo con 10 g y 20g', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0028', 'IN-MUNOL', 'Gl-icofosfopeptical 500mg', 'Cápsulas', 'Inmunomodulador', '085M88 SSA IV', '1999-08-30', '´ Caja con 15 capsulas', '24 meses' );
Insert INTO prodfarm (Codigo_Prod, Nombre, Prin_Activo, Forma_Farma, Activ_Terap, No_Reg_SSA, Fecha_Revision, Presentaciones, Caducidades ) Values ( 'REG-0029', 'IND-AFLEX', 'I-ndometacina 2.50 g/100g', 'Crema', 'Analgesico, Anti inflamatorio no-es-teroideo', '575M2004 SSA IV', '2005-10-07', 'Caja c/ tubo c/40, 60 y 100 g,c/ instructivo', '24 meses' );
My program executes each sentence directly from my code, but you can use ech statement directly in the mysql workbench (as I do) or in the system cmd screen of MySQL client. The result is the same!
I hope I answered all your 3 request questions Peter, and thanks for following this support thread.
Luis
P.S. I had to insert a hyphen in the Name and Prin_Act fields because when I tried to post my reply I got the message "A word you used in your post has ben banned from use. Please bla bla bla