Hello everyone,

I've got some data in hold file and I want to delete corresponding rows from mysql table where entry_no is the same, but mysql just throws syntax error. I can't make this to work.
The same problem exists if i try to delete rows from MSSQL table.

I connect through ODBC.


Please help.
Any suggestions appreciated.


Code:

 
TABLE FILE SOME_DATA_HOLD
PRINT ENTRY_NO
ON TABLE HOLD AS HOLD_TO_DELETE FORMAT ALPHA
END

MODIFY FILE  MYSQL_TABLE1
FIXFORM FROM HOLD_TO_DELETE
MATCH ENTRY_NO
ON MATCH DELETE
ON NOMATCH REJECT
LOG DUPL MSG OFF
DATA ON HOLD_TO_DELETE
END
-RUN
 

Result:

(FOC1400) SQLCODE IS 1064 (HEX: 00000428) XOPEN: 42000
: [37000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.51a-community-nt]You have a
: n error in your SQL syntax; check the manual that corresponds to your My
: SQL server version for the right syntax to use near '"Entry_No",T1."time
: stamp",T1."Customer_No",T1."Contract_No",
L (FOC1406) SQL OPEN CURSOR ERROR. : MYSQL_TABLE1

This message has been edited. Last edited by: array,

Array,

Please also post your master and access file for the mysql table.
I have been playing around with mysql a little bit, and found the odbc driver to be very very strict. And by the look of it, the problem is not with the modify code, so the other thing to look at are the master/access files.

access file :

 
 SEGNAME=NAVISION_CTRL_MYSQL, TABLENAME=navision_ctrl, CONNECTION=mysql_local, 
   KEYS=1, $

 

master file :

  
FILENAME=NAVISION_CTRL_MYSQL, SUFFIX=SQLODBC ,
 REMARKS='InnoDB free: 4096 kB', $
  SEGMENT=NAVISION_CTRL_MYSQL, SEGTYPE=S0, $
    FIELDNAME=ENTRY_NO, ALIAS=Entry_No, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=TIMESTAMP, ALIAS=timestamp, USAGE=A19, ACTUAL=A19,
      MISSING=ON, $
    FIELDNAME=CUSTOMER_NO, ALIAS=Customer_No, USAGE=A20V, ACTUAL=A20V, $
    FIELDNAME=CONTRACT_NO, ALIAS=Contract_No, USAGE=A20V, ACTUAL=A20V, $
    FIELDNAME=PASLAUGOS_TIPAS, ALIAS=Paslaugos_tipas, USAGE=A30V, ACTUAL=A30V, $
    FIELDNAME=PRIEZASTIS, ALIAS=Priezastis, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=VARTOTOJO_ID, ALIAS=Vartotojo_ID, USAGE=A20V, ACTUAL=A20V, $
    FIELDNAME=PARAISKOS_DATA, ALIAS=Paraiskos_data, USAGE=A19, ACTUAL=A19, $
    FIELDNAME=PINIGAI, ALIAS=Pinigai, USAGE=P10.2, ACTUAL=P10, $
    FIELDNAME=VEIKSMAS, ALIAS=Veiksmas, USAGE=I6, ACTUAL=I2, $
    FIELDNAME=VEIKSMO_KOMENTARAS, ALIAS=Veiksmo_komentaras, USAGE=A50V, ACTUAL=A50V, $
    FIELDNAME=FLAG_OK, ALIAS=FLAG_OK, USAGE=I6, ACTUAL=I2, $
    FIELDNAME=IJUNGTI_JEIGU_NE, ALIAS=Ijungti_jeigu_ne, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=VEIKSMO_DATA, ALIAS=Veiksmo_data, USAGE=A19, ACTUAL=A19, $

table names are different from the first post but i hope you get the idea.

Thanks.

Hmm,
What bugs me here is that the sql error speaks of at least 4 fields, while you only reference the first field. Also the access file only indicates 1 key field.
You sure that the number of keys correspond with the real data?
Could you capture the generated sql from the modify and see if you can pinpoint some syntax error there?
What happens if you take the generated code and run it in mysql native? Same error?

