Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2007
    Posts
    80

    Unanswered: how do i insert a null value from a script ?

    I have a database in MySQL that i want to make available for others.
    This includes people who use other databases than MySQL.
    For this purpose i create script files that are supposed to be ansi sql and therefore usable on many database systems.

    In MySQL i can insert a NULL value like this:
    INSERT INTO TABLE fieldX VALUES(\N);
    INSERT INTO TABLE (fieldA,fieldB,fieldC) VALUES("text",\N,12345);

    where the \N results in a null value for the field.

    But is this ANSI SQL ?
    will this work on (as many as possible) other database systems ?

  2. #2
    Join Date
    May 2007
    Posts
    80
    Hang on, found a reference that would indicate this syntax which also works with MySQL but not entirely sure its ANSI SQL :

    INSERT INTO table (fieldA,fieldB,fieldC) VALUES("text",NULL,12345);


    Would appreciate a confirmation form anyone who knows ?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BettingSherlock View Post
    Would appreciate a confirmation form anyone who knows ?
    no, that's not ANSI SQL

    the NULL is fine (the new line \N won't work anywhere else, and i'm wondering how you got it to work in MySQL)

    the doublequotes around "value" are not ANSI
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2007
    Posts
    80
    Many thanks,

    in MySQL when you do a SELECT INTO OUTFILE the null values become \N i think that's where i picked it up. Works when reading files back in as well as from scripts.

    the doublequotes around "value" are not ANSI
    Really, then how do i differentiate between string and numerical ?

    INSERT INTO table (fieldA,fieldB) VALUES("123",123);

    where fieldA is actually a varchar field and fieldB is an integer,
    do i simply omit the double quotes and rely on the definition of the table to be correct so the first 123 gets interpreted as string because the field is defined as string, and of course the second as integer because that field is defined as integer.

  5. #5
    Join Date
    May 2007
    Posts
    80
    I suppose i should use single quotes ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BettingSherlock View Post
    I suppose i should use single quotes ?
    slowly but surely you are learning the differences between ANSI SQL and MySQL SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2007
    Posts
    80
    i'll get there

    delete from racerunnerUK where concat(race_id,',',horse_id) IN ("84931,100401","84931,97256","84931,96156","84931 ,14021");
    insert into racerunnerUK (race_id,horse_id,jockey_id,trainer_id,........) values ("84931","100401","1139","940",........);

    I'm guessing this won't work either.

    The table has a primary key consisting of the combination of race_id and horse_id so as ansi sql does not know the REPLACE comand what i do first is delete then re-insert.

    The script is intended as an update to an existing table so there may be records that are not re-inserted again and i can only get rid of those by deleting them first then re-inserting those records that do still exist.

    Simply use single quotes ?

    delete from racerunnerUK where concat(race_id,',',horse_id) IN ('184931,100401','84931,97256','84931,96156','8493 1,14021');
    insert into racerunnerUK (race_id,horse_id,jockey_id,trainer_id,....etc.... ) values (84931,100401,1139,NULL,....etc....);

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, can't help you if i don't know your columns

    if you're still on MySQL for your testing, do a SHOW CREATE TABLE

    also, note that CONCAT is specific to MySQL, ANSI concatenation is accomplished with double pipe as concatenation operator

    besides, concatenation two numeric ids into a string is an awkward way to target compound PK values

    do it like this instead --
    Code:
    DELETE 
      FROM racerunnerUK 
     WHERE race_id = 84931 AND horse_id = 100401
        OR race_id = 84931 AND horse_id = 97256 
        OR race_id = 84931 AND horse_id = 96156 
        OR race_id = 84931 AND horse_id = 14021
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2007
    Posts
    80
    So the ANSI SQL equivalent would be:
    delete from racerunnerUK where (race_id || ',' || horse_id) IN ('84931,100401','84931,97256','84931,96156','84931 ,14021');

    I know the concatenation is awkward and i'm just doing that to keep the filesize of the scripts down. This table has over 1 million records. The biggest script contains data on 1 year and that can exceed 150000 records. So even though the syntax is awkward it does result in a lot smaller files. If it doesn't work as ANSI SQL then i can always swich to the syntax like you suggest.

    There are other tables as well but this one is the only one with a combined primary key.


    CREATE TABLE `sbs_racingdb`.`racerunneruk` (
    `HORSE_ID` mediumint(1) unsigned NOT NULL,
    `RACE_ID` mediumint(1) unsigned NOT NULL COMMENT 'Unique ID for a race specific to SBS',
    `runnerstatus` varchar(25) default NULL COMMENT 'Entry, Non-runner, Runner',
    `Entry` tinyint(3) unsigned default NULL,
    `Draw` tinyint(3) unsigned default NULL,
    `Position` tinyint(3) unsigned default NULL,
    `tempDistance` varchar(255) default NULL,
    `AGE` tinyint(2) unsigned default NULL COMMENT 'Age of horse at time of race, could be replaced when horses date of birth is known.',
    `STONE_WEIGHT` tinyint(2) unsigned default NULL COMMENT 'First part of weight, STONE.',
    `POUND_WEIGHT` tinyint(2) unsigned default NULL COMMENT 'Second part of weight, POUNDS.',
    `TEMP_HEADGEAR` varchar(10) default NULL COMMENT 'To be examined at a later stage.',
    `RATING` tinyint(2) unsigned default NULL COMMENT 'Official rating. Max.nr. found so far 148',
    `SP1` smallint(6) default NULL COMMENT 'First part of SP price.',
    `SP2` smallint(6) default NULL COMMENT 'Second part of SP price.',
    `FC1` smallint(6) unsigned default NULL,
    `FC2` smallint(6) unsigned default NULL,
    `REPORT` text COMMENT 'Report on horses performance. longest report found 833 characters',
    `TEMP_BETTING` varchar(20) default NULL COMMENT 'Early prices published by Sportinglife, to be examined at a later stage.',
    `JOCKEY_ID` mediumint(1) unsigned default NULL,
    `JOCKEYALLOWANCE` tinyint(2) unsigned default NULL COMMENT 'Weight in pounds(?)',
    `TRAINER_ID` mediumint(1) unsigned default NULL,
    `TEMP_SP_REMARK` varchar(2) default NULL COMMENT 'Indicated if horse was favourite, to be examined at a later stage.',
    `TEMP_LTOCDWBF` varchar(255) default NULL COMMENT 'Notes for horse, to be examined at a later stage.',
    `TEMP_FORM` varchar(10) default NULL COMMENT 'Result for horse in last races, to be replaced by own query.',
    PRIMARY KEY (`RACE_ID`,`HORSE_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Horses participating in a race.';

  10. #10
    Join Date
    May 2007
    Posts
    80
    Thats not it is it, i'm searching the net but just getting more confused.
    Please point me in the direction of a location where ANSI SQL is clearly explained with examples.

    Or let me rephrase my question.
    Got a MySQL database and i want to create ANSI SQL script files that function as update files. One script file per table seems appropriate. The reason for ANSI SQL is that i want the database to be available to non-MySQL users as well. Obviously providing the used database application can handle ANSI SQL script files. If not i provide basic .CSV file.

    What is the syntax in ANSI SQL to delete then insert records ?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BettingSherlock View Post
    Or let me rephrase my question.
    now you have totally lost me

    what exactly is it that you wish to distribute? the data itself?

    or some kind of code? specifically, what code?

    ANSI SQL for updates??? that's all?

    i am lost..
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2007
    Posts
    80
    The data itself.

    But i want to do it by means of scripts that contain the data using ANSI SQL so the scripts can be run on any database application that supports ANSI SQL.

  13. #13
    Join Date
    May 2007
    Posts
    80
    Forget the concatenation, asking for trouble with that, i'll do it the way you suggested. So i'll get much bigger files, can live with that.

  14. #14
    Join Date
    May 2007
    Posts
    80
    #(#@%(U@Q#U)U(T#)%(& this is embarrassing, i really put my foot in it this time.
    If a record gets deleted on the master database then i can't delete it using a script from the local database because i simply don't know it's in there. And i can just delete everything as then i'll delete records that should remain. How did i miss this

    Thanks for the help Rudy, i'm going back to the drawing board first.
    At least you've made me see the error of my ways

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BettingSherlock View Post
    So i'll get much bigger files, can live with that.
    you are embedding the data into SQL statements? sort of like steganography?

    you're dead from the outset

    the first step would be the CREATE TABLE statement

    you will be unable to come up with one SQL statement that can run on all databases

    well, you might be able to, but you'll be using a very small subset of the broad range of datatypes that various database systems support

    take TINYINT, for instance... in ANSI SQL this would have to be SMALLINT because there is no TINYINT

    TEXT would be very problematic, as the ANSI SQL equivalent is CHARACTER LARGE OBJECT, but not every database system supports that syntax

    and so on...

    distributing a CSV would seem like a far simpler strategy -- users who have databases will know how to load it, and users who don't have databases (e.g. they have excel) can also use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •