|
Thanks.. I got the answer
|
|
|
|
|
Hi,
I have mySQL with first_name, middle_name and last_name fields.
How can I pass a string from my php like param_name so when the user pass single name or multiple name it will split and search for the name in every fields..
For example if the user search for param_name = ""John Mathew Clark" it will do:
first_name or middle_name or last_name LIKE "%John%"
first_name or middle_name or last_name LIKE "%Mathew%"
first_name or middle_name or last_name LIKE "%Clark%"
Thanks,
Jassim
Technology News @ www.JassimRahma.com
modified 14-Jul-17 15:56pm.
|
|
|
|
|
I don't know about MySQL but in SQL Server I have done the following in the past.
Build a function that takes a string and splits it on a delimiter (space) and return a table.
In your query left outer join the function, passing in your name field, on your name field with Name = or LIKE '%' + itemFromFunction + '%'
Make the result set DISTINCT.
Not sure if = or like is needed in the LOJ.
This is an old SQL Server split function I dug up.
<pre>
ALTER FUNCTION [dbo].[fn_Split]
(@List varchar(8000), @Delimiter char(1))
RETURNS @Results table
(Item varchar(8000),ID int Identity(1,1))
AS
begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)
set @IndexStart = 1
set @IndexEnd = 0
set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')
while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)
If @Delimiter = char(32)
set @IndexEnd = charindex(Space(1), @List, @IndexStart)
if @IndexEnd = 0
set @IndexEnd = @Length + 1
set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1
INSERT INTO @Results(Item)
SELECT @Word
end
return
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am having serious difficulties in my attempt to duplicate a production db to a test instance on the same server.
Using these examples has not helped:
Duplicate a database
Duplicate
Duplicating a database
Duplicating a database using RMAN
and there's more. All attempts with all these materials still end in failure.
Additionally, the lack of in-depth examples for Oracle administration available on the internet (indexed by Google/Bing) is concerning.
Okay, here's the closest that I have been able to get
1. Create test database with the Database Configuration Assistant
2. Create minimal SPFile {DB_Name=XXXX}
3. Shutdown test database
4. Start test database with NOMOUNT and minimal SPFile
5. With RMAN, connect to prod db (target)
- connect to test db (auxiliary)
- ran this command
RMAN> duplicate target database to XXXX
2> from active database
3> password file
4> DB_FILE_NAME_CONVERT '/oracle/oradata/prod/','/oracle/oradata/test/';
6. It ran for a minute then died with a lot of these errors
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DCDI\CATALOGTBS.DBF conflicts with a file used by the target database
I have found some info with a 'you need to add this: CONFIGURE AUXNAME ....' but I have no clue where to add these lines and no example was provided.
By this point, the SPFile and Control file are in an invalid state. No clue how to get them back to try again. I've dropped and recreated the test database several times.
I have no clue what I am doing and I cannot find any example on how to do this properly. I've been working on this for about a day and a half and getting nowhere fast.
Can somebody help walk me through this or point me to a good example. I have no experience with Oracle admin and I'm reaching the end of my wits here.
if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
|
|
|
|
|
Can you not just do a backup and restore?
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
This is Oracle.
However much I prefer Oracle to SQL Server this is an area where Oracle sucks.
|
|
|
|
|
Would that copy everything? I need to copy not just tables and data, but procedures, users/passwords, links to other databases, etc...
if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
|
|
|
|
|
I would use Export/Import[^] instead.
The Enterprise Manager has a graphical UI that's quite helpful.
If you export only tablespaces from the database you'll need to setup the users on the new database BEFORE importing.
|
|
|
|
|
While the export/import track is an option, it is necessary to get a complete copy of the database (users, tables, procedures, everything). Using RMAN's replicate feature appears to be the logical choice to accomplish this in one easy to reproduce procedure that can be run whenever it's needed. The trouble is getting it to run correctly the first time. Once I have the correct procedure to replicate this production database to the test database, I can write up documentation. I'm having difficulty in getting it right.
if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
|
|
|
|
|
No, you can choose whether you want to export schemas, tablespaces, tables or the whole database.
Data Pump Export[^]
|
|
|
|
|
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. We are not using any third party tool to take backup.
Thanks,
SREE
|
|
|
|
|
|
Hi ,
I am getting this message on SQL log but i am not initiated any SQL job maintenance.
Please help me what this msg indicates.We are taking Drive backup.
Msg:
Database backed up. Database: abc, creation date(time): 2015/04/17(08:07:47), pages dumped:56589001, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{622759E005}7'}). This is an informational message only. No user action is required.
Thanks,
Sree
Thanks,
Sree.
|
|
|
|
|
|
I have to do an upgrade of MSSQL database like this :
I have folders that each contain an sql script
I must have a sql or batch script that allows me to upgrade my database by running scripts that exist in these folders depending on the version of the current database:
Example:
folders
1 Folder has the name: v2.00
2 Folder has the name: v3.00
3 Folder has the name: v4.00
the initial version of the database: v3.00
the version of the final database: v4.00
So I have to have a script (or batch) that allows me to:
execute just the proper scripts (in this case v3.00 and v4.00)
|
|
|
|
|
How do you determine the "version" of the database?
|
|
|
|
|
there is a table which contains this information
Table "TVersion" and field "FVersion"
|
|
|
|
|
khaliloenit wrote: I must have a sql or batch script In that case you'll need to write one.
You could try to do it completely from SQL, but that requires the xp_cmdshell sproc. Alternatively you write a batch-file for DOS, executing your scripts using the isql command.
Easiest way might be a powershell-script.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Have you an example how to do it ?
Best,
|
|
|
|
|
Which of the three versions?
..but no, not for that specific scenario.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
You can use the sqlcmd utility from a windows batch file e.g.
@ECHO OFF
FOR /F %%i IN ('sqlcmd -S YourDBInstance -Q"set nocount on;select CONVERT(DECIMAL(10,2),FVersion) from TVersion"') DO SET VNO=%%i
call .\v%VNO%\script.bat
@echo done.
Things to note
- you'll have to insert your own information for the -S parameter
- Do not put spaces around the = sign in SET VNO=%%i
- The conversion in the query is necessary to ensure the .00 is returned correctly
- you will need to substitute your script name for script.bat
|
|
|
|
|
I'm looking for a way of joining two tables using a function returning a ranking value where the rows joined would be decided on the returning value of a function.
Assume I'm joining table X and Table Y using a function f(X,Y)
If the values returned by the function from a cartesian join would be:
I would want this "excluding functional join" to return:
Y1,X3
Y2,X1
Y3,X2
Is this at all possible without using procedural code?
<edit>What I want is something similar to the Gale-Shapley[^] algorithm but using a special text search instead of simple preferences.
It's easy enough to do in procedural code, but I want to avoid an RBAR solution</edit>
modified 17-Jun-16 2:34am.
|
|
|
|
|
Jörgen Andersson wrote: without using procedural code I do doubt it.
One way might be to create a separate view that generates the ranking (can't see the PK field) and use the view in the join.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: can't see the PK field That's because they're of no use in this case.
I'm trying to map two tables in two different databases on their description fields, and some descriptions are basically the same but with added information for some rows, and those are the big problem.
I'm going to try to do it using a recursive CTE.
|
|
|
|
|
Jörgen Andersson wrote: m going to try to do it using a recursive CTE. why am I not surprised by that. Your cte skills seem to be quite adequate. Let us know if you achieve a solution
Never underestimate the power of human stupidity
RAH
|
|
|
|