Adding a Column to a Cursor 1

[David Higgs]

I would like to add a Column to Cursor 'curLogbook'.

I currently populate 'curLogbook' using:

SQLEXEC(SQLCONN,lcFields_List,'curLogbook')

I would like to add a "ALt_Col" Field and tried using the following code:

X = "Alt_Col Num(6)"
ALTER TABLE curLogbook ADD COLUMN &x

which resulted in an Error No: 1525 'Function not supported on remote tables'

As the following code works ok, I assume the error is because I'm using a remote MySQL Database to populate 'curlogbook' in the above example.

	CREATE CURSOR Cntry_Band (Band C(3), Band_tot Num(1) )

	INSERT INTO Cntry_Band (Band) VALUES ("160")
	INSERT INTO Cntry_Band (Band) VALUES ("80")
	INSERT INTO Cntry_Band (Band) VALUES ("60")
	
	X = "Alt_Col Num(6)"
	ALTER TABLE Cntry_Band ADD COLUMN &x

Regards,

David (G4NVB)

Recreational Developer and End User of VFP for my own personal use in 'Amateur Radio' and 'British Railways' related Applications.

 

[Chris Miller]

It surely depends on

a) what lcFields_List is, which you don't show
b) how the mysql connection is defined, which you don't show.

The simplest idea without changing the connection but surely the SQL code that's in lcFields_List and executed on the MySQL Server would be to add the field there. A clause like ...123456 as alt_col... in the code would add it with all records having the value 123456. To specify a type like num(6) you could use CAST(0 as decimal(6,0)) as alt_col.



Chriss

 

[David Higgs]

Hello Chriss,

The MySQL Database is shared between my application and a 3rd party application.

For my application I load the Contents of the Database into a Cursor ‘curLogbook’. I then use this Data in conjunction with my own data to display information that is unavailable with the 3rd party Software.

Due to sharing the 3rd party Database I prefer not to modify it's structure hence my reason for wanting to add a field to my ‘curLogbook’.

You mentioned the use of “CAST”; I have now used this method to achieve what I had set out to do, having an 'Alt_Col' Field in ‘curLogbook’.

Thank you for your help.


Regards,

David (G4NVB)

Recreational Developer and End User of VFP for my own personal use in 'Amateur Radio' and 'British Railways' related Applications.

 

[Chris Miller]

I was never suggesting to change the database. Just your SQL.

I and others could help much better, if we knew what's in lcFields_List when you execute it. You don't have to explain that SQLEXEC(SQLCONN,lcFields_List,'curLogbook') creates a cursor curLogbook that you then process in your application, that's simply the definition of SQLEXEC and the way we all work on remote data, nothing special needing an explanation.

You have to understand that lcFields_List is coming from your code, it's coming from your VFP application and it's executed by the mySQL server. That's usually SQL and as you have that in your hand you can change it, that doesn't change any table in the MySQL database.

Adding fields by using an expression, in the simplest case a constant and giving it a field name, you add a field to the result. And to the result only, not the table it comes from..

SELECT *, 0 as additionalfield FROM table

This creates an additionalfield in the result, without modifying table.

Chriss

 

[David Higgs]

I and others could help much better, if we knew what's in lcFields_List when you execute it.

TEXT TO lcFields_List TEXTMERGE NOSHOW
	SELECT 
	Col_Primary_Key, Col_Band, COL_Call, Col_Cnty, Col_Comment, Col_Cont, Col_Contacted_Op, Col_Contest_ID, Col_Country, Col_CQZ,
	Col_Distance, Col_DXCC, COL_EQ_Call, Col_EQSL_QSLRDATE, Col_EQSL_QSLSDATE, Col_EQSL_QSL_RCVD, Col_EQSL_QSL_SENT, Col_EQSL_STATUS,
	Col_Freq, Col_Gridsquare, Col_IOTA, Col_ITUZ, Col_Lat, Col_Lon, Col_Mode, Col_My_City, Col_My_Cnty, Col_My_Country, Col_My_CQ_Zone,
	Col_My_Gridsquare, Col_My_IOTA, Col_My_ITU_Zone, Col_My_Lat, Col_My_Lon, Col_My_Name, Col_My_Postal_Code, Col_My_Rig, Col_My_Street,
	Col_Name, Col_Notes, Col_Operator, Col_Owner_Callsign, Col_PFX, Col_QSLMSG, Col_QSLRDATE, Col_QSLSDATE, Col_QSL_Rcvd, Col_QSL_Rcvd_VIA,
	Col_QSL_Sent, Col_QSL_Sent_VIA, Col_QSL_VIA, Col_QTH, Col_RIG, Col_RST_RCVD, Col_RST_SENT, Col_SRX, Col_SRX_STRING, Col_STATE,
	Col_STATION_CALLSIGN, Col_STX, Col_STX_STRING, Col_TIME_ON, Col_TIME_Off, COL_Tx_PWR, 
	COL_USER_DEFINED_0 as WAB_Lg_Sq, COL_USER_DEFINED_1 as SOTA, COL_USER_DEFINED_2 as WAB_Sm_Sq,
	COL_USER_DEFINED_3 as WAI, COL_USER_DEFINED_4 as Trig_Point, COL_USER_DEFINED_8 as COL_My_Antenna	
	FROM <<TBL_Logbook>>
	
	ENDTEXT
	
	RETURN lcFields_List

