Re: HOW TO RUN SQL
hi, to run a sql script from mysql shell just use command "source your_sql_script.sql" if you want to run it directly from prompt use this mysql --force -uusername -ppassword -e"source your_sql_script.sql" - Original Message - From: "Seena Blace" <[EMAIL PROTECTED]> To: "Remko Lodder" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, February 09, 2004 11:10 PM Subject: HOW TO RUN SQL > how to run sql script like > if i have mysql prompt > mysql > > at above prompt I want to run sql script. how to do this? what will be extenstion of file? > > Remko Lodder <[EMAIL PROTECTED]> wrote: > mysql -p > -u -e > > that can be runned from any shell script. > > use enum to check Y/N TRUE/FALSE etc. > > CREATE TABLE testtest ( > test ENUM( 'TRUE', 'FALSE' ) NOT NULL > ); > > > it automaticly sets true as default value with me. > otherwise add: > > DEFAULT 'FALSE' > > after not null > > Hope this helps > -- > > Kind regards, > > Remko Lodder > Elvandar.org/DSINet.org > www.mostly-harmless.nl Dutch community for helping newcomers on the > hackerscene > mrtg.grunn.org Dutch mirror of MRTG > > -Oorspronkelijk bericht- > Van: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Seena Blace > Verzonden: maandag 9 februari 2004 22:14 > Aan: [EMAIL PROTECTED] > Onderwerp: [Mysql-discussions] HOW TO RUN SQL and default value ? > > > Hi, > How to run script at mysql prompt? > Like if i want to create a table or select some query and I want to put > those sql statement into one file ,how to run that and what would be the > extention of that query? > I want to create on column which contains only default value TRUE or > False.how to do that? > Create table test1 (no int, flag char (6) default ... > thx -seena > > > > - > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > - > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'ODBC@localhost' (Using password: YES)
I think MyODBC in this situation is irelevant. ODBC reinterpret the SQL language (and is good for some universal data components like ADO,DAO,...), but MySQL C API is native. Also you can't connect by MyODBC using MYSQL C API. So you really didn't forget some parameter??, it seems like. Otherwise try to create user named ODBC, and you will see, also it writes "Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)*", so you are using some password, and question is from where this goes, and which password you have to give to user ODBC. I still thing that you must omit some parameter when establishing connection. (But I never tried to make connection from COBOL, I'm just using MYSQL C API from C++) - Original Message - From: "Arunachalam" <[EMAIL PROTECTED]> To: "KKoTY" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 06, 2004 6:21 AM Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) > I have installed MyODBC 3.51 and created the DSN for my Database. > > I want to clarify that; > If the User name I have given is could not be resolved by mysal_real_connect, > then it automatically establish the connection to the server using > [EMAIL PROTECTED] - right > > If so, Is there need to have the user named ODBC in mysql database. > > OR > > It'll connect automatically using the DSN setting i have specified in myODBC. > > Becaz I don't have the source to check Is COBOL value passed to C > as such what I have specified? > > Please clarify my doubts... > > regards, > Arun. > > > --- KKoTY <[EMAIL PROTECTED]> wrote: > this occures when you ommit the user name, MYSQL C API uses > user ODBC as > > default when you ommit or enter empty string as > > user name when calling mysql_real_connect() > > > > - Original Message - > > From: "Arunachalam" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, February 05, 2004 3:38 PM > > Subject: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) > > > > > > > Hi all, > > > > > > Is any one met with the error while connecting to MySQL Server > > > > > > *Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)* > > > > > > if so pleass let me know the remedy to resolve this error. > > > > > > > > > I don't have the user named ODBC in my mysql Database. > > > > > > my configuration is: > > > Windows 2000 SP 4. > > > MySQL Server 4.0.17-max-debug for windows > > > > > > The situation I have met this error is when I try to Connect MySQL > > > from COBOL using the C API provided by MySQL. I have properly link > > > the *libmysql.lib* file into my COBOL compiler. > > > > > > Any suggestion are highly appreciated... > > > > > > Thanks. > > > > > > regards, > > > Arun. > > > > > > > > > Yahoo! India Mobile: Download the latest polyphonic ringtones. > > > Go to http://in.mobile.yahoo.com > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > Yahoo! India Mobile: Download the latest polyphonic ringtones. > Go to http://in.mobile.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL
- Original Message -
From: "/dev/null" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 05, 2004 11:27 PM
Subject: New to MySQL
> wow, nice product. Pretty easy to use and install - I like it.
>
> I know enough about SQL and databases to just about break any flavor of
them
> I put my hand on ;-). A couple of pointers would be great.
>
> I'm creating a handfull of tables that will inter-depend on eachother,
using
> it within php. I have three basic questions:
>
> 1. Some of the tables have a unique ID, each new record should get the
next
> number available. Is there an easy data type that will do this for me, or
> do I need to get a record count myself and use record count + 1 when I
> insert a new row?
>
CREATE TABLE `pricelist` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(255) NOT NULL default '',
`PRICE` float NOT NULL default '0',
PRIMARY KEY (`ID`)
);
The column ID will be automaticly incemented. You just write INSERT INTO
pricelist (`NAME`,`PRICE` ) VALUES ('coffee',200);
To get the ID of inserted record you just call mysql_insert_id() (in MYSQL
API, in another just find similar function;) or call
SELECT LAST_INSERT_ID();
> 2. Some of the tables will need a timestamp of the exact date/time the
> record was added. Does timestamp do this, or again do I need to get
time( )
> and use that value on the insert myself?
You can use TIMESTAMP data type. If you update the row with NULL for the
TIMESTAMP field it will enter
the current date and time. If you omit this column in INSERT statement, it
will alos enter the current date and time.
>
> 3. Some of the IDs from one table are used in another table, for example
I
> have an accounts table and a sites table. Each account can have several
> sites, and each site entry has an "account" field that has the account ID
> (from question 1) to tell who the account owner is. Isn't there a joint
> query that I can run that is like 'SELECT * FROM `sites` WHERE `AccountID`
=
> ( SELECT `ID` FROM `accounts` WHERE `Name` = 'First Last' )'??? Or do I
> need to run the inner select and get the ID myself, then run the outer
> select? I admit, I know just enough SQL to wipe the database and drop all
> the tables ;-), so any help would be greatly apreciate.
>
MySQL before versions 4.1 doesn't support subqueries (you sample works on
4.1 or greater).
But you can write standart SQL joins.
Some samples:
SELECT sites.* FROM `sites`,`accounts` WHERE `sites`.`AccountID`
=`accounts`.`ID` AND `accounts`.`Name` = 'First Last';
also this is possible:
SELECT sites.* FROM `sites` INNER JOIN `accounts` ON (`sites`.`AccountID`
=`accounts`.`ID`) WHERE `accounts`.`Name` = 'First Last';
when you need something like NOT IN (SELECT FROM ... WHERE) you can
write this:
mysql> SELECT table1.* FROM table1
->LEFT JOIN table2 ON table1.id=table2.id
->WHERE table2.id IS NULL;
This example finds all rows in `table1' with an `id' value that is
not present in `table2' (that is, all rows in `table1' with no
corresponding row in `table2'). This assumes that `table2.id' is
declared `NOT NULL', of course. *Note LEFT JOIN optimisation::.
If there is no matching record for the right table in the `ON' or
`USING' part in a `LEFT JOIN', a row with all columns set to
`NULL' is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:
> Thanks!
>
> /dev/null
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'ODBC@localhost' (Using password: YES)
this occures when you ommit the user name, MYSQL C API uses user ODBC as default when you ommit or enter empty string as user name when calling mysql_real_connect() - Original Message - From: "Arunachalam" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 05, 2004 3:38 PM Subject: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) > Hi all, > > Is any one met with the error while connecting to MySQL Server > > *Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)* > > if so pleass let me know the remedy to resolve this error. > > > I don't have the user named ODBC in my mysql Database. > > my configuration is: > Windows 2000 SP 4. > MySQL Server 4.0.17-max-debug for windows > > The situation I have met this error is when I try to Connect MySQL > from COBOL using the C API provided by MySQL. I have properly link > the *libmysql.lib* file into my COBOL compiler. > > Any suggestion are highly appreciated... > > Thanks. > > regards, > Arun. > > > Yahoo! India Mobile: Download the latest polyphonic ringtones. > Go to http://in.mobile.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] setting DATE field with default NOW value?
you can use TIMESTAMP data type, which have default NOW() - Original Message - From: "Riaan Oberholzer" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 05, 2004 12:38 PM Subject: [Q] setting DATE field with default NOW value? > In a table definition, how do I get a date field to be > assigned as DEFAULT the current date? > > I tried: > > dateField DATE DEFAULT CURDATE() > > but that gives me syntax errors > > Help? > > > __ > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online. > http://taxes.yahoo.com/filing.html > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wysiwyg web soft
i'm using this one : http://vsbabu.org/webdev/zopedev/ieeditor.html but it works just on IE5.5 SP1 and above (IE6;) - Original Message - From: "fab" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 04, 2004 5:06 PM Subject: wysiwyg web soft > Hi to all, > > I'm fed up with Access as a wysiwyg editor for mysql. Generaly, i'm fed > up with client that i have to install on each comp. Actualy, i'm looking > for an opensource wysiwyg web software. Something like the very good > phpMyAdmin but with 'visual' request editor for simple users. > > If anyone knows such thing. Thanx. > > fab. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql command line syntax
hi, you can try something like this mysql --force -uuser -ppass -Ddatabase -e"query.sql" where you can put all your SQL commands into to file named "query.sql" - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, February 02, 2004 8:38 PM Subject: mysql command line syntax > > Why does the following command, entered as a single line at a bash prompt, prompt me to enter more? > > mysql -u user -ppass -D database -e 'select field into outfile \'/tmp/field.1\' from mytable where id = 1;' > > > > And is there any way to supress the 300 lines of useless usage that accumpanies every failed attempt to execute a command? > > > Thanks njs. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: yearweek() result back to date format?
hi, i was solving same problem before month and i finished it by this way:
select dayname("2003-11-12"), yearweek("2003-11-12"),ADDDATE('2003-01-06',
INTERVAL 7*(WEEK('2003-11-12')-1) DAY),ADDDATE('2003-01-06', INTERVAL
7*(WEEK('2003-11-12')-1)+6 DAY);
but there is constant '2003-01-06', that's date of first Monday (Monday
because i'm Czech) of first week in a year.
There is a lot of ways to number weeks, see WEEK() function .
I think it's impossible to get this date (the date of first day of first
week) just in query, maybe you can write your own function ;))
But if you are using something like PHP etc., you can simply go throw 6 days
before and after your date, and simply check while the week number is same
like weeknumber you got from mysql.
I hope that it helps you little.
`WEEK(date [,mode])'
The function returns the week number for `date'. The two-argument
form of `WEEK()' allows you to specify whether the week starts on
Sunday or Monday and whether the return value should be in the
range 0-53 or 1-52. When `mode' argument is omitted the value of a
`default_week_format' server variable (or 0 in MySQL 4.0 or
earlier) is assumed. *Note SET OPTION::.
The following table demonstrates how the `mode' argument works:
*Value* *Meaning*
0 Week starts on Sunday; return value range is 0 to 53;
week 1 is the first week that starts in this year
1 Week starts on Monday; return value range is 0 to 53;
week 1 is the first week that has more than 3 days in
this year
2 Week starts on Sunday; return value range is 1 to 53;
week 1 is the first week that starts in this year
3 Week starts on Monday; return value range is 1 to 53;
week 1 is the first week that has more than 3 days in
this year
4 Week starts on Sunday; return value range is 0 to 53;
week 1 is the first week that has more than 3 days in
this year
5 Week starts on Monday; return value range is 0 to 53;
week 1 is the first week that starts in this year
6 Week starts on Sunday; return value range is 1 to 53;
week 1 is the first week that has more than 3 days in
this year
7 Week starts on Monday; return value range is 1 to 53;
week 1 is the first week that starts in this year
The `mode' value of 3 can be used as of MySQL 4.0.5. The `mode'
value of 4 and above can be used as of MySQL 4.0.17.
- Original Message -
From: "Trevor Smith" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 26, 2004 12:56 PM
Subject: yearweek() result back to date format?
> How can I use the output string of the yearweek() function
> (for example, "200346" below):
>
> mysql> select dayname("2003-11-12"), yearweek("2003-11-12");
> +---++
> | dayname("2003-11-12") | yearweek("2003-11-12") |
> +---++
> | Wednesday | 200346 |
> +---++
> 1 row in set (0.00 sec)
>
> to extract the start and end dates for that week (for example,
"2003-11-09"
> and "2003-11-15" above)?
>
> I'm using server v3.23.
>
> --
> Trevor Smith|[EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A fun one
try SELECT domain,AVG(speed) FROM your_table GROUP BY domain where domain and speed are columns of table your_table - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 24, 2004 10:59 AM Subject: A fun one > Here's a fun one to tickle your brains: > > I've got a table holding the results of a bandwidth tester thingie. Its > columns hold the date and time the test was run, the fully-qualified machine > name of the computer that took it, its IP address, and the speed in > kilobits/sec. What I'd like to do is compute the average for each domain: > for example, there are many machine names in the adelphia.com domain, and > I'd like to get an average of all adelphia.com results...and all other > domains found in the database, which may increase daily. > > I'm pretty new to SQL, so this is kinda befuddling to me. Oh, the page is > in PHP if it needs a little extracurricular touch. > > thanks a million! > Dan > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory leaks using MySQL C Api
try this, i found this when solving similar problem.There is another method
to make and free connection, here is just one change.
The trick is that you dont call mysql_close( ) on &mysql_connection , but
on return of mysql_real_connect();
Try it, i think that this solve your is problem.
static MYSQL myMYSQL ;
static MYSQL *myData ;
mysql_init(&myMYSQL);
if ( myData=mysql_real_connect( &myMYSQL, "localhost", "puberusr",
"puber1234", "pubernew", MYSQL_PORT,
NULL, 0 ) )
{
if ( mysql_select_db( myData, "pubernew" ) < 0 ) {
DisplayException(mysql_error(&myMYSQL));
mysql_close( myData ) ;
}
}
else {
DisplayException(mysql_error(&myMYSQL));
mysql_close( myData ) ;
}
- Original Message -
From: "John McCaskey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, January 18, 2004 8:31 AM
Subject: Re: Memory leaks using MySQL C Api
> Agreed, I am not calling mysql_store_result(). I attempted to add
> my_free() but the function does not seem to exist, it is also not listed
> in the API docs for the c api. As such it still seems that there should
> be no leak, but yet I do get one. Thanks for the idea anyway Chris,
> maybe you can clarify for us once more.
>
> John McCaskey
>
> On Sat, 2004-01-17 at 12:44, Aftab Jahan Subedar wrote:
> > Hey wait a minute. Where did you get the my_free(), may be you are
> > trying to say mysql_free(), but then that is used only if result set is
> > used/called.
> >
> > But the code does not show any result set call. ie. mysql_use_result()
> > or mysql_store_result().
> >
> > So, the question now, how come there is a leak here. I dont see any,
> > does anyone see any?
> >
> > Chris Nolan wrote:
> >
> > > Hi!
> > >
> > > You're looking for the function my_free(). Enjoy!
> > >
> > > Regards,
> > >
> > > Chris
> > >
> > > John McCaskey wrote:
> > >
> > >> I have the following code:
> > >>
> > >>
> > >>
> > >>//try the mysql connection
> > >>
> > >>mysql_init(&mysql_connection);
> > >>
> > >>if(!mysql_real_connect(&mysql_connection, db_host, db_user,
> > >> db_pass,
> > >> db_db, 0, NULL, 0)) {
> > >>
> > >>flockfile(stderr);
> > >>
> > >>fprintf(stderr, "%s: Failed to connect to database:
Error:
> > >> %s\n", timestamp, mysql_error(&mysql_connection));
> > >>
> > >>funlockfile(stderr);
> > >>
> > >>mysql_close(&mysql_connection);
> > >>
> > >>return(2);
> > >>
> > >>}
> > >>
> > >>
> > >>
> > >>mysql_close(&mysql_connection);
> > >>
> > >>
> > >>
> > >> This code is creating a memory leak. Am I missing some cleanup
calls?
> > >> I'm
> > >> under the impression all I should need to do is call mysql_close to
> > >> clean up
> > >> the connection? I'm testing this using mtrace, if I place a return
> > >> directly
> > >> above the code segment it reports no leaks, if I place it direcly
> > >> below the
> > >> fragment there are several variables reported as not being freed.
Any
> > >> ideas?
> > >>
> > >>
> > >>
> > >> John A. McCaskey
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> > --
> >
> > Aftab Jahan Subedar
> > CEO/Software Engineer
> > Subedar Technologies
> > Subedar Baag
> > Bibir Bagicha #1
> > North Jatrabari
> > Dhaka 1204
> > Bangladesh
> > http://www.SubedarTechnologies.com
> > http://www.DhakaStockExchangeGame.com/
> > http://www.CEOBangladesh.com/
> > http://www.NYSEGame.com
> > tel://+88027519050
> > EMail://[EMAIL PROTECTED] - Directly to my notebook
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysql_close() API
hi, i had same problem. But after I put mysql_free_result(MYSQL_RES *pResult) after fetching rows from query (or generaly after calling all queries), it was OK without this message. I think there is a problem when you allocate memory for result of a query (with mysql_store_result(MYSQL *myData)), and you dont call mysql_free_result() to free this allocated memory, than memory leaks are detected, and Access Violation Error proceed. - Original Message - From: "Leo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 7:24 AM Subject: Problem with mysql_close() API Hi All, i have a problem everytime i call the function mysql_close(), it result in Access Violation Error in module libmysql.dll fyi, im migrating from 3.23.52 to 4.0.x server runs under linux, im accesing the server through windows based application built with delphi, using Object Pascal translation from C API (by Jorge del Conde) with the library i took from mysql directory the flow is similiar like this: mysql_init(@myconnection); mysql_real_connect(@myconnection,...); ... some proccess ... mysql_close(@myconnection); <-- this is where the error raised i slip in mysql_init before mysql_close, and the problem disappear.. mysql_init(@myconnection); mysql_real_connect(@myconnection,...); ... some proccess ... mysql_init(@myconnection); mysql_close(@myconnection); <-- this is where the error raised is this normal?? is this how it should be? i've red the manual, and there is none mentioning that i have to do init before cloaing one. thanks for any comment -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
