Hi,
I have ISTRINGS to store variables such a lName (Last name), and it is set to "Memo" type in the DB table.
But when I try to increase this size to say 300 from 255 I get SQL error 07002.
All corresponding fields (as far as I can see) are set to 300 - what am I doing wrong here anyone?
iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,300) '<------------------- Here!
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes,255)
dbBindParameter(hStmt,18,cphoto,255)
endif
dbExecute(hStmt)
IF LEN(dbGetErrorCode(hStmt))
' <--------- Error message here
MESSAGEBOX w1,"("+STR$(__LINE__)+") "+dbGetErrorText(hStmt)+"\n"+iStmt,dbGetErrorCode(hStmt)
ENDIF
dbFreeSQL(hStmt)
hStmt=NULL
Please remember I know very little about databases!
Thanks,
Andy.
Andy,
I don't see MEMO being referenced in IWB. Why not ISTRING your variable to the required length - lName ISTRING(300), and then when you call CREATE TABLE, set the column name lName as lName VARCHAR(300)? That should work
Brian
Tried VARCHAR(300), gives error. I am looking into setting the 'Unicode Compression' to YES, which might mean you could leave the field as 255 and let Access handle the Unicode for you. However, so far, I have not found a way to turn Unicode Compression on from within the program. If you have ACCESS you can change after the DB is created, but that is not the solution that will solve every one else who tries this.
Also note that MEMO is now LONGTEXT when using "Microsoft Access Driver (*.mdb, *.accdb)" driver, if you have it.
Bill
Test program attached, use CONSOLE to compile.
Found a way around and it does save into the Memo field.
See attached program. However, still going to look into using Unicode Compression.
Bill
How I use memo fields in programs like my Snippet Manager
1) When I create the field in the table I define it as "snip longtext"
2)When doing it this way the size of what I can store is determined solely by the variable I use; in my case -- istring snip[100001]
3) and the proper syntax for binding would be -- dbBindParameter(hstmt,8,snip,255) <- notice the 255
I've never tried Brian's way for strings over 255 but I've use it many times for very short strings and it works very well.
If it indeed works the same over 255 as it does for under 255 then his method would save memory over using a memo field since memo fields consume memory in blocks.
Larry, I have tried to do 'WITH COMP' and 'WITH COMPRESSION' to turn on the Unicode Compression on a TEXT/LONGTEXT field and it gives me an error. Just wondering if you might know a way to turn the COMPRESSION on?
Thanks,
Bill
PS, found this:
Note The WITH COMPRESSION and WITH COMP keywords listed in the previous SQL statements can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface.
Never used Jet OLE DB provider or ADO, so will need to see if we can do this from IWB.
I looked through the include files for the iwdb source code and found extensive use of a compiler directive ODBCVER30 if defining consts and declaring functions.
I found a note mentioning ODBCVER35 and UNICODE but no alternate const or function definitions.
Bottom line is that I will be of no help. Sorry
Thanks guys,
The reason I'm trying to exceed 255 characters is this:
In my database, I'm storing data as Unicode strings, here is an example:
200,3,187,3,187,3,187,3,187,3,187,3,187,3,0,0
As you can see it is using up much more space than single ANSII characters - but it's the only practical way to store Unicode that I've found.
So at the moment I can store around 42 Unicode characters as a string which fits in to 255,
Now that's okay for a first, last, other name etc, but not if you wanted a field where you can type some notes on the customer.
At the moment I'm using Bill's older suggestion and splitting up notes into two fields - notes and notes2.
I'm restricting the edit fields to 40 chars, so two note fields will give me 80 chars - still think I need a couple more.
But I think it would be interesting to see if we can exceed this in IWB.
Thanks,
Andy.
Andy
Did you try my way of writing a memo field and binding and it didn't work with your unicode data?
If you did try it and it worked then I believe there is a simple solution solution to to "compress" your dbase file ro regain no longer used memory.
Thanks Larry,
Yes I did try it, and no it didn't work, nor does Bills method.
Do I need to do an SQL / something update my end? :o
Andy.
This is the source for the "bind" command.
SUB dbBindVariable(hstmt as INT,column as INT,variable as ANYTYPE,opt pReturn=NULL as POINTER,opt cbSize=255 as INT),INT
DEF rc as WORD
DEF typ as WORD
DEF pParam as POINTER
DEF length as INT
pParam = NULL
length = 0
indTemp = 0
rc = FALSE
IF pReturn = NULL THEN pReturn = indTemp
IF hstmt AND ISREF(variable)
SELECT TYPEOF(variable)
CASE @TYPEINT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_LONG
CASE @TYPEUINT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_ULONG
CASE @TYPEWORD
length = 0
pParam = #<POINTER>variable
typ = SQL_C_USHORT
CASE @TYPESWORD
length = 0
pParam = #<POINTER>variable
typ = SQL_C_SHORT
CASE @TYPECHAR
length = 1
pParam = #<POINTER>variable
typ = SQL_C_CHAR
CASE @TYPESCHAR
length = 1
pParam = #<POINTER>variable
typ = SQL_C_CHAR
CASE @TYPESTRING
length = cbSize
pParam = ##<STRING>variable
typ = SQL_C_CHAR
CASE @TYPEFLOAT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_FLOAT
CASE @TYPEDOUBLE
length = 0
pParam = #<POINTER>variable
typ = SQL_C_DOUBLE
CASE @TYPEINT64
length = 0
pParam = #<POINTER>variable
typ = SQL_C_SBIGINT
CASE @TYPEUINT64
length = 0
pParam = #<POINTER>variable
typ = SQL_C_UBIGINT
default
length = cbSize
pParam = #<POINTER>variable
typ = SQL_C_BINARY
ENDSELECT
rc = SQLBindCol(hstmt,column,typ,pParam,length,pReturn)
IF(rc = SQL_SUCCESS) OR (rc = SQL_SUCCESS_WITH_INFO) THEN rc = TRUE ELSE rc = FALSE
ENDIF
RETURN rc
ENDSUB
Notice that this "SQL_C_WCHAR" is missing. I don't know enough to know what else is missing elsewhere to make it all work.
How is the field defined in your SQL DB - Varchar allows a larger field size.
Thanks every one for your help on this.
What I have found out is that the data is indeed being stored, that is longer than 255 characters in the DB.
However, it's only reading back to the maximum of a normal string, even though the field in an ISTRING.
Attached is my old Unicode DB example, thought it would be easier to play around with this one rather than my main program.
You will see that the "Product" field is defined as ISTRING Product[2000] (made it large so there's no mistakes).
In the console screen that opens, you will see me printing
print "Product Characteer 255 = ",Product[255]
This returns nothing, if you set it to say 25 you get the character.
So the problem seems to be retrieving more than 254 charaters.
Help!
Andy.
How is product defined? if product is defined as product[255] that would be 0-254 so if you access pos 255 you would be beyond the definition. Just one of those things I have often done.
Hi Larry,
The variable Product is an ISTRING with space for 2000 characters.
The dbbind is 255 as every one tells me thats okay.
As I said, it is saving beyond 255 characters, just not reading them back in.
Don't know yet if it's a DB problem or if it's the code that translates it.
Andy.
OK, this is what I learned by looking at the web, the sample projects and the help file.
- Prior to MySQL 5.0.3, a VARCHAR could only store up to 255 characters.
- Help file mentions BINARY as a data type that DBMS should support. But I could never make it to work properly with dbBindParameter (tried unicode buff, int array, UDT).
- After many tests, I found out that VARBINARY can accept unicode data inside dbPrepareSQL if it's entered as an hexagesimal string. I've seen hex strings on some old codes before, but this is actually the first time I need to use them. For instance the hex string of L"ab" would be "0x610062000000"
- VARBINARY also seems to have its own limit of 510 bytes. which means that it can save up to 255 unicode characters at most.
- dbBindVariable binds unicode data without any issue, which is surprising.
Putting all of this together I have come up with an example that seems to work fine, it may have mistakes, I learned through this topic on how to write/read a database for the first time. If you can't break the 255 ansi character limit here's an alternative, but it requires many changes to your code.
Run once and it should create the database, 2 VARCHAR ansi fields and one VARBINARY unicode field. The exe directory needs write access permission.
Close it and run it again to read the data back in and print it to the console.
$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"
IWSTRING buffer[255] ' 510 seems to be max allowed size for BINARY
OPENCONSOLE
PRINT
IF PathFileExists(GETSTARTPATH() + "mydatabase.mdb") = 0 THEN CreateDatabase() ELSE ReadDatabase()
PRINT
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
SUB ReadDatabase()
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")
IF pdb THEN
string first, last
INT branch_null
INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")
IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 510)
print "Reading database..."
print
WHILE dbGet(hStmt)
print "First ansi name: ", first
print "Last ansi name: ", last
print "Unicode data: " + w2s(buffer)
print
ENDWHILE
dbFreeSQL(hStmt)
ENDIF
dbDisconnect(pdb)
ENDIF
ENDSUB
SUB CreateDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")
IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")
IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )
IF hstmt THEN
'add database record
DatabaseAddRecord(pdb, "Miller", "Lisa", L"Some unicode info about Lisa")
DatabaseAddRecord(pdb, "Miller", "Tammy", L"Some unicode info about Tammy")
dbFreeSQL(hstmt)
PRINT "Database created. Restart the console."
ENDIF
dbDisconnect(pdb)
ENDIF
ENDIF
ENDSUB
SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
string unicodeHEX = WstringToHexString(unicodeData)
INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")
IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)
dbFreeSQL(hstmt)
ENDIF
ENDSUB
SUB WstringToHexString(wstring unicodeData), string
string unicodeHEX = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0
DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0
RETURN unicodeHEX
ENDSUB
SUB GetHEX(int t), string
string hex = HEX$(t)
if hex = "0" THEN hex = "00"
RETURN hex
ENDSUB
Fasecero,
Thank you, you've (any many others) have put a lot of work into this - as I have done too.
Well I've managed to create a new DB with one of the fields called "notes" which is set to varbinary(510) - that is Binary type field.
I have a Unicode edit control called IDE_notes, and I'm trying to update this field with Unicode characters but again I have a problem.
If I type in English (in the control IDE_notes) the update to the DB works.
If I type in another language, it doesn't - just stores nonsense.
So these are the parts of the code:
wstring DisplayText = L""
CreateWindowW(L"EDIT", L"",WS_VISIBLE|WS_CHILD|SS_LEFT|0x200, 90,310,820,20, w1.hwnd, IDE_notes, GetModuleHandle(0), 0)
Now this is what I have for the update section...
GetWindowTextW(GetDlgItem(w1.hwnd,IDE_notes),DisplayText,255)
string notesx = WstringToHexString(DisplayText)
iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes="+notesx+",notes2=?,notes3=?,notes4=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,200)
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes,0,255)
dbBindParameter(hStmt,18,notes2,255)
dbBindParameter(hStmt,19,notes3,255)
dbBindParameter(hStmt,20,notes4,255)
dbBindParameter(hStmt,21,cphoto,255)
And finally...
SUB WstringToHexString(wstring unicodeData), string
string unicodeHEX = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0
DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0
RETURN unicodeHEX
ENDSUB
SUB GetHEX(int t), string
string hex = HEX$(t)
if hex = "0" THEN hex = "00"
RETURN hex
ENDSUB
So it's not quite right here yet.
I've tried variations for the variable "DisplayText" such as IWSTRING with a size, dbBindParameter(hStmt,17,notes,0,255)
and dbBindParameter(hStmt,17,notes,255) or 510 - but still can't store Unicode from the edit control.
But interestingly.....
If I change dbBindParameter(hStmt,17,notes,255)
to dbBindParameter(hStmt,17,notesx,255) it's being stored in the next field "notes2".
Andy.
I see, the problem may be due to many causes, I'll not be able to find out until I write a small gui version of my sample code and paste non english characters. I'll try to do it soon. Meanwhile, maybe someone else can come up with another solution :)
Yesterday, I made a few small additional fixes to WstringToHexString/GetHEX. I just made a GUI sample - working fine from my side.
$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"
'------------------------------------------
' VARIABLES
'------------------------------------------
IWSTRING buffer[255] ' 510 seems to be max allowed size for BINARY
INT databaseCreated = 0
INT j
'------------------------------------------
' INTERFACE
'------------------------------------------
CONST BUTTON_1 = 1
CONST BUTTON_2 = 2
CONST EDIT_3 = 3
CONST BUTTON_4 = 4
CONST STATIC_5 = 5
CONST STATIC_6 = 6
CONST EDIT_7 = 7
CONST STATIC_8 = 8
CONST EDIT_9 = 9
CONST STATIC_10 = 10
CONST EDIT_11 = 11
WINDOW w1
OPENWINDOW w1,0,0,553,550,@SIZE,NULL,"Simple Window",&w1_handler
ENABLETABS w1, 1
SETWINDOWCOLOR w1, GetSysColor(COLOR_3DFACE)
CONTROL w1,@SYSBUTTON,"Create Database",43,15,193,45,0x50000000,BUTTON_1
CONTROL w1,@BUTTON,"Delete Database",281,16,218,42,0x50000000,BUTTON_2
CONTROL w1,@EDIT,"",135,104,229,25,0x50800000|@TABSTOP,EDIT_3
CONTROL w1,@SYSBUTTON,"Add Record and save it to the Databse",378,103,123,142,0x50002000,BUTTON_4
CONTROL w1,@STATIC,"First name",45,109,70,20,0x5000010B,STATIC_5
CONTROL w1,@STATIC,"Last name",45,148,70,20,0x5000010B,STATIC_6
CONTROL w1,@EDIT,"",137,145,228,22,0x50800000|@TABSTOP,EDIT_7
CONTROL w1,@STATIC,"Info",48,193,70,20,0x5000010B,STATIC_8
'CONTROL w1,@EDIT,"",138,190,228,57,0x50800004,EDIT_9
UnicodeEditCreate(w1, EDIT_9, 138, 190, 228, 57, 0)
CONTROL w1,@STATIC,"After a new record is added, the database is (re)loaded below",53,295,448,27,0x5000010B,STATIC_10
'CONTROL w1,@LISTBOX,"ListBox1",53,334,448,140,0x50800340,EDIT_11
UnicodeEditCreate(w1, EDIT_11, 53, 334, 448, 140, 1)
OnInit()
' main loop
WAITUNTIL w1 = 0
END
' window procedure
SUB w1_handler(), INT
SELECT @MESSAGE
CASE @IDCREATE
CENTERWINDOW w1
CASE @IDCLOSEWINDOW
OnClose()
CLOSEWINDOW w1
CASE @IDCONTROL
SELECT @CONTROLID
CASE BUTTON_1
IF @NOTIFYCODE = 0
OnCreateDatabase()
ENDIF
CASE BUTTON_2
IF @NOTIFYCODE = 0
OnDeleteDatabase()
ENDIF
CASE BUTTON_4
IF @NOTIFYCODE = 0
OnNewRecord()
ENDIF
ENDSELECT
ENDSELECT
RETURN 0
ENDSUB
'------------------------------------------
' EVENTS
'------------------------------------------
SUB OnInit()
FOR j = 1 TO 11
SETFONT w1,"Segoe UI",11,400,0,j
NEXT j
databaseCreated = PathFileExists(GETSTARTPATH() + "mydatabase.mdb")
UpdateUI()
IF databaseCreated THEN LoadDatabase()
ENDSUB
SUB OnClose()
' nothing?
ENDSUB
SUB OnCreateDatabase()
CreateNewDatabase()
ENDSUB
SUB OnDeleteDatabase()
DeleteDatabase()
ENDSUB
SUB OnNewRecord()
CreateNewRecord()
ENDSUB
'------------------------------------------
' DATABASE
'------------------------------------------
SUB CreateNewDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")
IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")
IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )
IF hstmt THEN
databaseCreated = 1
UpdateUI()
dbFreeSQL(hstmt)
ENDIF
ENDIF
dbDisconnect(pdb)
ENDIF
ENDSUB
SUB DeleteDatabase()
IF databaseCreated THEN
DELETEFILE GETSTARTPATH() + "mydatabase.mdb"
databaseCreated = 0
ResetUI(1)
UpdateUI()
ENDIF
ENDSUB
SUB CreateNewRecord()
' check if an edit is empty
INT empty_edit = 0
string firstName = GETCONTROLTEXT(w1, EDIT_3)
string lastName = GETCONTROLTEXT(w1, EDIT_7)
UnicodeEditGetText(w1, EDIT_9, buffer, 255)
IF LEN(firstName) = 0 THEN empty_edit = 1
IF LEN(lastName) = 0 THEN empty_edit = 1
IF LEN(buffer) = 0 THEN empty_edit = 1
IF empty_edit THEN
MESSAGEBOX(w1, "Empty Edit Box", "error", @MB_ICONSTOP)
RETURN
ENDIF
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")
IF pdb THEN
DatabaseAddRecord(pdb, lastname, firstname, buffer)
ResetUI(0)
dbDisconnect(pdb)
LoadDatabase()
ENDIF
ENDSUB
SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
istring unicodeHEX[510] = WstringToHexString(unicodeData)
INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")
IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)
dbFreeSQL(hstmt)
ENDIF
ENDSUB
SUB WstringToHexString(wstring unicodeData), string
istring unicodeHEX[510] = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0
DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0
RETURN unicodeHEX
ENDSUB
SUB GetHEX(int t), string
istring hex[3] = HEX$(t)
if LEN(hex) = 1 THEN hex = "0" + hex
RETURN hex
ENDSUB
SUB LoadDatabase()
IWSTRING text[10000] = L""
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")
IF pdb THEN
string first, last
INT branch_null
INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")
IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 510)
WHILE dbGet(hStmt)
text += S2W(first) + L" - " + S2W(last) + L" - " + buffer + L"\n"
ENDWHILE
UnicodeEditSetText(w1, EDIT_11, text)
dbFreeSQL(hStmt)
ENDIF
dbDisconnect(pdb)
ENDIF
ENDSUB
'------------------------------------------
' UI PROCEDURES
'------------------------------------------
SUB UpdateUI()
ENABLECONTROL w1, BUTTON_1, InvertFlag(databaseCreated)
ENABLECONTROL w1, BUTTON_2, databaseCreated
ENABLECONTROL w1, EDIT_3, databaseCreated
ENABLECONTROL w1, EDIT_7, databaseCreated
ENABLECONTROL w1, EDIT_9, databaseCreated
ENABLECONTROL w1, BUTTON_4, databaseCreated
ENABLECONTROL w1, EDIT_11, FALSE
ENDSUB
SUB ResetUI(INT allofthem)
SETCONTROLTEXT w1, EDIT_3, ""
SETCONTROLTEXT w1, EDIT_7, ""
UnicodeEditSetText(w1, EDIT_9, L"")
IF allofthem THEN UnicodeEditSetText(w1, EDIT_11, L"")
ENDSUB
SUB InvertFlag(INT flag), INT
IF flag THEN RETURN 0
RETURN 1
ENDSUB
'------------------------------------------
' UNICODE EDIT CONTROL
'------------------------------------------
SUB UnicodeEditCreate(window w, INT ctrlID, INT x, INT y, INT width, INT height, INT multiline)
INT dwStyles = WS_CHILD | WS_VISIBLE | ES_LEFT | WS_TABSTOP
IF multiline THEN dwStyles = dwStyles | WS_VSCROLL | ES_MULTILINE
INT hwndEDIT = CreateWindowExW(WS_EX_CLIENTEDGE, L"EDIT",NULL, dwStyles, x, y, width, height, w.hwnd, ctrlID, GetModuleHandle(0), NULL)
ENDSUB
SUB UnicodeEditGetText(window w, INT ctrlID, pointer buffer, INT maxCount)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
GetWindowTextW(hwndEDIT, buffer, maxCount)
ENDSUB
SUB UnicodeEditSetText(window w, INT ctrlID, pointer buffer)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
SetWindowTextW(hwndEDIT, buffer)
ENDSUB
Fasecero,
That is actually superb work!
Yes it works here too, but I've amended a test version of my main program (with a new test DB) and it works on that too.
Just one question (and there's always one more - sorry), when I change you code to multi line like this:
UnicodeEditCreate(w1, EDIT_9, 10, 190, 1000, 57, 1)
and then type away onto a second line then save, it's not correct when it saves.
I will have a look now.
Thanks,
Andy.
:)
Well the one thing I can work out is that:
If 510 is the maximum length you can store then the string length you can have is this -
510 - 6 characters "0x and 0000" - that is the start / end of the string.
So that gives you 504 characters available.
504 / 4 (per character) = 126.
So the maximum characters you can store is 126 - which is a big improvement from the 40 or so I could do.
So finally, it's not a multi line issue, it's the string length that matters - and a check must be put in place for this I think.
Andy.
And one final interesting note:
This is the update code for my main program
iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes="+notesx+",notes2=?,notes3=?,notes4=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,200)
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes2,255)
dbBindParameter(hStmt,18,notes3,255)
dbBindParameter(hStmt,19,notes4,255)
dbBindParameter(hStmt,20,cphoto,255)
If you look closely at it, bind parameter for "notes" is left out, and that's the only way I found it to work.
Is this correct?
All I can say is it works here.
Andy.
QuoteIf you look closely at it, bind parameter for "notes" is left out, and that's the only way I found it to work.
Is this correct?
Yes, don't bind the unicode parameter and use the hex "trick" :)
QuoteIf 510 is the maximum length you can store then the string length you can have is this -
510 - 6 characters "0x and 0000" - that is the start / end of the string.
So that gives you 504 characters available.
504 / 4 (per character) = 126.
The example seems to agree with you about this, just tried an unicode with 130 characters and the application has crashed. Even so, inside the database each character takes only 2 bytes long, so it should be at least 504/2 (per character) = 252. Something else must be wrong in my code.
wow. I made a modification and now you can store up to any unicode size! ;D
$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"
'------------------------------------------
' VARIABLES
'------------------------------------------
SETPRECISION 0
$DEFINE MAX_UNICHARS 1000 ' MAXIMUN NUMBER OF UNICODE CHARACTER ALLOWED/WANTED
IWSTRING buffer[MAX_UNICHARS]
INT databaseCreated = 0
INT j
'------------------------------------------
' INTERFACE
'------------------------------------------
CONST BUTTON_1 = 1
CONST BUTTON_2 = 2
CONST EDIT_3 = 3
CONST BUTTON_4 = 4
CONST STATIC_5 = 5
CONST STATIC_6 = 6
CONST EDIT_7 = 7
CONST STATIC_8 = 8
CONST EDIT_9 = 9
CONST STATIC_10 = 10
CONST EDIT_11 = 11
WINDOW w1
OPENWINDOW w1,0,0,553,550,@SIZE,NULL,"Simple Window",&w1_handler
ENABLETABS w1, 1
SETWINDOWCOLOR w1, GetSysColor(COLOR_3DFACE)
CONTROL w1,@SYSBUTTON,"Create Database",43,15,193,45,0x50000000,BUTTON_1
CONTROL w1,@BUTTON,"Delete Database",281,16,218,42,0x50000000,BUTTON_2
CONTROL w1,@EDIT,"",135,104,229,25,0x50800000|@TABSTOP,EDIT_3
CONTROL w1,@SYSBUTTON,"Add Record and save it to the Databse",378,103,123,142,0x50002000,BUTTON_4
CONTROL w1,@STATIC,"First name",45,109,70,20,0x5000010B,STATIC_5
CONTROL w1,@STATIC,"Last name",45,148,70,20,0x5000010B,STATIC_6
CONTROL w1,@EDIT,"",137,145,228,22,0x50800000|@TABSTOP,EDIT_7
CONTROL w1,@STATIC,"Info",48,193,70,20,0x5000010B,STATIC_8
'CONTROL w1,@EDIT,"",138,190,228,57,0x50800004,EDIT_9
UnicodeEditCreate(w1, EDIT_9, 138, 190, 228, 57, 1)
CONTROL w1,@STATIC,"After a new record is added, the database is (re)loaded below",53,295,448,27,0x5000010B,STATIC_10
'CONTROL w1,@LISTBOX,"ListBox1",53,334,448,140,0x50800340,EDIT_11
UnicodeEditCreate(w1, EDIT_11, 53, 334, 448, 140, 1)
OnInit()
' main loop
WAITUNTIL w1 = 0
END
' window procedure
SUB w1_handler(), INT
SELECT @MESSAGE
CASE @IDCREATE
CENTERWINDOW w1
CASE @IDCLOSEWINDOW
OnClose()
CLOSEWINDOW w1
CASE @IDCONTROL
SELECT @CONTROLID
CASE BUTTON_1
IF @NOTIFYCODE = 0
OnCreateDatabase()
ENDIF
CASE BUTTON_2
IF @NOTIFYCODE = 0
OnDeleteDatabase()
ENDIF
CASE BUTTON_4
IF @NOTIFYCODE = 0
OnNewRecord()
ENDIF
ENDSELECT
ENDSELECT
RETURN 0
ENDSUB
'------------------------------------------
' EVENTS
'------------------------------------------
SUB OnInit()
FOR j = 1 TO 11
SETFONT w1,"Segoe UI",11,400,0,j
NEXT j
databaseCreated = PathFileExists(GETSTARTPATH() + "mydatabase.mdb")
UpdateUI()
IF databaseCreated THEN LoadDatabase()
ENDSUB
SUB OnClose()
' nothing?
ENDSUB
SUB OnCreateDatabase()
CreateNewDatabase()
ENDSUB
SUB OnDeleteDatabase()
DeleteDatabase()
ENDSUB
SUB OnNewRecord()
CreateNewRecord()
ENDSUB
'------------------------------------------
' DATABASE
'------------------------------------------
SUB CreateNewDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")
IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")
IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )
IF hstmt THEN
databaseCreated = 1
UpdateUI()
dbFreeSQL(hstmt)
ENDIF
ENDIF
dbDisconnect(pdb)
ENDIF
ENDSUB
SUB DeleteDatabase()
IF databaseCreated THEN
DELETEFILE GETSTARTPATH() + "mydatabase.mdb"
databaseCreated = 0
ResetUI(1)
UpdateUI()
ENDIF
ENDSUB
SUB CreateNewRecord()
' check if an edit is empty
INT empty_edit = 0
string firstName = GETCONTROLTEXT(w1, EDIT_3)
string lastName = GETCONTROLTEXT(w1, EDIT_7)
UnicodeEditGetText(w1, EDIT_9, buffer, MAX_UNICHARS)
IF LEN(firstName) = 0 THEN empty_edit = 1
IF LEN(lastName) = 0 THEN empty_edit = 1
IF LEN(buffer) = 0 THEN empty_edit = 1
IF empty_edit THEN
MESSAGEBOX(w1, "Empty Edit Box", "error", @MB_ICONSTOP)
RETURN
ENDIF
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")
IF pdb THEN
DatabaseAddRecord(pdb, lastname, firstname, buffer)
ResetUI(0)
dbDisconnect(pdb)
LoadDatabase()
ENDIF
ENDSUB
SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
istring unicodeHEX[4 * MAX_UNICHARS + 6] = WstringToHexString(unicodeData)
INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")
IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)
dbFreeSQL(hstmt)
ENDIF
ENDSUB
SUB WstringToHexString(wstring unicodeData), string
istring unicodeHEX[4 * MAX_UNICHARS + 6] = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0
DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0
RETURN unicodeHEX
ENDSUB
SUB GetHEX(int t), string
istring hex[10] = HEX$(t)
if LEN(hex) = 1 THEN hex = "0" + hex
RETURN hex
ENDSUB
SUB LoadDatabase()
IWSTRING text[10000] = L""
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")
IF pdb THEN
string first, last
INT branch_null
INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")
IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 2* MAX_UNICHARS)
WHILE dbGet(hStmt)
text += S2W(first) + L" - " + S2W(last) + L" - " + buffer + L" (" + WLTRIM$(wstr$(len(buffer))) + L" characters long)" + L"\n"
ENDWHILE
UnicodeEditSetText(w1, EDIT_11, text)
dbFreeSQL(hStmt)
ENDIF
dbDisconnect(pdb)
ENDIF
ENDSUB
'------------------------------------------
' UI PROCEDURES
'------------------------------------------
SUB UpdateUI()
ENABLECONTROL w1, BUTTON_1, InvertFlag(databaseCreated)
ENABLECONTROL w1, BUTTON_2, databaseCreated
ENABLECONTROL w1, EDIT_3, databaseCreated
ENABLECONTROL w1, EDIT_7, databaseCreated
ENABLECONTROL w1, EDIT_9, databaseCreated
ENABLECONTROL w1, BUTTON_4, databaseCreated
ENABLECONTROL w1, EDIT_11, TRUE
ENDSUB
SUB ResetUI(INT allofthem)
SETCONTROLTEXT w1, EDIT_3, ""
SETCONTROLTEXT w1, EDIT_7, ""
UnicodeEditSetText(w1, EDIT_9, L"")
IF allofthem THEN UnicodeEditSetText(w1, EDIT_11, L"")
ENDSUB
SUB InvertFlag(INT flag), INT
IF flag THEN RETURN 0
RETURN 1
ENDSUB
'------------------------------------------
' UNICODE EDIT CONTROL
'------------------------------------------
SUB UnicodeEditCreate(window w, INT ctrlID, INT x, INT y, INT width, INT height, INT multiline)
INT dwStyles = WS_CHILD | WS_VISIBLE | ES_LEFT | WS_TABSTOP
IF multiline THEN dwStyles = dwStyles | WS_VSCROLL | ES_MULTILINE | ES_WANTRETURN
INT hwndEDIT = CreateWindowExW(WS_EX_CLIENTEDGE, L"EDIT",NULL, dwStyles, x, y, width, height, w.hwnd, ctrlID, GetModuleHandle(0), NULL)
ENDSUB
SUB UnicodeEditGetText(window w, INT ctrlID, pointer buffer, INT maxCount)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
GetWindowTextW(hwndEDIT, buffer, maxCount)
ENDSUB
SUB UnicodeEditSetText(window w, INT ctrlID, pointer buffer)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
SetWindowTextW(hwndEDIT, buffer)
ENDSUB
Wow is the word!
Fasecero, that's so brilliant - I've now been able to amend my test program to accept Unicode notes and it work very well.
I've got a few other fields I'm going to amend - but this is just superb! (is that enough thanks lol?).
Hopefully one day, I might actually be able to help you in return.
Andy.
:) :)
Thank you very much, Andy. Don't worry, when I joined the forum there were people who helped me a LOT. Oh one thing, the math you did to get those 126 characters were the key to solve the unicode limit. You can notice it in the new hex string size [4 * MAX_UNICHARS + 6] your math put me on the right track.
One curious thing is how flexible VARBINARY is, it allow to overcome the limit we are imposing with VARBINARY(510). If I put there a value greater than 510, it gives an error. But with 510 works ok even if we exceed that size.
And for the first time I get what hex strings are capable of. They can hold unicode data inside non unicode strings. How cool is that? Similar to your own format: 200,3,187,3,187,3,187,3,187,3,187,3,187,3,0,0
----
On a final note to anyone using this, just be cautious with the buffer size on each place. They must match exactly to avoid any data overflow. The most simple thing to do is to define a constant at the beginning of the source with the desired size.
$DEFINE MAX_UNICHARS 1000 ' MAXIMUN NUMBER OF UNICODE CHARACTER ALLOWED/WANTED
IWSTRING buffer[MAX_UNICHARS]
Later on you can setup the remaining arrays sizes more easily
UnicodeEditGetText(w1, EDIT_9, buffer, MAX_UNICHARS) ' getting unicode edit text
istring unicodeHEX[4 * MAX_UNICHARS + 6] = WstringToHexString(unicodeData) ' creating the hex string
istring unicodeHEX[4 * MAX_UNICHARS + 6] = "0x" ' buffer size inside WstringToHexString
dbBindVariable(hstmt, 3, buffer, branch_null, 2* MAX_UNICHARS) ' loading the unicode from the database
Fasecero,
Some great golden rules there - nicely put.
You will also have to use
dbGetData(hStmt,17,buffer,2* MAX_UNICHARS) '<---- with a size (2* MAX_UNICHARS)
If you are using the dbGetData command.
Andy.
:)