Forum Replies Created
-
AuthorPosts
-
peterlaursen
ParticipantThe tab referred to is a 'Query' tab in the editor area. You are prompted if you want to save the content to a (script)file.
peterlaursen
Participant1) To upload just rename the DUMP to .txt or zip it! Except for compressed images (.jpg,.gif,.png) and very short .txt file we appreciate if users would zip yo save disk space!
2) First try to disable autocomplete in tools .. preferences. If that solves the issue, the reason is that the sqlite databases used by autocomplete are corrupted. We had a few more examples of this. If that is the issue, then simply delete or rename the 'tags' folder where those files are. New files will be created for every connection next time connection is establised
Where they are depends on Windows version
2K/XP/2003: C:Documents and Settings{user}Application DataSQLyogTags (as far as I remember!)
Vista/2008: C:Users{user}AppDataRoamingSQLyogTags
We will update the SQLite libary. That should solve this issue.
peterlaursen
ParticipantI don't think a WHILE .. END WHILE loop is the way! A single query should handle this. If not the schema design is not good!
Say you have the tables:
CREATE TABLE `tab1` (
`chief_id` int(11) default NULL,
`client_id` int(11) default NULL,
`val1` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `tab2` (
`chief_id` int(11) default NULL,
`client_id` int(11) default NULL,
`val2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
you can do like this in query builder
[attachment=1010:twojoins.jpg]
to generate the query
Code:SELECT
`tab1`.`chief_id`
, count(`tab1`.`chief_id`)
, `tab1`.`client_id`
, `tab1`.`val1`
, `tab2`.`val2`
FROM
`yognew`.`tab1`
INNER JOIN `yognew`.`tab2`
ON (`tab1`.`chief_id` = `tab2`.`chief_id`) AND (`tab1`.`client_id` = `tab2`.`client_id`)
GROUP BY `tab1`.`chief_id`;Also you may of course wrap it in a CREATE (TEMPORARY) TABLE or CREATE VIEW statement like
Code:CREATE TEMPORARY TABLE mytemptable as SELECT {continue as above}or
Code:CREATE VIEW myview as SELECT {continue as above}(the good think with the VIEW is that when underlying tables changed the VIEW also will automatically)
What I am not able to see is
1) if both conditions are reguired in the JOIN. Maybe only joing one from each table will return what you need here or maybe creating two seperate queries and merge the results with UNION
Code:SELECT {as above with 1st JOIN} UNION SELECT {as above with 2nd JOIN}I simply cannot tell as I do not know what data in the columns mean exactly
2) what to count and what group by? “GROUP BY chief_id”, “GROUP BY client_id”, “GROUP BY chief_id,client_id”. I do not understand your needs in detail!
peterlaursen
ParticipantIf you are using the ENTERPRISE version: In tools .. preferences (powertools tab) there is an option to define the storage location for the auto-complete ('tags') files. If not defined they will likely be stored in the 'roaming profile' on the domain server.
Please try specifying a local folder for those files!
peterlaursen
ParticipantI cannot know the 'logic' of this example. Please first try the GUI query builder of the ENTERPRISE (TRIAL) version yourself . 🙂
I also think that duplication 2 rows in both tables is not a good idea – if that is what you are doing.
But for this example I would consider simply using a WHERE-clause to establish the JOIN
select t1.column1, t1.column2, t1.column3, t2.column3 from t1,t2 where ((t1.column1 = t2.column1) and (t2.column2 = t2.column2))
or
select t1.column1, t1.column2, t1.column3, t2.column3 from t1.t2 group by t*.column* having ((t1.column1 = t2.column1) and (t.column2 = t2.column2))
But also in JOIN you can use AND (in Query Builder just draw 2 lines between tables and see what it generates)
Without understanding the organisation of the data in detail I cannot be perfectly sure. At least I would nead exact structure dumps – not just a *verbose* description like this.
peterlaursen
ParticipantLooks much like there is a networking problem!
It may not be an issue with the ISP. It can also be your telecom company. A bad cable or unit somewhere on the line for instance. Do you have an option to try from somewhere else (home/work/family/friends) preferably in another (part of) town?
peterlaursen
ParticipantOK .. now I understand the *ODBC designer'.
I think we never had reports from users using those options of the text driver before. Most users using the Migration toolkit will migrate from other RDMS (including access) and more specific/proprietary systems like ERP systems, financial systems etc. Also we have quite a lot of users migrating *lots* of flat csv files (one migrates around 40000 files every week with pollution data collected all over US by different types of measuring equipment)
But it seems we do not support the reading of the table schema from this format (fixed width with structure in schema.ini). We will check this in detail. And also I found a problem when migrating a fixed width CSV generated with SQLog CSV-export – so also this should be checked!
You do no have the option to either
* connect directly with ODBC to the data source without using temporary/text files? (that is best option)
* or generate data in a single variable lenght file with a header containing the column names? (but admittedly you will then have to do the mapping manually in the wizard first time)
?
peterlaursen
Participantactually this GUI imperfection is already registered:
peterlaursen
ParticipantYou should learn the use of GROUP BY with 'aggregate functions'. One such function is 'count'
MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/gro…-modifiers.html
Try understand this:
Code:SELECT client_id, count(client_id) FROM thetable GROUP BY client_idand
Code:SELECT client_id, count(client_id) FROM thetable GROUP BY client_id ORDER BY client_id descand
Code:SELECT client_id, count(client_id) FROM thetable GROUP BY client_id asc, somevalue descpeterlaursen
ParticipantAn example:
Create a simple table with an autoincrement PK and a NULLABLE varchar filed
SQLyog will send (from SQLyog HISTORY tab):
Code:create table `test3`.`rrrrr`( `id` int NOT NULL AUTO_INCREMENT , `test` varchar(30) , PRIMARY KEY (`id`)) ;SHOW CREATE TABLE (from SQLyog OBJECT tab) now reads
Code:CREATE TABLE `rrrrr` (
`id` int(11) NOT NULL auto_increment,
`test` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8The point is that if not either:
* defined NOT NULL
* some explicit default defined
.. it will be defined “default NULL”. A NULLABLE column with no explicit default is 'default NULL' in MySQL!
BTW: before you are asking you could read this too (where we also make use of the server ability to define explicit defaults):
http://webyog.com/faq/content/8/159/en/how…ith-sqlyog.html
as well as this: http://webyog.com/faq/content/8/99/en/does…l-keywords.html
.. I think most of it was like that in your time too, though could be slightly changed.
peterlaursen
ParticipantNice to hear that! 😀
peterlaursen
Participant“but allow atleast 'n' and 't'” Nope! Over my dead body! Because we need to handle escapes a *literals* in strings, identifiers and comments. And they shuld not have effect as linebreks, tabs etc. in the editor. They should just be *literals*- Both 'Riteshnnow in US' and 'Peterrnfrom Denmark' are *just strings* like 'æøå' and 'blahblihbluh'!
We need a GUI solution for a GUI tool! The best GUI solution I can think of is a(n optional) multiline text-area in search and replace.
peterlaursen
Participantpeterlaursen
Participant[attachment=1007:replace.jpg]
ok – confirmed! In the example above 'bli' gets replaced with 'blirn' (I use rn on Windows .. not sure how linebreaks are implemented in Wine)
But isn't hat how you would expect a GUI editor and GUI search to work? “r”, “n”, t”, “\” etc are *literal strings* and I think it should be like that!
Consider: If I have a statement like
SELECT INTO OUTFILE … ESCAPED BY '\' ENCLOSED BY ',' TERMINATED BY 'n'
and want to change to
SELECT INTO OUTFILE … ESCAPED BY '@' ENCLOSED BY '|' TERMINATED BY 'rn'
… escape sequences will need to be handled as *literals*! They should not be interpreted as C-escape sequences in my opinion. It should simply *read* and *print* them *as literals*!
But you are right search (and replace) spanning multiple lines is not avial currently.
We could look into implementation in advanced editors, but what would you suggest?
It was exactly like this in your time here, so nobody qualifies better for suggesting than you!
I would consider to make the 'search' and 'replace' text-fields >> multi-line text-boxes simply.
peterlaursen
Participant1)
This “What I call “ODBC Designer” is the tool which pops up if I define a new ODBC-Source.” I still do not understand. Nothing like this pops up on my system! See attached!
[attachment=1006:odbcconfig.jpg]
I think this is something non-standard installed with a specific application on your system? Please explain this! Attach a screenshot of this 'popup' please!
2)
Please provide a valid archive (with no images etc. inside). If you cannot expose these data/schema you will have to create a 'dummy' example. It will have to be complete and valid. Tell in detail how you are able to import it to any server with any kind of application (attach screenshots if required).
3)
Also explain in broad terms what is your 'project'. You want to import data to MySQL of course. But from where do those data come? Likely there is a much easier option than what you are trying now!
-
AuthorPosts