Re: Saving a recordset in an XML stream - Database Discussion Boards

alex.barylski7-Nov-02 12:58

7-Nov-02 12:58

I've actually used the in statement before, but only like in your second example...

Anyways, I appreciate the help Paul...I was getting pretty desperate...

One question? I just read a little more on the subject and it appears I could accomplish something very similar using a left join...???

I will have to read up on this appraoch more, but which would be more efficient...?

Thanx again

Cheers! Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: Trivial SQL (i think)

alex.barylski7-Nov-02 13:10

alex.barylski

7-Nov-02 13:10

Hey Paul, i'm getting the following error, you wouldn't care to help me figure out why...? Smile | :)

<br />
SQL-query :  <br />
<br />
SELECT * FROM agency WHERE NOT agencyid IN (SELECT agencyid FROM clients) LIMIT 0, 30<br />
<br />
MySQL said: <br />
<br />
<br />
You have an error in your SQL syntax near 'SELECT agencyid FROM clients) LIMIT 0, 30' at line 1<br />

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: Trivial SQL (i think)

Paul Watson7-Nov-02 21:08

Paul Watson

7-Nov-02 21:08

Hockey wrote:
MySQL

Doh, I just assumed you meant Microsoft SQL Server. I have about 2 hours experience with MySQL and most of it was spent cursing Big Grin | :-D

There are some differences between the TSQL that MS SQL Server uses and the SQL that MySQL uses.

*the following is pure guessing so I could be utterly wrong*

If I had to guess, the error seems to be saying that a in statement has a maximum of 30 returnable rows. There might be a setting in MySQL for this.

If none of that works then in answer to your other question about rather using a join..

SELECT
    Clients.ClientID, 
    Clients.AgencyID AS ClientAgencyID, 
    Agency.AgencyID, 
    Agency.Name AS AgencyName
FROM
    Clients FULL OUTER JOIN
    Agency ON Clients.AgencyID = Agency.AgencyID
WHERE    (Clients.ClientID IS NULL)

Sorry but I can't test it against a MySQL box so I do not know if it works on MySQL, works on MSSQL though.

As for performance I am no DBA so I really could not tell you which is faster.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

Re: Trivial SQL (i think)

markkuk8-Nov-02 2:08

markkuk

8-Nov-02 2:08

MySQL doesn't support subSELECTs at all. You must replace the nested query, see the MySQL manual.

Re: Trivial SQL (i think)

alex.barylski8-Nov-02 8:11

alex.barylski

8-Nov-02 8:11

Brutal...

I ended up using multiple queries inside a for loop...proabbly not the MOST elegant solution, but it works...

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

SELECT ... FOR XML AUTO

Alexandru Savescu6-Nov-02 23:42

Alexandru Savescu

6-Nov-02 23:42

Hello,

I want to use the SELECT some recordsets in an XML format (using the SQL Server 2000 features). I am using Carlos Antollini classes that wrap the ADO database clases. I am declaring a CADORecordset object, open it and use GetFieldValue (0, var), where var is a _variant_t object.

Its type is VT_ARRAY | VT_UI1 (this means an array of bytes, rights?). The parray member looks like this:

cDims = 0x0001; // one dimension array
fFeatures = 0x0080;
cbElements = 0x00000001;
cLocks = 0x0;
pvData = 0xsmth;

Dumping my array of bytes into a file looks awful. However, looking at the code the array looks like an array of BSTRs, but is not the XML encoded data I am expecting.

Can anyone help?
Thanks.

Best regards,
Alexandru Savescu

Best regards,
Alexandru Savescu

Saving a recordset in an XML stream

Alexandru Savescu5-Nov-02 23:33

Alexandru Savescu

5-Nov-02 23:33

I am saving an ADO recordset as an XML. It works fine. However I do not want to save into a file, I would like to save it into a stream, a CString, ostream etc. Anyone have an idea?

Thanks!

Best regards,
Alexandru Savescu

Re: Saving a recordset in an XML stream

Richard Deeming6-Nov-02 2:54

Richard Deeming

6-Nov-02 2:54

You can pass a Stream object to the Save method of the recordset. For example, in IIS 5 you can pass in the Response object, and the XML will be sent straight to the browser.

Datasets

Scoop5-Nov-02 23:15

Scoop

5-Nov-02 23:15

I am trying to retrieve data using datasets in C#. However when i use the select command "SELECT * from Some_table". if there is a field which has a null in it, when the dataset returns the XML, the field is not present. When I put data in this column, the field is then present in the returned XML.

Is there a setting for enabling null fields in the results.

Here is the code

<code>SqlCommand sqlCommand = new SqlCommand("Select * from MyTable",sqlConnection1);

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = sqlCommand;
sqlConnection1.Open();
DataSet custDS = new DataSet();
custDA.Fill(custDS,strName);
sqlConnection1.Close();
strRet = custDS.GetXml();</code>

Re: Datasets

Alexandru Savescu5-Nov-02 23:37

Alexandru Savescu

5-Nov-02 23:37

May I ask why it bothers you this? The XML file is smaller. Smile | :)

Best regards,
Alexandru Savescu

Re: Datasets

Paul Watson6-Nov-02 3:15

Paul Watson

6-Nov-02 3:15

This might help[^], search for "drops column fields with null values" to get to the relevant paragraph.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

Re: Datasets

Scoop6-Nov-02 3:23

Scoop

6-Nov-02 3:23

Thanks;)

Re: Datasets

Paul Watson6-Nov-02 3:26

Paul Watson

6-Nov-02 3:26