Thanks for helping me, GamP.
Sorry, can you tell how to capture generated SQL statement??

Some topics ago Francis M gave his version of setting up traces.
The below code is repeated from that thread.

-*-- Set up SQL tracing --------------------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show SQL generated sub-statement trace ----------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON

Put this code in front of your modify code and run the procedure.
You should get the sql statement that is being used to delete the record.
Hope this helps in locating the source of the problem.

Try regenerating your master using the native SQL Server adapter as opposed to ODBC. Your suffix in the master would then be SQLMSS. This is a much smarter adapter and will generate better SQL for you.

Ginny,

This is MySQL, not MSSql.....
As far as I know, there is only odbc to talk to MySQL.

As suggested by GamP, run the fex with the SQL Trace - you will see the SQL generated by WF.

quote:

The same problem exists if i try to delete rows from MSSQL table.

I guess I didn't read carefully enough because of the above original statement from Array.

Hello again,

I just got back to work and i tried to add sql traces to my code, just like GamP suggested, but unfortunately there is no sql output...
Just theese lines :

  
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0
 0 TRANSACTIONS:         TOTAL =     0  ACCEPTED=     0  REJECTED=     0
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0

what i'm trying to do is to take some data from MSSQL and put it into Mysql, and vice versa.

Someone said that ODBC adapter has problems when you need to send delete statement, but I'm not sure about this.

I think I'll just rewrite everything in Java.

Thank you.

This message has been edited. Last edited by: array,

Array

Your problem is that you dont have WRITE=YES in your .acx like this.

 SEGNAME=SEG01, TABLENAME=R1241CRX, CONNECTION=WF_REPORTING, KEYS=01, WRITE=YES, $

Use this code to trace if still probs cos I just test it with a modify

-* File sqltrace.fex
-* check on includes translation
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

As this code shows...

SET TRACEUSER=ON
 SET TRACEOFF=ALL
 SET TRACEON=STMTRACE/1/CLIENT
 SET TRACEON=SQLAGGR/1/CLIENT
 -*SET TRACEON=WHOPT/1/CLIENT
 SET TRACEON=SQLTRANS/3/CLIENT
 SET TRACEON=?
 SET XRETRIEVAL=OFF
 SET EMPTYREPORT=OFF
 MODIFY FILE R1241CRX
 FIXFORM FROM R1241_CR_LOAD
 MATCH CR
 ON NOMATCH INCLUDE
 ON MATCH IF D.LOCKED EQ 'Y' THEN GOTO TOP ;
 ON MATCH UPDATE LABHRS LABCOST SUBCOST MATCOST DATE_PRINTED
 DATA ON R1241_CR_LOAD
 END
 -RUN
 Use SET TRACEON=<name>/<lvl>/CLIENT to send traces to the client
 Name     Level Description                       Set  Comp.ID
 STMTRACE  1    SQL/MDX Generated Statement Trace  Y     AE
 SQLTRANS  3    SQLTRANS Memory Dump               Y     BL
 SQLAGGR   1    SQL Aggregation Trace              Y     BR
 13.26.43 AE   SELECT T1."CR",T1."DATE_PRINTED",T1."LOCKED",T1."CUST_REF",
 13.26.43 AE   T1."CHARGEABLE",T1."PROGRESS",T1."LABHRS",T1."LABCOST",
 13.26.43 AE   T1."SUBCOST",T1."MATCOST",T1."AGREED_PRICE",T1."DATE_SENT_CUST",
 13.26.43 AE   T1."DATE_RETURNED_CUST",T1."COMMENTS",T1."AT_RISK",T1."TF_NO",
 13.26.43 AE   T1."EMAILED",T1."FIVE_CARD_TRICK" FROM R1241CRX T1 WHERE
 13.26.43 AE   (T1."CR" = ?);