[peterlaursen]

Thanks .. I think it is clear now!

this SQL_WHERE

Quote:

type ='corporate' and paid_thru = '2005-12-31'

will easily handle table 'name' (remember to use exact date-syntax as demanded by MS SQL server)

But the other one cannot be done with ODBC-import, I believe.

No SQL_WHERE is possible (or I am just not clever enough!)

But I have another solution. Just import everything (no SQL_WHERE) from table 'corp_profile'. And after import then delete the rows that should not be there with this SQL

Quote:

use db_name;

create table corp_profile_cleanup select corp_profile.id, corp_profile.corp_desc from corp_profile,name where type ='corporate' and paid_thru = '2005-12-31' and corp_profile.id = name.id;

drop table corp_profile;

rename table corp_profile_cleanup to corp_profile;

You can do that with “Notification Service” and the and can be executed from one bat-file, and that bat-file can be scheduled with the windows scheduler as I have described it in my WhitePaper here http://www.webyog.com/whitepaper/Using_SJA_2006_07_18.pdf (look for paragraph named “Combining Things”.

But you should NOT import the FOREIGN KEY here (!) as it might not let you drop table 'corp_profile' . When I say “might not” that depends on whether it is a one-one, one-many or many-one relationship! Best is not to import FK's here at all.

Create it (or them if you want a one-to-one relationsship) afterwards – manually or with a concluding ALTER TABLE statement in the jobfile, like

Quote:

alter table  `name` add foreign key (id) references `corp_profile` (id)

(or the other way or both!)

Attached image should document that it works!

[peterlaursen]

Can you describe the relations between the tables ?

Is there a relationsship between id in NAME and id in CORP_PROFILE?