Here is another one[^] which seems quite good. It talks about using an xsi:null instance to handle DBnull values.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

feedback from stored proc

SimonS5-Nov-02 22:11

SimonS

5-Nov-02 22:11

I have a long running stored procedure that populates a OUTPUT variable with values for each stage of the procedure.

What I would like to do is to have my C# app get these values during the course of the proc, but I'm not sure how to do this.

The procedure has numerous lines like:

SELECT @MyOutputVal = some_other_proc_that_returns_a_value

SET NO COUNT ON is being used, so obviously no intermediate vals are going to be returned at the moment.
Any ideas on how I need to structure the VC# code to catch the output values, values?

Cheers,
Simon

"VB.NET ... the STD of choice", me, internal company memo

Re: feedback from stored proc

Richard Deeming6-Nov-02 3:15

Richard Deeming

6-Nov-02 3:15

The value of your output parameters will only be returned to your app when the procedure has finished. If that's all you need, all you have to do is set the Parameter.Direction property to ParameterDirection.Output. If you want values from different stages, but don't need them until the end of the procedure, you can use multiple output parameters.

If you need feedback during the execution, you can use PRINT commands in your stored procedure, and sink the InfoMessage event of the connection. When you call PRINT 'Some message' in the procedure, you'll get an InfoMessage event, and the Message property of the SqlInfoMessageEventArgs will contain the string you printed.

Re: feedback from stored proc

SimonS6-Nov-02 18:58

SimonS

6-Nov-02 18:58

Thanks for the info, Richard.

I've forwarded this to my colleague.

Cheers,
Simon

"VB.NET ... the STD of choice", me, internal company memo

ADO.NET Books

Paul Selormey5-Nov-02 15:06

Paul Selormey

5-Nov-02 15:06

Hello All,
Any recommendation for .NET database programming books? I need books that will really speak to me - I have the unlucky project to write Managed Providers. The books must be written in C# (do not like books written in two languages like C# and VB.NET - do not have the time).

Most of the stuff at Amazon.com seems to be VB.NET books - doing close to zero VB.NET currently.

Best regards,
Paul.

Jesus Christ is LOVE! Please tell somebody.

Re: ADO.NET Books

Vasudevan Deepak Kumar6-Nov-02 3:57

Vasudevan Deepak Kumar

6-Nov-02 3:57

hi,

since u need the core internals of .net too, try

"c# and .net framework -- c++ perspective"

that should best fit your requirement.

Deepak Kumar Vasudevan
http://deepak.portland.co.uk/

Re: ADO.NET Books

Paul Selormey6-Nov-02 4:11

Paul Selormey

6-Nov-02 4:11

Just gone to check it. It is rating very low at Amazon.com. The table of contents does not seems to carry anything new. I have 8 books on .NET so far, with 4 on C# - none deals with the internals, anyway.

Best regards,
Paul.

Jesus Christ is LOVE! Please tell somebody.

SQL Wildcard Search

Richard Hudson4-Nov-02 22:40

Richard Hudson

4-Nov-02 22:40

Please could someone help me?
I have a SQL command which retrieves records no problem.
I can do wildcard searches with every field except my date field. What am I doing wrong?
Can someone give me some ideas or sample code.

Thankyou very much!

Re: SQL Wildcard Search

Jon Hulatt5-Nov-02 5:41

Jon Hulatt

5-Nov-02 5:41

internally, a date is represented as an integer of some sort. dates are converted to string representations at the last possible moment. This is why you can do arithmetic with dates:-

select DateField1 + DateField2 from table

and you can use inequalities as conditional clauses too:-

select * from table where date1 < date2

Anyway, if you want to do some kind of wildcard matching, on strings, then you need to dynamically convert the date to a string:-

select * from table
where convert(varchar(25),datefield) like '%-10-%'

That should help you out. I am a bit worried though. The like clause, and use of wildcards, is a surefire way to kiss goodbye to performance. Particularly when you mention that you use wildcards on every field.

If you are trying to implement keyword searching on the data in your table, i'd suggest you look up full text indexing.

Signature space for rent. Apply by email to....

Re: SQL Wildcard Search

Pete Bassett5-Nov-02 5:50

Pete Bassett

5-Nov-02 5:50

I've replyed to your other post (naughty!)

http://www.codeproject.com/script/comments/forums.asp?msg=328767&forumid=1647#xx328767xx[^]

Pete

Insert Sig. Here!

strange dates...

Ray Cassick4-Nov-02 8:53

Ray Cassick

4-Nov-02 8:53

I have a feild of a SQL2000 DB set up as datetime, and I am trying to place a date into it as '11/4/2003'. The record gets inserted ok, but the date comes out as '1/1/1900'

Anyone care to gander as why?

Re: strange dates...

Andreas Saurwein4-Nov-02 9:23

Andreas Saurwein

4-Nov-02 9:23

I suppose your dateformat on the SQL server isnt 'd/m/yy' so it recognized it as 'something' but cant make a valid date out of it. So it gets inserted as 'empty' date which resolves to 1/1/1900.

I keep submitting “VB” as a Priority-1 bug, but apparently no one here knows how to fix it. Nick Hodapp, Semicolon

Re: strange dates...

Jon Hulatt5-Nov-02 5:38

Jon Hulatt

5-Nov-02 5:38

Tip:- to save confusion when using dates in MSSQLServer, always use the format like this:-

YYYY-MM-DD HH:MM:SS

eg

'2002-11-05'

or

'2002-11-05 16:35:00'

this really helps to eliminate date ordering oddness.

Signature space for rent. Apply by email to....

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.