You don't have to explain that SQLEXEC(SQLCONN,lcFields_List,'curLogbook') creates a cursor curLogbook that you then process in your application, that's simply the definition of SQLEXEC and the way we all work on remote data, nothing special needing an explanation.

I apologise; if you look back through some of my previous posts you will see that I often struggle with what to include and what not to include in my postings. What may seem irrelevant to me, maybe very relevant to a professional such as yourself. As my 'signature' shows I am not a professional programmer



Regards,

David (G4NVB)

Recreational Developer and End User of VFP for my own personal use in 'Amateur Radio' and 'British Railways' related Applications.

 

[Chris Miller]

Ah, there you go. Thank you, David.

This is a SQL Select query, not just a field list. The list of fields surely is what stands out the most, but it's SQL. And that was to be expected as that's what the second parameter of SQLEXEC is for. I can only recommend if you don't have a full understanding of what happens and what things are for, take a read in the help and see what the parameters mean.

You can't, for example, just send a field list, just the portion between SELECT and FROM, to MySQL Server and expect anything to happen.

You can add to this SQL, as already said, but to make it even clearer:

TEXT TO lcFields_List TEXTMERGE NOSHOW
	SELECT 
	Col_Primary_Key, Col_Band, COL_Call, Col_Cnty, Col_Comment, Col_Cont, Col_Contacted_Op, Col_Contest_ID, Col_Country, Col_CQZ,
	Col_Distance, Col_DXCC, COL_EQ_Call, Col_EQSL_QSLRDATE, Col_EQSL_QSLSDATE, Col_EQSL_QSL_RCVD, Col_EQSL_QSL_SENT, Col_EQSL_STATUS,
	Col_Freq, Col_Gridsquare, Col_IOTA, Col_ITUZ, Col_Lat, Col_Lon, Col_Mode, Col_My_City, Col_My_Cnty, Col_My_Country, Col_My_CQ_Zone,
	Col_My_Gridsquare, Col_My_IOTA, Col_My_ITU_Zone, Col_My_Lat, Col_My_Lon, Col_My_Name, Col_My_Postal_Code, Col_My_Rig, Col_My_Street,
	Col_Name, Col_Notes, Col_Operator, Col_Owner_Callsign, Col_PFX, Col_QSLMSG, Col_QSLRDATE, Col_QSLSDATE, Col_QSL_Rcvd, Col_QSL_Rcvd_VIA,
	Col_QSL_Sent, Col_QSL_Sent_VIA, Col_QSL_VIA, Col_QTH, Col_RIG, Col_RST_RCVD, Col_RST_SENT, Col_SRX, Col_SRX_STRING, Col_STATE,
	Col_STATION_CALLSIGN, Col_STX, Col_STX_STRING, Col_TIME_ON, Col_TIME_Off, COL_Tx_PWR, 
	COL_USER_DEFINED_0 as WAB_Lg_Sq, COL_USER_DEFINED_1 as SOTA, COL_USER_DEFINED_2 as WAB_Sm_Sq,
	COL_USER_DEFINED_3 as WAI, COL_USER_DEFINED_4 as Trig_Point, COL_USER_DEFINED_8 as COL_My_Antenna[highlight #FCE94F],
        CAST(0 as Decimal(6,0)) as alt_col[/highlight]
	FROM <<TBL_Logbook>>
ENDTEXT

I highhlighted the change. The rest can stay as is, just notice the additional comma after the COL_My_Antenna field, as it's now not the last field in the list.

The names "COL_USER_DEFINED_N" seem to be something like multi purpose "reserved fields" for future use. That's bad database design style in my opinion. You only need fields for what you actually want to sotr permanently, if your data read into memory in VFP cursors or PHP or Javascript arrays or whatever in-memory data structure in the client language needs something extra, you can always create it via expressions, you cannot only select existing fields and data from a table.

Now the only open question is why the cursor this SQL creates wasn't accepting an ALTER TABLE to add the field as aftermath of the unchanged query. It's less important, but it must have to do with options of the connection. In general you can alter a cursor in VFP, no matter if you got it from a VFP native query of DBFs, from a local or remote view, from a cursoradapter or you just use SQLEXEC to create it, it's all a DBF in the end, when it's in a VFP workarea. Just like a USE. And in case of SQLEXEC or anything else not being a straight forward USE of a DBF file, an ALTER TABLE only alters the workarea cursor, not a DBF nor a remote table. For that to change, you would need to SQLEXEC an ALTER TABLE statement in your case.

But the connection may bind the cursor in some way to a server side MYSQL object and hinder you to alter the cursor. It's not necessary as you can foresee the fields you want in the result just by adding them to the list of the SELECT query.

Chriss

 

[David Higgs]

Chris, thank you for your reply, much appreciated.

I can see now why you needed to see the SQL Select query in full, so that you could advise on changes I could make to achieve what I wanted. I’ve incorporated your code in my application and of course, it worked flawlessly.

I can only recommend if you don't have a full understanding of what happens and what things are for, take a read in the help and see what the parameters mean.

I did take look at the VFP Help file with regards to using CASE but didn't find it that helpful. So that’s why I thought I’d ask on Tek-Tips.

The names "COL_USER_DEFINED_N" seem to be something like multi-purpose "reserved fields" for future use.

The "COL_USER_DEFINED_N" Fields are VARCHAR(64) Fields that the user can use to store whatever. I use some of them to store information for claiming awards.

Now the only open question is why the cursor this SQL creates wasn't accepting an ALTER TABLE to add the field as aftermath of the unchanged query.

That was the reason I mentioned I could get the ALTER TABLE to work with a CURSOR using CREATE CURSOR and not with a CURSOR using MySQL.



Regards,

David (G4NVB)

Recreational Developer and End User of VFP for my own personal use in 'Amateur Radio' and 'British Railways' related Applications.

 

[Chris Miller]

I could get the ALTER TABLE to work with a CURSOR using CREATE CURSOR and not with a CURSOR using MySQL.

I know, but let me point out in general there is nothing different about a cursor that you create by CREATE CURSOR or create by SQLEXEC().In general you can ALTER both. What I forgot is error 1525 'function is not supported on remote tables' is general. And it's misleading as it perhaps made you think ALTER TABLE of the cursor would modify the remote table as well as the result cursor.

SQLGETPROP("SourceType") will give 2 for a cursor coming from a remote database.

So you can make it a new VFP cursor again, that you can alter by first copying it:

SELECT * FROM curLogbook INTO CURSOR curLogbook2 READWRITE
ALTER TABLE curLogbook2 add COLUMN Alt_Col Num(6)

The connection specific reason I had in mind was coming from ADO recordset options to generate a recordset that has a server side cursor by setting CursorLocation = adUseServer and thought of a similar MySL option you might have in your DSN or connections string.

It's actually a stupid restriction VFP puts on itself, because only sqlexec result cursors you bind to the remote table with cursorsetprops making them updatable actually bind it back to the remote backend. And even then altering the cursor would just alter it, generate a field you then can't store on the remote database as it doesn't exist there. A bound cursor aka updatable remote cursor is still a VFP cursor of which VFP only knows corresponding fields of the remote table, it's not a local reference to the remote table, it's a data copy, jusst like any query result of remote data is.

Note: You can also copy a workarea with USE DBF('curLogbook') AGAIN ALIAS curLogbook2, but then you also copy over the "SourceType" cursor property that make VFP deny ALTER TABLE on it.

The ooverall conclusion is, you can trick ALTER TABLE to work when you copy over the sqlresult, but then you actually have to copy, not just USE AGAIN with new alias name, which makes adding fields in the SQL-Select you initially execute the better option in terms of already having the field. It has a price, this new field already is added to the remote result and transported via network to the VFP datasession. And the network may be slower about the additional transport of one of many fields than RAM is in copying the whole data afterwards.




Chriss