(that' how I understand it!) or is the relationsship between other columns in the two tables ? or is the not a relationship defined with the datastructure at all (in the meaning of a Foreign Key), but nevertheless a correspondance in data ?

If you can write an ordinary SQL (using the syntax of the source D:cool: like

SELECT * FROM table_name WHERE {wherecondition] that works

then the ODBC-tool also can! simply put the {wherecondition] in the SQL_WHERE.

Quote:

In NAME, import only member_type is Corporate and paid_thru is 12/31/2005.

This is a quite ordinary SQL, so this is no problem!

Quote:

In CORP_PROFILE, only import records that have records in NAME linked by id.

this is still quite unclear to me what you mean here!

There are some rows in CORP_PROFILE that are not referenced by (or does not correspond to) any row in NAME. And those rows in CORP_PROFILE that are not referenced (or where there is no correspondance) shall not be imported.

Is that how to understand it ?

[peterlaursen]

you are welcome!

but of course you are welcome too to buy a few site licenses for SQLyog 😀

to enter data into a datetime from SQLyog just enter it as a 14-character string

like “20050730111112” (year2005 month07 day30 hour11 minute11 second12)

Simply omit any input mask. I believe that works with JDBC too (?). But maybe VB wants an input mask – I don't know!

In the example below I typed those values

“20050730111112”

“20:00:00”

“00:08:25”

“00000000082500”

and after save and retrieve the are like image!

Maybe this would be some help for debugging your problem!

[peterlaursen]

One posibility is sub-queries

In MySQL 4.1 and above thi is legal SQL

select * from childtable where childcolumnname = (select whatyouwant from parenttable where parentcolumnname = whereyouwant);

in the SQL-WHERE-window of SQLyog you enter then

Quote:

childcolumnname = (select whatyouwant from parent where parentcolumnname = whereyouwant)

I don't know the exact syntax for subqueries with other DB-servers.

Also I don't know if your data are structured so that you can use it!

And besides tables don't need to be related to use subqueries.

Wait for some SQL expert to show up!

[peterlaursen]

BTW ..

that reminds me that I have a minor/cosmetical issue too. Image below is when you open a odbc-import-jobfile with the sync tool.

I think error message should instead be something like “this is not a legal jobfile for this jobtype” or something like that …

And there also seems to be an image display problem with the new forum software!

[peterlaursen]

I shall not attempt to answer the question! My SQL knowledge is not sufficient!

But if there is a WHERE statement that is legal with the source database that allows for it, and if it is implemented in the ODBC driver then: YES, if not: NO!

[peterlaursen]

I second this!

Actually it has annoyed me a little bit too. Just forgot about it!

And for completeness I would like an “execute” button form the opening screen of the wizard som that you wont have to step through them all.

And I think that these requests apply to all SJA wizards! None of it is important. Just something for a “lazy day” 🙂

[peterlaursen]

I believe that what I wrote last was correct!

http://msdn.microsoft.com/library/default…._oa-oz_3qpf.asp

says

Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

THAT's WHY both != and <> fail. They can't be used with MS SQL TEXT data types

this:

http://msdn.microsoft.com/library/default&#8230;._ta-tz_3zaq.asp

describes functions to work with texts

I believe you must use

SUBSTRING(text_column, start, length)

SUBSTR() fundction described here

http://msdn.microsoft.com/library/default&#8230;._ta-tz_3zaq.asp

or in your case:

SUBSTRING(service_production_description,0 (or 1),XXXXXXXXX) <> ''

would work.

Maybe you will even have to RTRIM(SUBSTRING(…)) …

I can't find information aabout whether substring trims trailing blanks!

about RTRIM

http://msdn.microsoft.com/library/default&#8230;._ra-rz_6xm5.asp

To also test for NULL values:

SUBSTRING(service_production_description,0 (or 1),XXXXXXXXX) <> '' OR service_production_description IS NOT NULL

XXXXXXXXX won't have to be a high number since it is only used for SQL_WHERE. if you are sure that there are no leading blanks in data “1” will do … Of course you can also LTRIM() …

NOTE that <> is SQL standard != is C-syntax and nonstandard though supported.

VARCHARs in MS SQL can be very long. Do you need TEXTs ? VARCHARS perform much better!

[peterlaursen]

hmmmm …

let Ritesh look into it tomorrow!

But my personal belief is that the SQL_WHERE must follow SQL syntax of the source database. When you get these error mesaages it would mean that you are writing wrong syntax. In a lot of respects MySQL syntax is more “relaxed” and “forgiving” than most other SQL-dialects. So if it executes correctly against a mySQL database but not a MS SQL databases that could/must be because of difference in SQL dialects.

Can you execute the same SQL against MS SQL DB-server from a “pure” MS SQL -client without errors ?

BTW when you are talking of “datatype TEXT” is it then the MS SQL datatype TEXT or MySQL datatype TEXT. I understand it as MS SQL. And what a MS SQL TEXT is I really don't know. 🙁

Maybe you'll have to convert to a string using some function for that before comparing with the empty string ''. But you probably know better!

[peterlaursen]

but the compound statement

service_production_description != '' OR service_production_description IS NOT NULL

what is that?

I don't know too much about string operators in MS SQL syntax but what would this one give:

service_production_description <> ''

and

service_production_description <> '' AND service_production_description IS NOT NULL

Idon't know what this

service_production_description <> ''

returns in MS SQL if variable is NULL (true, NULL or an error)

[peterlaursen]

BTW

I think the SQL should be:

service_production_description != '' OR service_production_description IS NOT NULL

to test for both empty strings and NULL values.

[peterlaursen]

but it exexutes correctly from commandline ??

[peterlaursen]

that is 530 characters!

[peterlaursen]

Quote:

Timestamp is also affected.

But if you use the TRANSFORM facililty of SQLyog and choose MySQL TIMESTAMP datatype, will it then import correct ?

I don't know how a TIMESTAMP is stored internally in MS SQL. In MySQL it is stored as a string.

If the ODBC-driver does not tell SJA that variable is a TIMESTAMP, you'll have to!

The ODBC-driver for MS-ACCESS works fine with SQLyog and DATE, TIME, DATETIME and TIMESTAMP variables.

[peterlaursen]

It is an issue with the ODBC-driver, I believe!

When I for instance

1)export from Access to MySQl using the MyODBC driver

and

2)import with SQLyog using the M$ ODBC driver for Access

… I won't always get the same datatypes

But then SQLyog/SJA has the “transform” option that let's you choose char/varchar/BLOB/TEXT and set length of imported variables as you want.

Which DB are you importing from and to and which ODBC driver do you use ?

[Author]

Posts