Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
5
Erster Beitrag:
vor 2 Jahren, 11 Monaten
Letzter Beitrag:
vor 2 Jahren, 10 Monaten
Beteiligte Autoren:
DerekT, Fabrice Harari

WD17>WD19 and MySql query issues

Startbeitrag von DerekT am 18.10.2014 12:02

I have a v17 application upgraded to v19 and I am also changing the DB access from HFSQL to MySQLMySql v5.6 using the v19 MySQL gateway & libMySQL.dll v5.6.21.0. -- - this has, although working, highlighted a few issues.

First issue was when creating a new analysis - for this I used drag&drop.
One of the tables had been originally created using the 'duplicate' function and modified to suit - however on paste I received a duplicate file name error.

Second issue was in generating the sql scripts to create the tables in MySQL.
In this case the table had, at some stage, been renamed and the generated script made reference to the original name.
No problem thought I, edit the script with the correct name.
MySQL created the correct table but for some reason any reference to this table (hReadSeek(), queries etc) within my code failed for some reason.
Recreating the tables in the analysis manually solved the issues but does anyone know where WD stores the table information used for the above operations?

Third issue is with queries where I have formula that concatenates fields as these return no value - query in WD has a line...
Adjuster.Forename+' '+Adjuster.Surname+' ['+Adjuster.CurrencyCode+']' AS fFullname,

If I test the WD query in Navicat the returned value = 0, if I change the line to an SQL format
CONCAT(Adjuster.Forename,' ',Adjuster.Surname,' [',Adjuster.CurrencyCode,']') AS fFullname,
the result is as expected.
Question is should, as I had hoped, the MySQLgateway handle this or do I need to modify all queries with concatination to use the CONCAT statement and revised syntax?

Antworten:

Hi Derek

Where do windev store the table name: in the analysis description, for each file, there is a logical name and a physical one, maybe one was changed but not the other?

Qry syntax: Windev will automatically translate any query made in the QUERY EDITOR...

However, if you use a "string query" that you build yourself in your code, you are ON YOUR OWN (which is another reason why I ALWAYS try to use the editor

Best regards

von Fabrice Harari - am 18.10.2014 13:21
Fabrice

The query string

Adjuster.Forename+' '+Adjuster.Surname+' ['+Adjuster.CurrencyCode+']' AS fFullname,
was produced in the QueryEditor as a calculated field.

Always returns 0 unless I use the CONCAT syntax.

Perhaps someone who is using v19 against MySQL can test it and let us know.


Edit>>>
Just checked a few other queries - works for concatenated values but not strings

von DerekT - am 18.10.2014 13:33

Re: WD17>WD19 and MySql query issues -- MORE

Another set of problems, this time with transactions.
It seems to me that the link between the MySQL gateway and the MySQL client layer (libMySQL.dll) is broken.

If I use hModify within a transaction the update is applied correctly but 'outside' of the transaction - SQLTransaction(sqlRollBack) has no effect.
If I use SQLExec(write my own query here) all works as expected.

I have sent a support request about the query issue but will wait for their response before sending one about transactions.

von DerekT - am 19.10.2014 16:54

Re: WD17>WD19 and MySql query issues -- PCS Answers

Received the answers from PCS.
The actual response time was very good ( a few days) - reason for this late post is down to my inefficiency.

The SQLTransaction functions do work with MySQL.
My error in that I was not including the connection name in the command....
SQLTransaction(sqlStart,woConnection)

I have noticed that hxxx functions and SQLExec work in different ways....
Hxxx write additions/updates immediately to disk - 'rollback' removes the changes.
SQLExec does not write additions/updates to disk untile a 'commit' is issued.

My original request regarding the use of concatenated strings in a query formula was not really answered.
The test by PCS was to run my query in 'phpMyAdmin' not in the supplied project - their answer = 'I see no WINDEV problem here as phpMyAdmin returns the very exact same result.'
I replied stating that I knew this and asked for clarification as the Help contains the statement 'Note: The queries run by HExecuteQuery and HExecuteSQLQuery are automatically "corrected" in order to be compatible with MySQL.'
There response to this was 'In this case, the SQL code is correct, therefore there is nothing to clarify.'
A bit obtuse IMHO but I guess in means that CONCAT() needs to be used.

von DerekT - am 04.11.2014 11:43
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.