|
Is it possible to append a selected column onto the end of a selected row? For instance, I have 2 selects. The first only returns 1 row. The second returns many rows, but only in 1 column. Can I easily append all of the rows from the 2nd select onto the end of the row in the first select, without using a cursor? Even better, we'd like to concatenate all of the rows from the 2nd select into one long string, and just append that string onto the end of the 1 selects row. What might be the best approach to this?
Thanks!
|
|
|
|
|
Assuming you are using SQL Server
Here is a little sample of creating a comma delimited list from a set of rows. You can run it in SQL Query Analyzer by replacing [Name]/[Weather] with field/table of your choice. Careful that you don't pick a query that goes beyond the 8000 character limit.
This doesn't get rid of the cursor when assembling the final table since you will have to reset @sample to null at each row iteration. It does get rid of the nasty 'rows to 1 column' issue though.
DECLARE @sample VARCHAR(8000)
SELECT @sample = ISNULL(@sample + ', ','') + [Name]
FROM [Weather]
PRINT @sample
|
|
|
|
|
I have Table in ACCEss. i want it to be exported or moved to oracle. I had tried by making ODBC connection string and exporting the table.
It shows table in tablespace (i.e Whem we fire
Select * from tab; )
But when we fire desc or any select query on table
it shows object does not exists/....
In short i want to export mine table from access to oracle
I m having many table's that r to be moved to oracle with data. Suggest Possible way: confused:
|
|
|
|
|
hi people,
I have a question concerning the text/ntext data types. I have read that the maximum number of characters that we can place in an SQL row is 8060. It sounds quite strange since the text data type can hold more than 2 million. Now when I declare a field as "text", I notice that the size is 16, which is probably 16 bytes. This probably means that in fact, the field, is just like the interger field. Is it correct to say so? Is the "text" field in fact a pointer to a location where the 2 million-character (or more) data is located?
I have tried to insert manually (using the SQL Enterprise Manager) a 3000-character text into a field of "text" data type, but it doesn't permit so.
My question is, how can I insert text in a "text" data type field?
thanks,
Talal
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
The largest you can set a char or varchar column to is 8000 bytes. An nchar or nvarchar column can be up to 4000 characters. The size of the whole row, including overhead, must be less than 8060 bytes.
The TEXT data type can actually hold up to 2GB of data. NTEXT is limited to 2^30 - 1 characters (again, 2GB of data). In the typical case, the row holds a 16-byte pointer to the location of the actual data. An additional index is created on the table to indicate where the text/image data is located.
To insert text data manually, use the SQL WRITETEXT or UPDATETEXT commands in Query Analyzer.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for the reply, I checked those functions and the help that comes with the Query Analyzer. I managed to insert data but when I tried to retrieve the field, still using the QA, I only got a portion of it, eventhough I had the size to 0 in the READTEXT function, which should normally return a chunk of 4KB (as the help says).
I'm thinking this might be just because of QA, because when I put a size that is bigger than the real size of the text, I get an error that says that the size is greater than the actual size, and it gives me the correct number of characters, which in my case is 2800.
So I suppose that the result given by the QA is just a fixed number of characters, regardless of the real size of the text?
To use this programmatically, how would I proceed? Would I use a function or a stored procedure? or is there a special way?
thanks a lot for your help.
Talal
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
Did anybody knows how to move Olap cubes from one computer to another. So far I have to do it manually on the other computer. Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi there. Are you talking about olap cubes in Microsoft Analysis Services? or a different framework?
If this is in Analysis Services, you can use the Analysis Services manager to create an archive file of the cube database, then copy it over to the other machine and restore it there.
|
|
|
|
|
I`m using Microsoft OLAP Service. Should I use Microsoft Analysis Services? Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi there. Perhaps I'm ignorant about this - I thought they were one and the same.
|
|
|
|
|
Maybe ... maybe not. "OLAP Services" could mean the general technology OR the OLAP engine in MS SQL 7. Microsoft renamed it "Analysis Services" with SQL2000.
|
|
|
|
|
I`m using the OLAP engine in MS SQL 7. Isn`t it different with the Analysis Service in SQL2000?
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I am a VB.Net developer and I am trying to use VB.Net, a web service and a MySQL table on a Linux server to save and retrieve .wav files.
I have been able to insert and retrieve .wav files to a MS SQL table by using the image datatype in the MSSQL table. I have converted a .wav file to both a byte array and a base64 character type and saved it (inserted) to a MSSQL table with no trouble. And I have used the select statment to retrieve that same .wav file and my code works.
However, I really need to create a webservice to store .wav files into a MySql table on a Linux server. We currently have numerous webservices that work well with MySql but inserting a .wav file has proven to be a real challenge. I am not trying to use a webservice during the testing, I make a direct connection to the MySql db and use ODBC SQL statements for my Inserts and Selects.
I have used numerous reader and writer routines that I have found on the Internet but none of the have worked.
There is not much on the internet about how to save .wav files in a database, but I am making an assumption I could use some of the same code that is used to save and retrieve image files. This assumption has worked when I am using MSSQL tables but it doesn't seem to work with MySql tables.
Does anyone have any idea as to how to do this?
|
|
|
|
|
I figured it out myself and I thought I would put the answer onto this forum in case anyone else might need to do the same thing.
This code did the trick but I had to make a couple of changes because I was using an ODBC driver for MySql and not a native driver and I would get an error when I tried to use the FILESIZE argument.
Here is what I finally came up with and it works great.
Private Sub SaveWavFile(ByVal WavFileAndPath As String, ByVal StudyRef As String, ByVal UserName As String, ByVal PW As String)
Dim cmd As New OdbcCommand
Dim SQL As String
Dim rawData() As Byte
Dim fs As FileStream
Dim connectionString As String = " _
& "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=XXX.XXX.XX.XXX " _
& ";DATABASE=MyDatabase" _
& ";UID=UserName" _
& ";PASSWORD=PW" _
& ";OPTION=3;"
Dim conn As New OdbcConnection(connectionString)
Try
fs = New FileStream(WavFileAndPath, FileMode.Open, FileAccess.Read)
rawData = New Byte(fs.Length) {}
fs.Read(rawData, 0, fs.Length)
fs.Close()
conn.Open()
SQL = "INSERT INTO dictations VALUES(?, ?)"
cmd.Connection = conn
cmd.CommandText = SQL
cmd.Parameters.Add("@Study_ref", StudyRef)
cmd.Parameters.Add("@bin_data", rawData)
Try
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
End Try
MessageBox.Show("File Inserted into database successfully!", _
"Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
conn.Close()
Catch ex As Exception
MessageBox.Show("There was an error: " & ex.Message, "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
And if you want to retrieve the file from the MySQL table you can use this code:
Sub RetrieveWavFilel(ByVal imavar1 As String)
'mysql> describe dictations;
'+-----------+-------------+------+-----+---------+-------+
'| Field | Type | Null | Key | Default | Extra |
'+-----------+-------------+------+-----+---------+-------+
'| study_ref | varchar(25) | YES | | NULL | |
'| bin_data | longblob | YES | | NULL | |
'+-----------+-------------+------+-----+---------+-------+
Dim fs As FileStream ' Writes the BLOB to a file (*.bmp).
Dim bw As BinaryWriter ' Streams the binary data to the FileStream object.
Dim bufferSize As Integer = 100 ' The size of the BLOB buffer.
Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by
'GetBytes.
Dim retval As Long ' The bytes returned from GetBytes.
Dim startIndex As Long = 0 ' The starting position in the BLOB output.
Dim strTmp As String = ""
Dim bWavFile() As Byte
'Dim connectionString As String = "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;" '
Dim connectionString As String = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=XXX.XXX.XXX.XXX;DATABASE=MyDatabase;UID=User_name;PASSWORD=PW;OPTION=3;"
Dim conn As New OdbcConnection(connectionString)
conn = New OdbcConnection(connectionString)
conn.Open()
Dim cmdOrder As New OdbcCommand("SELECT study_ref, bin_data From dictations Where (study_ref = ?) ", conn)
cmdOrder.Parameters.Add("@Study_ref", OdbcType.VarChar, 25).Value = imavar1
Dim fdRead As Odbc.OdbcDataReader = cmdOrder.ExecuteReader(CommandBehavior.CloseConnection)
Do While fdRead.Read()
strTmp = fdRead.GetString(0)
' Create a file to hold the output.
Dim strWavFileOut As String = Me.TextBox2.Text
fs = New FileStream(strWavFileOut, FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)
' Reset the starting byte for a new BLOB.
startIndex = 0
' Read bytes into outbyte() and retain the number of bytes returned.
retval = fdRead.GetBytes(1, startIndex, outbyte, 0, bufferSize)
' Continue reading and writing while there are bytes beyond the size of the
' buffer.
Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()
' Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize
retval = fdRead.GetBytes(1, startIndex, outbyte, 0, bufferSize)
Loop
' Write the remaining buffer.
Try
bw.Write(outbyte, 0, retval - 1)
Catch
End Try
bw.Flush()
' Close the output file.
bw.Close()
fs.Close()
Loop
fdRead.Close()
conn.Close()
End Sub
If anyone finds this of any help please let me know.
|
|
|
|
|
 Hello gents, I have this interesting question. I have one field that contains a record such as this "SD070220N0300E00004" that I need to turn in to several sub fields. The first being ST and being the first 2 charecters in the field, then another that contains the second two char and so on.
I have agregated several fields to one, but never one into many!
Your expertise would be greatly appreciated and to the most helpful goes good Karma!
David
You are only as good as your last project.
|
|
|
|
|
Well, since the gents seem to be busy, do you mind if I answer?
How you actually do what you want depends on the language you are using. If it is VB.NET you could use the Substring method of the String class. If its a different language, you need to look up info on working with strings.
|
|
|
|
|
Hi there,
I'm not sure why following code does not work for one class function but does for another in my application. It doesn't allow me to insert into a table that contains more than one field. Below, the field and value arrays are char array pointers (char *fields[200], values[200]) that contain the field names and their corresponding values. num_fields is the number of fields in the table.
HRESULT hr;
m_Rec.CreateInstance( __uuidof(ADODB::Recordset));
hr = m_Rec->("SELECT * FROM Employees", _variant_t(IDispatch *)m_Conn, true), ADODB::adOpenForwardOnly, ADODB::adLockOptimistic, ADODB::adCmdText);
if (SUCCEEDED(hr))
{
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, num_fields);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, num_fields);
long lArrayIndex[1];
for (i = 0; i < num_fields; i++)
{
lArrayIndex[0] = i;
vaFieldList.PutElement(lArrayIndex, &(_variant_t(fields[i])));
vaValueArray.PutElement(lArrayIndex, &(_variant_t(values[i])));
}
m_Rec->AddNew(vaFieldList, vaValueArray);
m_Rec->Close();
}
//clear the array for the next record
for (i = 0; i < num_fields; i++)
{
*(fields + i) = 0;
*(values + i) = 0;
}
Thanks!
|
|
|
|
|
Nevermind. I figured it out. It was actually my database data types that were mismatching. Stupid me.
|
|
|
|
|
Is it possible to compare two datarows taken from two separate datasets as a whole, or do you have to compare the value of each column in the row.
Please advise
|
|
|
|
|
u can do ,if the table schemes r the same.
|
|
|
|
|
Just Greeky Creek wrote:
u can do ,if the table schemes r the same.
How is this done?
|
|
|
|
|
Dim greeky As Boolean = True<br />
Dim xDs1 As New DataSet<br />
Dim xDs2 As New DataSet<br />
For i As Short = 0 To xDs2.Tables(1).Rows.Count - 1<br />
greeky = greeky And xDs1.Tables(0).Rows.Contains(xDs2.Tables(1).Rows(i))<br />
Next<br />
If greeky Then<br />
MessageBox.Show("xDs1.Tables(0) contains all rows of xDs2.Tables(1)")<br />
End If
|
|
|
|
|
As I read in all my records from a Recordset, I want to delete them.
Will this code work:
<br />
try<br />
{<br />
CString mySelect;<br />
mySelect.Format("SELECT MDC1_DATA_TEXT FROM MDC1 WHERE (MDC1_I_O = 'O')");<br />
pCommand.CreateInstance(__uuidof(Command));<br />
pCommand->ActiveConnection = m_pConnection;<br />
pCommand->CommandText = _bstr_t(mySelect);<br />
pRecordset.CreateInstance(__uuidof(Recordset));<br />
pRecordset->CursorLocation = adUseClient;<br />
pRecordset->Open((IDispatch*) pCommand, <br />
vtMissing,<br />
adOpenStatic,<br />
adLockBatchOptimistic,<br />
adCmdUnknown);<br />
_variant_t MDC1_DATA_TEXT;<br />
_variant_t MDC1_REV_DATE;<br />
int recCount = pRecordset->GetRecordCount();<br />
while (!pRecordset->adoEOF)<br />
{<br />
MDC1_DATA_TEXT = pRecordset->GetCollect("MDC1_DATA_TEXT");<br />
int strLength = strlen((char*)_bstr_t(MDC1_DATA_TEXT));<br />
if (MDC1_DATA_TEXT.vt != VT_NULL)<br />
{<br />
m_sConnectSocket.Send((char*)_bstr_t(MDC1_DATA_TEXT), strLength);<br />
pRecordset->Delete(adAffectCurrent);<br />
pRecordset->UpdateBatch(adAffectCurrent);<br />
<br />
<br />
}<br />
pRecordset->MoveNext();<br />
}<br />
}<br />
catch (_com_error &e)<br />
{<br />
_bstr_t bstrError(e.ErrorMessage());<br />
CString strError = (char*)bstrError;<br />
AfxMessageBox(strError);<br />
}<br />
catch (...)<br />
{<br />
AfxMessageBox("Unknown Error!");<br />
}<br />
<br />
<br />
So after I read in each records that has a 'O', I want to delete it with these commands:
pRecordset->Delete(adAffectCurrent);
pRecordset->UpdateBatch(adAffectCurrent);
Then read the next record that has an 'O', then delete it....so on and so forth.
Will the Delete and UpdateBatch work for me?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
If this is really all you want to do, I suggest using a DELETE statement:
DELETE FROM MDC1
WHERE MDC_I_O = 'O' If you want more conditions you can easily add them to the WHERE clause.
If you can't do this - you need to cross-reference with another data source, perhaps - you should be able to call UpdateBatch after performing all the Deletes.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
my problem is that I may have more than one record with an 'O'. The database is based on a date time stamp in ascending order. This is why I wanted to delete each record as it's read in to my record set.
Let me ask this, as I run thru my code, I'm getting one record at a time, right? Or does the recordset pointer get all records at one time, that match my select statement?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
